cancel
Showing results for 
Search instead for 
Did you mean: 

Why do the queries change and fail?

Jump to solution

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
1 Solution

Accepted Solutions
McAfee Employee
McAfee Employee
Report Inappropriate Content
Message 2 of 5

Re: Why do the queries change and fail?

Jump to solution

The change in the query looks like it's being added to deal with permissions... which account are you running the query as? It looks like it may not have permissions to all of the tree.

What happens if you run the same query as a global administrator?

Regards -

Joe

4 Replies
McAfee Employee
McAfee Employee
Report Inappropriate Content
Message 2 of 5

Re: Why do the queries change and fail?

Jump to solution

The change in the query looks like it's being added to deal with permissions... which account are you running the query as? It looks like it may not have permissions to all of the tree.

What happens if you run the same query as a global administrator?

Regards -

Joe

Re: Why do the queries change and fail?

Jump to solution

Thanks for the response!

Yes, I just came to the same conclusion but unfortunately both my accounts are Global administrators. One is working and the other one is not. The account that isn't working has had other permissions assigned previously.

I'm currently tryiing to figure out if theres a file based cache somewhere because restarting the service didn't help at all.

Re: Why do the queries change and fail?

Jump to solution

It is related to permissions but in this case previous permissons and not current permissions (global administrator).

McAfee Employee
McAfee Employee
Report Inappropriate Content
Message 5 of 5

Re: Why do the queries change and fail?

Jump to solution

Glad you've got it sorted

Regards -

Joe