7 Replies Latest reply on Aug 18, 2014 6:07 AM by epository

    Compliance History Query - Daily Total of Rogue Systems Trends

    epository

      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?

        • 1. Re: Compliance History Query - Daily Total of Rogue Systems Trends
          ansarias

          Hello,

           

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

          • 2. Re: Compliance History Query - Daily Total of Rogue Systems Trends
            epository

            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?

            • 3. Re: Compliance History Query - Daily Total of Rogue Systems Trends
              ansarias

              Hello,

               

              Did you define months option in filter under query tab?

              • 4. Re: Compliance History Query - Daily Total of Rogue Systems Trends
                epository

                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.

                • 5. Re: Compliance History Query - Daily Total of Rogue Systems Trends
                  epository

                  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

                  • 6. Re: Compliance History Query - Daily Total of Rogue Systems Trends
                    epository

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

                    • 7. Re: Compliance History Query - Daily Total of Rogue Systems Trends
                      epository

                      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.