Compliance History Query - Daily Total of Rogue Systems Trends

[Former Member]

I have a Boolean pie chart set up using Detected Systems and Rogue State to show how many Rogues we have per day.

I have a server task that is running this query daily with the subtask "Generate Compliance Event", however, not getting any data in my Compliance Query.

I have another query set that is showing me the number of Managed Systems per day, and this one is working fine.

Any idea how to get a trend line of number of rogues?

[ansarias]

Hello,

Change per day to weekly or monthly basis so you will get compliance history trends.

[Former Member]

I'm sorry, can you elaborate on this?

I mean, i can get an hourly Compliance History report on Managed Systems....ie. last checkin time within the last 3 days....so why cant I get a daily trend of how many Rogue systems we have per day over the last X months?

[ansarias]

Hello,

Did you define months option in filter under query tab?

[Former Member]

yes...but the point is, my tracking of Rogues is displaying on the Total Number of Detected Systems.....I have tried every variation on the Compliance History query with the same results.

The Boolean Pie Chart works fine and splits out Rogues vs. Managed, but the Compliance History one will only display a total number of Detected systems...so Managed + Rogue + Exceptions.

[Former Member]

Booean Pie Chart Query

select count(*) as 'count', [BooleanPieChart_Alias].[ChartColor], [BooleanPieChart_Alias].[ChartColor] from ( select ( case when ( ( [RSDDetectedSystems].[Rogue] = 1 ) and ( [RSDDetectedSystems].[LastDetectedTime] between '2014-08-07T05:52:15.485' and '2014-08-14T05:52:15.485' ) and ( [RSDDetectedSystems].[Exception] = 0 ) and ( [RSDDetectedSystems].[Managed] = 0 ) ) then 1 when ( not ( ( [RSDDetectedSystems].[Rogue] = 1 ) and ( [RSDDetectedSystems].[LastDetectedTime] between '2014-08-07T05:52:15.485' and '2014-08-14T05:52:15.485' ) and ( [RSDDetectedSystems].[Exception] = 0 ) and ( [RSDDetectedSystems].[Managed] = 0 ) ) ) then 0 else -1 end ) as ChartColor from [RSDDetectedSystems] where ( [RSDDetectedSystems].[LastDetectedTime] between '2014-08-07T05:52:15.485' and '2014-08-14T05:52:15.485' ) ) as BooleanPieChart_Alias group by [BooleanPieChart_Alias].[ChartColor] order by [BooleanPieChart_Alias].[ChartColor] desc Here is the SQL for the Compliance Summary select [EpoComplianceHistory].[CountNonCompliant], datepart( YEAR, dateadd( MILLISECOND, 10800000, [EpoComplianceHistory].[TheTimestamp] ) ) as 'EpoComplianceHistory.TheTimestamp.year' , datepart( DAYOFYEAR, dateadd( MILLISECOND, 10800000, [EpoComplianceHistory].[TheTimestamp] ) ) as 'EpoComplianceHistory.TheTimestamp.dayofyear' , [EpoComplianceHistory].[AutoId] from [EpoComplianceHistory] where ( ( [EpoComplianceHistory].[TaskName] = N'Compliance History - Daily Rogue count (detected last 7 days)' ) and ( [EpoComplianceHistory].[TheTimestamp] < '2014-08-14T05:53:20.615' ) ) order by datepart( YEAR, dateadd( MILLISECOND, 10800000, [EpoComplianceHistory].[TheTimestamp] ) ) asc, datepart( DAYOFYEAR, dateadd( MILLISECOND, 10800000, [EpoComplianceHistory].[TheTimestamp] ) ) asc

[Former Member]

NOTE to mcafee...this JIVE engine for comments really really sucks

[Former Member]

Finally got this to work, but had to play with it.

1.  You can only get a compliance report on # of non-compliant systems OR % compliant AND/OR %non-compliant....not so helpful with Rogues.

2.  So, for a Managed System trend of a daily count of the # of Managed Systems checked in within the Last 3 days....you Boolean chart would have to show Compliance criteria as  "Last Communication NOT withing the Last 3 Days" then your compliance report would show the # of Non-Compliant sytems i.e. # of machines that have checked in within the last 3 days.

3.  For a Daily Rogue count, my Boolean Pie has criteria "Last Detected within the last week" and "Rogue = False"...for some reason, this does exclude exceptions....adding exception jacked things up.