cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
HBullock
Level 10
Report Inappropriate Content
Message 1 of 2

SQL to SQUID help

I can not find any documentation on SQUID query syntax. Can someone point me to the relevant links?

Can anyone provide a conversion from SQL to SQUID for the following query?

datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.year' ,
       datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.week' ,
       [EPOEvents].[AnalyzerHostName],
       [EPOEvents].[ThreatType],
       [EPOEvents].[ThreatName],
       [EPOEvents].[AnalyzerDetectionMethod],
       [EPOBranchNode].[NodeTextPath2]select count(*) as 'count',
       datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.year' ,
       datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.week' ,
       [EPOEvents].[AnalyzerHostName],
       [EPOEvents].[ThreatType],
       [EPOEvents].[ThreatName],
       [EPOEvents].[AnalyzerDetectionMethod],
       [EPOBranchNode].[NodeTextPath2]
from [EPOEvents]
left outer join [ePOLeafNode] on [EPOEvents].[AgentGUID] = [ePOLeafNode].[AgentGUID]
left outer join [EPOBranchNode] on [ePOLeafNode].[ParentID] = [EPOBranchNode].[AutoID]
where (
       ( [EPOEvents].[AnalyzerName] = N'VirusScan Enterprise' )
       and
       ( ( [EPOEvents].[ThreatType] is null
          or ( [EPOEvents].[ThreatType] <> N'access protection' )
       )
       and ( [EPOEvents].[ThreatType] is null
           or ( [EPOEvents].[ThreatType] <> N'none' )
           )
       )
      )
group by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         [EPOEvents].[AnalyzerHostName],
         [EPOEvents].[ThreatType],
         [EPOEvents].[ThreatName],
         [EPOEvents].[AnalyzerDetectionMethod],
         [EPOBranchNode].[NodeTextPath2]
order by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
        'count' desc,
        [EPOEvents].[AnalyzerHostName] asc,
        [EPOEvents].[ThreatType] asc,
        [EPOEvents].[ThreatName] asc,
        [EPOEvents].[AnalyzerDetectionMethod] asc,
        [EPOBranchNode].[NodeTextPath2] ascselect count(*) as 'count',
       datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.year' ,
       datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.week' ,
       [EPOEvents].[AnalyzerHostName],
       [EPOEvents].[ThreatType],
       [EPOEvents].[ThreatName],
       [EPOEvents].[AnalyzerDetectionMethod],
       [EPOBranchNode].[NodeTextPath2]
from [EPOEvents]
left outer join [ePOLeafNode] on [EPOEvents].[AgentGUID] = [ePOLeafNode].[AgentGUID]
left outer join [EPOBranchNode] on [ePOLeafNode].[ParentID] = [EPOBranchNode].[AutoID]
where (
       ( [EPOEvents].[AnalyzerName] = N'VirusScan Enterprise' )
       and
       ( ( [EPOEvents].[ThreatType] is null
          or ( [EPOEvents].[ThreatType] <> N'access protection' )
       )
       and ( [EPOEvents].[ThreatType] is null
           or ( [EPOEvents].[ThreatType] <> N'none' )
           )
       )
      )
group by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         [EPOEvents].[AnalyzerHostName],
         [EPOEvents].[ThreatType],
         [EPOEvents].[ThreatName],
         [EPOEvents].[AnalyzerDetectionMethod],
         [EPOBranchNode].[NodeTextPath2]
order by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
        'count' desc,
        [EPOEvents].[AnalyzerHostName] asc,
        [EPOEvents].[ThreatType] asc,
        [EPOEvents].[ThreatName] asc,
        [EPOEvents].[AnalyzerDetectionMethod] asc,
        [EPOBranchNode].[NodeTextPath2] ascselect count(*) as 'count',
       datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.year' ,
       datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.week' ,
       [EPOEvents].[AnalyzerHostName],
       [EPOEvents].[ThreatType],
       [EPOEvents].[ThreatName],
       [EPOEvents].[AnalyzerDetectionMethod],
       [EPOBranchNode].[NodeTextPath2]
from [EPOEvents]
left outer join [ePOLeafNode] on [EPOEvents].[AgentGUID] = [ePOLeafNode].[AgentGUID]
left outer join [EPOBranchNode] on [ePOLeafNode].[ParentID] = [EPOBranchNode].[AutoID]
where (
       ( [EPOEvents].[AnalyzerName] = N'VirusScan Enterprise' )
       and
       ( ( [EPOEvents].[ThreatType] is null
          or ( [EPOEvents].[ThreatType] <> N'access protection' )
       )
       and ( [EPOEvents].[ThreatType] is null
           or ( [EPOEvents].[ThreatType] <> N'none' )
           )
       )
      )
