9 Replies Latest reply: Jul 23, 2013 9:58 AM by stev379 RSS

    Query returns different number of total systems

    stev379

      Why is the total system count so high on my 8.7 query? I run AD discovery nightly and clean up disconnected systems regularly.

       

      If run 2 queries, one for 8.7 and one for 8.8, I get widely different totals. Why?

       

      I know many of these system have not been online for a while. I don't care about the 8.7 stuff being non-compliant. I get why that is, but I don't understand why the total counts are different.

       

      8.7 query = 2741 Non-Compliant

      8.8 query = 615 Compliant  &  4 Non-Compliant

      There's over 2100 more machines being reported on from the 8.7 query

       

      CRITERIA

      8.7 Boolean Pie

      Pie slices are number of managed systems

      VirusScan Product Version is Greater than or equals - 8.7

      AND Less than 8.8

      RESULT --> 2741 Non-Compliant

       

      8.8 Boolean Pie

      Pie slices are number of managed systems

      VirusScan Product Version is Greater than or equals - 8.8

      AND Less than 8.9

      RESULT --> 615 Compiant & 4 Non-Compliant

       

       

       

      ePO 4.6.3 (build 197) on Win 2k3

      XP and Win 7 desktops

      Using VirusScan 8.8 and previous had 8.7...a couple years ago.

       

      Thanks!

        • 1. Re: Query returns different number of total systems
          Laszlo G

          So you mean that the 8.7 query shows any computer as non-compliant? If that's right then you must not have computers with VirusScan 8.7 or you forgot to install VirusScan 8.7 management extension into ePO.

           

          Remeber also that your 8.7 query will show any VSE 8.8 computer as non-compliant and vice-versa with 8.8 query

          • 2. Re: Query returns different number of total systems
            Tristan

            First question is how many machines are you actually managing in ePO? e.g. In your system tree select 'My Organization' and change the preset to 'This Group and All Subgroups' how many items are listed?

             

            Sometimes machines get dumped in 'Lost&Found'

             

            What results do you get with the default ePO Queries?

            • 3. Re: Query returns different number of total systems
              stev379

              Thanks for the reply. Much appreciated.

               

              I understand how 8.7 will report the 8.8 computers as non-compliant, but I don't understand why the 8.7 query finds 2741 computers total while the 8.8 query only finds 619 computers total. Regardless of compliancy, both queries should be looking at the same group of total computers.

              • 4. Re: Query returns different number of total systems
                Tristan

                Post the SQL of your query. 2741 is roughly x4 your number of systems so it's possible there's a JOIN in the query that's multiplying the system count.

                 

                Tick the query and in the 'Actions' button there will be an option to 'view SQL'

                • 5. Re: Query returns different number of total systems
                  stev379

                  @Tristan's first reply: Thanks for the suggestions. I think this is heading in the correct direction.

                   

                  With subgroups shown, the current total is 2739.

                   

                  The default queries return different results as well. The VSE: Version 8.8 Compliance default query returns 2741 computers

                   

                  The default "Unmanaged Systems" query returns 1917

                   

                  The default "VSE: Managed Machine Compliance query returns 675 total (659 Mgd - 16 Not Mgd)

                   

                  This is helping. I just don't understand how the queries are looking at different totals.

                  • 6. Re: Query returns different number of total systems
                    stev379

                    I'm no SQL guru, but it looks like the 8.8 query is limiting the detected time range. I went back to confirm the criteria and even added and removed a "last detected" statement in the GUI and resaved. So the criteria shown for both queries below is currently accurate. But the 8.8 SQL statement seems to be different than the GUI settings.

                     

                    I created a new statement for 8.8 Less than 8.9 and it returns the full 2740 count. I think this might be resolved. I still have some clean up, but your direction to check SQL was what got me to this point. 

                     

                     

                     

                    8.7 Query

                    CRITERIA

                    8.7 Boolean Pie

                    Pie slices are number of managed systems

                    VirusScan Product Version is Greater than or equals - 8.7

                    AND Less than 8.8

                    RESULT --> 2741 Non-Compliant

                     

                    8.7 SQL -

                    select count(*) as 'count', [BooleanPieChart_Alias].[ChartColor], [BooleanPieChart_Alias].[ChartColor] from ( select ( case when ( ( ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] > 8 ) or ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] = 8 and [EPOProdPropsView_VIRUSCAN].[verProductMinor] >= 7 ) ) and ( not ( ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] > 8 ) or ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] = 8 and [EPOProdPropsView_VIRUSCAN].[verProductMinor] >= 8 ) ) )  ) then 1 when ( not ( ( ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] > 8 ) or ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] = 8 and [EPOProdPropsView_VIRUSCAN].[verProductMinor] >= 7 ) ) and ( not ( ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] > 8 ) or ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] = 8 and [EPOProdPropsView_VIRUSCAN].[verProductMinor] >= 8 ) ) )  ) )  then 0 else -1  end ) as ChartColor from [EPOLeafNode] left join [EPOProdPropsView_VIRUSCAN] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID] ) as BooleanPieChart_Alias group by [BooleanPieChart_Alias].[ChartColor] order by [BooleanPieChart_Alias].[ChartColor] desc

                     

                    8.8 Query

                    CRITERIA

                    8.8 Boolean Pie

                    Pie slices are number of managed systems

                    VirusScan Product Version is Greater than or equals - 8.8

                    AND Less than 8.9

                    RESULT --> 615 Compiant & 4 Non-Compliant

                     

                    8.8 SQL -

                    select count(*) as 'count', [BooleanPieChart_Alias].[ChartColor], [BooleanPieChart_Alias].[ChartColor] from ( select ( case when ( ( ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] > 8 ) or ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] = 8 and [EPOProdPropsView_VIRUSCAN].[verProductMinor] >= 8 ) ) and ( not ( ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] > 8 ) or ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] = 8 and [EPOProdPropsView_VIRUSCAN].[verProductMinor] >= 9 ) ) )  ) then 1 when ( not ( ( ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] > 8 ) or ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] = 8 and [EPOProdPropsView_VIRUSCAN].[verProductMinor] >= 8 ) ) and ( not ( ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] > 8 ) or ( [EPOProdPropsView_VIRUSCAN].[verProductMajor] = 8 and [EPOProdPropsView_VIRUSCAN].[verProductMinor] >= 9 ) ) )  ) )  then 0 else -1  end ) as ChartColor from [EPOLeafNode] left join [EPOProdPropsView_VIRUSCAN] on [EPOLeafNode].[AutoID] = [EPOProdPropsView_VIRUSCAN].[LeafNodeID] left join [RSDDetectedSystems] on [EPOLeafNode].[AgentGUID] = [RSDDetectedSystems].[AgentGUID] where (  ( [EPOLeafNode].[LastUpdate] between '2013-07-16T10:00:20.687' and '2013-07-19T10:00:20.687' )  and  ( [RSDDetectedSystems].[LastDetectedTime] between '2013-01-20T10:00:20.687' and '2013-07-19T10:00:20.687' )  ) ) as BooleanPieChart_Alias group by [BooleanPieChart_Alias].[ChartColor] order by [BooleanPieChart_Alias].[ChartColor] desc

                    • 7. Re: Query returns different number of total systems
                      Tristan

                      It looks like your queries are cross joining the RSDDetectedSystems tables. Try flushing out any computers listed in 'Detected systems' and see if it changes the results.

                      • 8. Re: Query returns different number of total systems
                        JoeBidgood

                        That's a good catch, Tristan

                        Another approach is to remove the RSD components from the query. They are being added by the query builder either on the Columns or Filter page - edit the query and remove any columns that are part of the Detected Systems section of the Available Columns or Available Properties lists. It's unlikely that you'd need these columns as part of a compliance query.

                        If you're not sure which columns are the culprits, please export the two queries and attach the xml files here, and we can take a look.

                         

                        HTH -

                         

                        Joe

                        • 9. Re: Query returns different number of total systems
                          stev379

                          Thanks again to all who replied, but mostly to Tristan who led me to data range criteria. I knew it was there, but was skipping past it somehow. The old queries had messy SQL statements from years of edits. I created new queries and all looks good.

                           

                           

                          Thanks!