SMS 2003 Report Question 1

[Grrrowler]

I'm wanting to run a report on computers in my site that don't have a specific file installed. I've got no problems running reports on computers with a specific file installed, but I need an exception report. Can anyone help me out with how to go about this either in the reporting component or with a query?

Thanks!

Todd F.

 

[MisterNiceGuy]

If I am understanding you correctly, you are looking for a way to determine what systems do not have a particular file, and when you are doing your query, you are getting just about everything including the systems that have the file because there are tons of other files stored that are not the file you are looking at. Not being especially SQL savvy, I would just create a query that returns computers having the file. Then I create query that returns all computers not in the list of computers in the first query. This is most easily done (for me) by applying the first query to a collection, then basing another collection on computers that are not a member of the first collection. Hopefully I helped and did not just confuse you more.

 

[mweible]

select SMS_R_System.ResourceID,
SMS_R_System.ResourceType,
SMS_R_System.Name,
SMS_R_System.SMSUniqueIdentifier,
SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client
from SMS_R_System
where ResourceId not in (select SMS_R_System.ResourceID from SMS_R_System
inner join SMS_G_System_SoftwareFile
on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_SoftwareFile.FileName = "myfile.exe")

Give this one a try...

 

[GSC]

mweible,
Thanks a lot! I was really looking for something like this and your query worked like a dream.
Works even better with the tip from MNG "by applying the query to a collection"

Cheers

GSC

Common sence is not so common anymore

 

[Grrrowler]

Hey mweible - That worked perfectly. Thanks for that! I know my boss will be wanting more of the same for different software in the future so that's a great query to keep.

- Todd