group by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         [EPOEvents].[AnalyzerHostName],
         [EPOEvents].[ThreatType],
         [EPOEvents].[ThreatName],
         [EPOEvents].[AnalyzerDetectionMethod],
         [EPOBranchNode].[NodeTextPath2]
order by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
        'count' desc,
        [EPOEvents].[AnalyzerHostName] asc,
        [EPOEvents].[ThreatType] asc,
        [EPOEvents].[ThreatName] asc,
        [EPOEvents].[AnalyzerDetectionMethod] asc,
        [EPOBranchNode].[NodeTextPath2] asc

drop table #WeeklyEventsselect count(*) as 'count',
       datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.year' ,
       datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.week' ,
       [EPOEvents].[AnalyzerHostName],
       [EPOEvents].[ThreatType],
       [EPOEvents].[ThreatName],
       [EPOEvents].[AnalyzerDetectionMethod],
       [EPOBranchNode].[NodeTextPath2]
from [EPOEvents]
left outer join [ePOLeafNode] on [EPOEvents].[AgentGUID] = [ePOLeafNode].[AgentGUID]
left outer join [EPOBranchNode] on [ePOLeafNode].[ParentID] = [EPOBranchNode].[AutoID]
where (
       ( [EPOEvents].[AnalyzerName] = N'VirusScan Enterprise' )
       and
       ( ( [EPOEvents].[ThreatType] is null
          or ( [EPOEvents].[ThreatType] <> N'access protection' )
       )
       and ( [EPOEvents].[ThreatType] is null
           or ( [EPOEvents].[ThreatType] <> N'none' )
           )
       )
      )
group by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         [EPOEvents].[AnalyzerHostName],
         [EPOEvents].[ThreatType],
         [EPOEvents].[ThreatName],
         [EPOEvents].[AnalyzerDetectionMethod],
         [EPOBranchNode].[NodeTextPath2]
order by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
        'count' desc,
        [EPOEvents].[AnalyzerHostName] asc,
        [EPOEvents].[ThreatType] asc,
        [EPOEvents].[ThreatName] asc,
        [EPOEvents].[AnalyzerDetectionMethod] asc,
        [EPOBranchNode].[NodeTextPath2] asc

drop table #WeeklyEventsselect count(*) as 'count',
       datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.year' ,
       datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.week' ,
       [EPOEvents].[AnalyzerHostName],
       [EPOEvents].[ThreatType],
       [EPOEvents].[ThreatName],
       [EPOEvents].[AnalyzerDetectionMethod],
       [EPOBranchNode].[NodeTextPath2]
from [EPOEvents]
left outer join [ePOLeafNode] on [EPOEvents].[AgentGUID] = [ePOLeafNode].[AgentGUID]
left outer join [EPOBranchNode] on [ePOLeafNode].[ParentID] = [EPOBranchNode].[AutoID]
where (
       ( [EPOEvents].[AnalyzerName] = N'VirusScan Enterprise' )
       and
       ( ( [EPOEvents].[ThreatType] is null
          or ( [EPOEvents].[ThreatType] <> N'access protection' )
       )
       and ( [EPOEvents].[ThreatType] is null
           or ( [EPOEvents].[ThreatType] <> N'none' )
           )
       )
      )
group by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         [EPOEvents].[AnalyzerHostName],
         [EPOEvents].[ThreatType],
         [EPOEvents].[ThreatName],
         [EPOEvents].[AnalyzerDetectionMethod],
         [EPOBranchNode].[NodeTextPath2]
order by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
        'count' desc,
        [EPOEvents].[AnalyzerHostName] asc,
        [EPOEvents].[ThreatType] asc,
        [EPOEvents].[ThreatName] asc,
        [EPOEvents].[AnalyzerDetectionMethod] asc,
        [EPOBranchNode].[NodeTextPath2] asc

drop table #WeeklyEventsselect count(*) as 'count',
       datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.year' ,
       datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.week' ,
       [EPOEvents].[AnalyzerHostName],
       [EPOEvents].[ThreatType],
       [EPOEvents].[ThreatName],
       [EPOEvents].[AnalyzerDetectionMethod],
       [EPOBranchNode].[NodeTextPath2]
from [EPOEvents]
left outer join [ePOLeafNode] on [EPOEvents].[AgentGUID] = [ePOLeafNode].[AgentGUID]
left outer join [EPOBranchNode] on [ePOLeafNode].[ParentID] = [EPOBranchNode].[AutoID]
where (
       ( [EPOEvents].[AnalyzerName] = N'VirusScan Enterprise' )
       and
       ( ( [EPOEvents].[ThreatType] is null
          or ( [EPOEvents].[ThreatType] <> N'access protection' )
       )
       and ( [EPOEvents].[ThreatType] is null
           or ( [EPOEvents].[ThreatType] <> N'none' )
           )
       )
      )
