4 Replies Latest reply on Mar 18, 2010 8:57 AM by mwestman

    Why do the queries change and fail?

      EDIT: ePO 4.5, VSE 8.7 and VSES 1.0

       

      For example, the standard query "VSES NetApp: Threats Detected per Week" ran fine yesterday but today it returns the annoying "Query did not return any results".

       

      I checked the query itself by choosing "View Query SQL" and got this:

      select count(*) as 'count', datepart(year, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) as 'EPOEvents.DetectedUTC.year' , datepart(week, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) as 'EPOEvents.DetectedUTC.week' , datepart(year, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) as 'EPOEvents.DetectedUTC.year' , datepart(week, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) as 'EPOEvents.DetectedUTC.week' from [EPOEvents]
      where ( ( [EPOEvents].[DetectedUTC] between '2009-12-17T08:36:31.785' and '2010-03-17T08:36:31.785' ) and ( [EPOEvents].[Analyzer] like N'VSESTOMD%' ) and ( ( [EPOEvents].[ThreatType] = N'app' ) or ( [EPOEvents].[ThreatType] = N'app_adware' ) or ( [EPOEvents].[ThreatType] = N'app_remoteadmin' ) or ( [EPOEvents].[ThreatType] = N'app_pua' ) or ( [EPOEvents].[ThreatType] = N'app_puo' ) or ( [EPOEvents].[ThreatType] = N'app_package' ) or ( [EPOEvents].[ThreatType] = N'app_rootkit' ) or ( [EPOEvents].[ThreatType] = N'app_P2P' ) or ( [EPOEvents].[ThreatType] = N'app_IM' ) or ( [EPOEvents].[ThreatType] = N'app_keylogger' ) or ( [EPOEvents].[ThreatType] = N'app_pwcracker' ) or ( [EPOEvents].[ThreatType] = N'app_dialer' ) or ( [EPOEvents].[ThreatType] = N'app_spyware' ) or ( [EPOEvents].[ThreatType] = N'virus' ) or ( [EPOEvents].[ThreatType] = N'comp' ) or ( [EPOEvents].[ThreatType] = N'trojan' ) or ( [EPOEvents].[ThreatType] = N'joke' ) or ( [EPOEvents].[ThreatType] = N'test' ) ) and ( [EPOEvents].[AnalyzerDetectionMethod] = N'NetApp' ) )
      group by datepart(year, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])), datepart(week, dateadd(ms,3600000,[EPOEvents].[DetectedUTC]))
      order by datepart(year, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) asc, datepart(week, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) asc
      

      When running this using SQL Server Management Studio it returns 10 rows as it should.

       

      Then I used SQL Profiler to catch the query issued when choosing "Run" from the Actions options in the ePO console on the same query and got this:

      select count(*) as 'count', datepart(year, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) as 'EPOEvents.DetectedUTC.year' , datepart(week, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) as 'EPOEvents.DetectedUTC.week' , datepart(year, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) as 'EPOEvents.DetectedUTC.year' , datepart(week, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) as 'EPOEvents.DetectedUTC.week' from [EPOEvents]
      where ( EPOEvents.AgentGUID IN (SELECT AgentGUID FROM EPOLeafNode WHERE AgentGUID IS NOT NULL AND ParentID IN (SELECT AutoID from EPOBranchNode WHERE AutoID IN (SELECT NodeID FROM EPONodePermissions WHERE EPONodePermissions.GroupID IN (7,8)))) and ( ( [EPOEvents].[DetectedUTC] between '2009-12-17T08:38:43.972' and '2010-03-17T08:38:43.972' ) and ( [EPOEvents].[Analyzer] like N'VSESTOMD%' ) and ( ( [EPOEvents].[ThreatType] = N'app' ) or ( [EPOEvents].[ThreatType] = N'app_adware' ) or ( [EPOEvents].[ThreatType] = N'app_remoteadmin' ) or ( [EPOEvents].[ThreatType] = N'app_pua' ) or ( [EPOEvents].[ThreatType] = N'app_puo' ) or ( [EPOEvents].[ThreatType] = N'app_package' ) or ( [EPOEvents].[ThreatType] = N'app_rootkit' ) or ( [EPOEvents].[ThreatType] = N'app_P2P' ) or ( [EPOEvents].[ThreatType] = N'app_IM' ) or ( [EPOEvents].[ThreatType] = N'app_keylogger' ) or ( [EPOEvents].[ThreatType] = N'app_pwcracker' ) or ( [EPOEvents].[ThreatType] = N'app_dialer' ) or ( [EPOEvents].[ThreatType] = N'app_spyware' ) or ( [EPOEvents].[ThreatType] = N'virus' ) or ( [EPOEvents].[ThreatType] = N'comp' ) or ( [EPOEvents].[ThreatType] = N'trojan' ) or ( [EPOEvents].[ThreatType] = N'joke' ) or ( [EPOEvents].[ThreatType] = N'test' ) ) and ( [EPOEvents].[AnalyzerDetectionMethod] = N'NetApp' ) ) )
      group by datepart(year, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])), datepart(week, dateadd(ms,3600000,[EPOEvents].[DetectedUTC]))
      order by datepart(year, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) asc, datepart(week, dateadd(ms,3600000,[EPOEvents].[DetectedUTC])) asc
      

      When running this using SQL Server Management Studio it returns 0 rows.

       

      The difference is this:

      ( EPOEvents.AgentGUID IN (SELECT AgentGUID FROM EPOLeafNode WHERE AgentGUID IS NOT NULL AND ParentID IN (SELECT AutoID from EPOBranchNode WHERE AutoID IN (SELECT NodeID FROM EPONodePermissions WHERE EPONodePermissions.GroupID IN (7,8)))) and 
      

       

      Why the query is changing is interesting in itself but how can I get this working again?

       

       

      Message was edited by: Mats Westman on 3/17/10 4:33:00 AM CDT

       

       

      Message was edited by: Mats Westman on 3/17/10 4:33:41 AM CDT

       

       

      Message was edited by: mwestman on 3/17/10 10:51:40 AM CET