Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

I have a sql report that I need to filter by a filter GUID

Created: 22 May 2013 | 6 comments

When I use the below query it works fine, except it shows me the totals for all systems.  I need the totals for only active systems.  I created a filter for active computers (from 9K computers, down to 6K).  I have the guid for the filter as {xxxxx}.  How do I filter the below query in SQL to reduce my report to just the active systems.  I can do with ITA easily, but not sure how in SQL. Thanks in Advance for your help!

 

This is for CMS 7.1 (SP2)

 

SQL Query:

--------------------------------

SELECT DISTINCT
                      dca5_AddRemoveProgram.DisplayName AS Software,
                      dca5_AddRemoveProgram._SoftwareComponentGuid AS GUID,
                      dca5_AddRemoveProgram.DisplayVersion AS Version,
                      dca5_AddRemoveProgram.Publisher AS Company,

      COUNT(dca5_AddRemoveProgram.DisplayName) AS Total
FROM       
      Inv_AddRemoveProgram AS dca5_AddRemoveProgram

INNER JOIN
      vComputer AS vComputer ON vComputer.Guid = dca5_AddRemoveProgram._ResourceGuid
WHERE   
      dca5_AddRemoveProgram.DisplayName LIKE 'microsoft office%' AND --dca5_AddRemoveProgram.DisplayName LIKE 'microsoft office%' AND
         ((dca5_AddRemoveProgram.DisplayName NOT LIKE '%web%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%MUI%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%primary%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%IME%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%interface%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%security%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%runtime%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%activation%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%language%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%integration%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%update%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%live%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%components%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%viewer%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%programs%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%connector%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%service pack%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%trial%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%labs%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%plugin%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%proof%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%library%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%evaluation%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%sample%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%templante%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%lenguage%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%engine%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%add-in%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%inside%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%resource kit%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%chinese%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%Viewer%') AND
         (dca5_AddRemoveProgram.DisplayName NOT LIKE '%Library%') AND
(dca5_AddRemoveProgram.InstallFlag LIKE '1'))

GROUP BY dca5_AddRemoveProgram.DisplayName,
         dca5_AddRemoveProgram.DisplayVersion,
         dca5_AddRemoveProgram.Publisher,
         dca5_AddRemoveProgram._SoftwareComponentGuid

 

Operating Systems:

Comments 6 CommentsJump to latest comment

HighTower's picture

You're already joined on vComputer.  vComputer gets you 'IsManaged'.

Add this to your query:

AND vcomputer.IsManaged = 1

 

mhendel05's picture

forgive my ignornance, where?  Change last few lines from This:

(dca5_AddRemoveProgram.DisplayName NOT LIKE '%Library%') AND
(dca5_AddRemoveProgram.InstallFlag LIKE '1'))

 

To this: ?

  (dca5_AddRemoveProgram.DisplayName NOT LIKE '%Library%') AND
(dca5_AddRemoveProgram.InstallFlag LIKE '1')

AND vcomputer.IsManaged = 1)

 

Thanks for your time.

HighTower's picture

That should work.

 

Edit:  Oh wait, I just re-read the query and tested it and I'm getting the same results with and without the IsManaged statement.  For a number of reasons we don't keep our "retired" systems in the database so I'm not sure this will give you what you're asking.  Try it and let us know.

mhendel05's picture

the query looks like it returned the same result. We already have a computer filter we have the guid for the filter that narrows down the active systems (from 9K to ~6K). We just need to filter the report by the filter. Thanks,

HighTower's picture

If you know the GUID this query could help you modify yours:
 

SELECT

vComputer.Guid,

vComputer.Name,

vComputer.[OS Name],

vComputer.[IsManaged],

vCollection.Name AS Filter

FROM CollectionMembership INNER JOIN

vComputer ON CollectionMembership.ResourceGuid = vComputer.Guid INNER JOIN

vCollection INNER JOIN

Collection ON vCollection.Guid = Collection.Guid ON CollectionMembership.CollectionGuid = Collection.Guid

where Collection.Guid = 'abe7b346-ea5e-43e0-b70d-b484441f5ead'

order by vComputer.[OS Name], vComputer.Name ASC

 

(Sorry, I totally read your original post too quickly.  Request comprehension fail!)

HighTower's picture

How about this?

SELECT DISTINCT

ARP.DisplayName AS 'Software',

ARP._SoftwareComponentGuid AS 'GUID',

ARP.DisplayVersion AS 'Version',

ARP.Publisher AS 'Company',

COUNT(ARP.DisplayName) AS Total

FROM

CollectionMembership INNER

JOIN vComputer ON CollectionMembership.ResourceGuid = vComputer.Guid INNER

JOIN vCollection INNER

JOIN Collection ON vCollection.Guid = Collection.Guid ON CollectionMembership.CollectionGuid = Collection.Guid INNER

JOIN Inv_AddRemoveProgram ARP ON vComputer.Guid = ARP._ResourceGuid

WHERE

ARP.DisplayName LIKE 'microsoft office%' AND

((ARP.DisplayName NOT LIKE '%web%') AND

(ARP.DisplayName NOT LIKE '%MUI%') AND

(ARP.DisplayName NOT LIKE '%primary%') AND

(ARP.DisplayName NOT LIKE '%IME%') AND

(ARP.DisplayName NOT LIKE '%interface%') AND

(ARP.DisplayName NOT LIKE '%security%') AND

(ARP.DisplayName NOT LIKE '%runtime%') AND

(ARP.DisplayName NOT LIKE '%activation%') AND

(ARP.DisplayName NOT LIKE '%language%') AND

(ARP.DisplayName NOT LIKE '%integration%') AND

(ARP.DisplayName NOT LIKE '%update%') AND

(ARP.DisplayName NOT LIKE '%live%') AND

(ARP.DisplayName NOT LIKE '%components%') AND

(ARP.DisplayName NOT LIKE '%viewer%') AND

(ARP.DisplayName NOT LIKE '%programs%') AND

(ARP.DisplayName NOT LIKE '%connector%') AND

(ARP.DisplayName NOT LIKE '%service pack%') AND

(ARP.DisplayName NOT LIKE '%trial%') AND

(ARP.DisplayName NOT LIKE '%labs%') AND

(ARP.DisplayName NOT LIKE '%plugin%') AND

(ARP.DisplayName NOT LIKE '%proof%') AND

(ARP.DisplayName NOT LIKE '%library%') AND

(ARP.DisplayName NOT LIKE '%evaluation%') AND

(ARP.DisplayName NOT LIKE '%sample%') AND

(ARP.DisplayName NOT LIKE '%templante%') AND

(ARP.DisplayName NOT LIKE '%lenguage%') AND

(ARP.DisplayName NOT LIKE '%engine%') AND

(ARP.DisplayName NOT LIKE '%add-in%') AND

(ARP.DisplayName NOT LIKE '%inside%') AND

(ARP.DisplayName NOT LIKE '%resource kit%') AND

(ARP.DisplayName NOT LIKE '%chinese%') AND

(ARP.DisplayName NOT LIKE '%Viewer%') AND

(ARP.DisplayName NOT LIKE '%Library%')

AND

Collection.Guid = 'dd7c79d8-4afc-4ecd-9088-6517d1e9f00e')

GROUP BY ARP.DisplayName,

ARP.DisplayVersion,

ARP.Publisher,

ARP._SoftwareComponentGuid

 

The Collection GUID that I have specified in this sample is for the Windows 7 filter in my build.  I can change that GUID and get different results.

Does this help?