group by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         [EPOEvents].[AnalyzerHostName],
         [EPOEvents].[ThreatType],
         [EPOEvents].[ThreatName],
         [EPOEvents].[AnalyzerDetectionMethod],
         [EPOBranchNode].[NodeTextPath2]
order by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
        'count' desc,
        [EPOEvents].[AnalyzerHostName] asc,
        [EPOEvents].[ThreatType] asc,
        [EPOEvents].[ThreatName] asc,
        [EPOEvents].[AnalyzerDetectionMethod] asc,
        [EPOBranchNode].[NodeTextPath2] asc

drop table #WeeklyEvents

select count(*) as 'count',
       datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.year' ,
       datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) as 'EPOEvents.ReceivedUTC.week' ,
       [EPOEvents].[AnalyzerHostName],
       [EPOEvents].[ThreatType],
       [EPOEvents].[ThreatName],
       [EPOEvents].[AnalyzerDetectionMethod],
       [EPOBranchNode].[NodeTextPath2]
from [EPOEvents]
left outer join [ePOLeafNode] on [EPOEvents].[AgentGUID] = [ePOLeafNode].[AgentGUID]
left outer join [EPOBranchNode] on [ePOLeafNode].[ParentID] = [EPOBranchNode].[AutoID]
where (
       ( [EPOEvents].[AnalyzerName] = N'VirusScan Enterprise' )
       and
       ( ( [EPOEvents].[ThreatType] is null
          or ( [EPOEvents].[ThreatType] <> N'access protection' )
       )
       and ( [EPOEvents].[ThreatType] is null
           or ( [EPOEvents].[ThreatType] <> N'none' )
           )
       )
      )
group by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ),
         [EPOEvents].[AnalyzerHostName],
         [EPOEvents].[ThreatType],
         [EPOEvents].[ThreatName],
         [EPOEvents].[AnalyzerDetectionMethod],
         [EPOBranchNode].[NodeTextPath2]
order by datepart( YEAR, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
         datepart( WEEK, dateadd( MILLISECOND, -18000000, [EPOEvents].[ReceivedUTC] ) ) asc,
        'count' desc,
        [EPOEvents].[AnalyzerHostName] asc,
        [EPOEvents].[ThreatType] asc,
        [EPOEvents].[ThreatName] asc,
        [EPOEvents].[AnalyzerDetectionMethod] asc,
        [EPOBranchNode].[NodeTextPath2] asc

1 Reply
jbrooks2
Level 9
Report Inappropriate Content
Message 2 of 2

Re: SQL to SQUID help

Hi Howard,

Unfortunately, there is no way to represent this query, as there is a limitation on the 'select' parameter to core.executeQuery.  The reference to the 'select' parameter being similar to the SQL select is a bit of a misnomer, at least in the current Web API.  It does not allow SQUID operators such as 'year' or 'week'.  It supports simple column selection but not expressions, such as 'year' or 'week' (ie the date extraction expression defined in your SQL's SELECT).  Additional, you might think there would be a way to workaround this by specifying an alias for any GROUP BY columns or expressions and then referencing that alias in the select, as you can in SQL, but that also is not supported.


Additionally, I tried creating this query in the UI using a Multi-Group Summary Table but was unable to use the same column (for your example, [EPOEvents].[ReceivedUTC]) in more than one GROUP BY expression; our UI does not allow it. The attempted workaround here would be that the query could be constructed in the UI (thus having a wider array of functionality available with which to construct the query) and then could be executed by its ID rather than constructed on the fly.  But it appears, unless I'm missing some other way to create your query, that this particular query cannot be represented.  If, however, it can, then you could simply call core.executeQuery with the queryId argument instead of target.

core.executeQuery queryId [database=<>]

instead of

core.executeQuery target=<> [select=<>] [where=<>] [order=<>] [group=<>]

[database=<>] [depth=<>] [joinTables=<>]

Were you also unable to represent this query using the query wizard/UI?

We do have a bug filed to update the documentation for this missing piece of information.

-Jeremy

You Deserve an Award
Don't forget, when your helpful posts earn a kudos or get accepted as a solution you can unlock perks and badges. Those aren't the only badges, either. How many can you collect? Click here to learn more.

Community Help Hub

    New to the forums or need help finding your way around the forums? There's a whole hub of community resources to help you.

  • Find Forum FAQs
  • Learn How to Earn Badges
  • Ask for Help
Go to Community Help

Join the Community

    Thousands of customers use the McAfee Community for peer-to-peer and expert product support. Enjoy these benefits with a free membership:

  • Get helpful solutions from McAfee experts.
  • Stay connected to product conversations that matter to you.
  • Participate in product groups led by McAfee employees.
Join the Community
Join the Community