How to Write Queries about Collection Members

[televisio1]

Hello all,

I have SMS 2.5 with the SUS and toolkit add-ons. I'm trying to write a Report that will list all the members in a collection and also give a definitive yes or no on the presence of a particular MS patch as reported in each machine's Add/Remove Software listing. I know how to list a collection's members. Separately, I can figure out how to search for a patch display name from the ADD_REMOVE_SOFTWARE table view for a particular machine. But I'm not good enough with SQL to mash these two together to get the desired full listing of the collection membership (variable #1) plus a column that says "yes" or "no" to the presence of a given MS patch (variable #2) for each. Thanks in advance for any help!

 

[mojo31]

Do you have the web query tool installed. If so this is a doddle. Just search for machines with a specific file, and find the name of the exe(assuming it is) in question and voila! Gives you a list of all pc's with users etc that SMS has found with the file in question.

 

[televisio1]

Yes, thanks, I agree that finding those members who have the specific program or file is not so bad. My need is slightly different. I don't want to drop those members of the collection from my report who do not have the program. I need a full and complete list of the collection members with a 2nd column that indicates if the member has the program or not. Getting the members who do not to show up with the ones that do seems tough for SQL. It's way easy to do in a full-blown programming language but I don't have skills to twist SQL into performing this feat! Thanks again for any help.