1 Reply Latest reply on Jul 2, 2012 6:10 PM by jbrooks2

    SQL to SQUID help

    HBullock

      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. 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