Systems Management Question

New To SMS 2003 and trying to run a Query

11/14/2008 4597 views

Sorry to bother you but I was wanting to get some help. I have begun to take on the role of SMS admin at my workplace and have taken some classes. I was recently asked to generate a report, but I am having trouble getting exactly what they want.

Here is what they want to know:

They want a list of every application installed (especially if they have multiple versions) on all the systems at the site (or sub collection). They don't need it to be PER system, just an overall list....

They don't want the list to include office 2003 apps, drivers, or patches (office or windows).

I assume this needs to be a query run against the database that looks at Add/Remove Programs.

Not sure how to do this and put it a format that the manager types can use.

Thanks for your help!!!!!!!
0 Comments   [ + ] Show comments


Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

All Answers

You can start with this query and then tailor it to your specific needs. It displays all the applications installed on any collection -- it prompts you for the collection. In this particular case, I wanted to see a few of the updates, so excluded only specific Windows updates.

To use, create a new Query and then:
Select the option "Prompt for collection"
Click the "Edit Query Statement..." button
On the General tab, click the checkbox "Omit duplicate rows (select distinct)"
Click the Criteria tab and then click the Show Query Language button
Paste the following into the Query statement: text area
Save the query and give it a try.

[font="courier new"]select distinct SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%" and LOWER(SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName) not like "Security Update for Windows%" and LOWER(SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName) not like "Update for Windows%" and LOWER(SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName) not like "Windows XP Hotfix%" and LOWER(SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName) not like "Windows 2000 Hotfix%" and LOWER(SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName) not like "Hotfix for Windows XP%"
[font="courier new"]
[font="courier new"]Hope this gives you the starting point you need.
[font="courier new"]
[font="courier new"]Coleen Carswell
[font="courier new"]SMS Administrator II
Answered 11/18/2008 by: ColeenC
Senior Yellow Belt

I am currently working with 2 clients looking for individuals who are strong in SMS and Group Policy, both are permanent positions and one is working with the Olympics, and driving the IT infrastructure behind it. If interested please email me at [email=miclarke@teksystems.com]miclarke@teksystems.com[/email]. Any referrals are also appreciated.

Answered 11/18/2008 by: miclarke
Yellow Belt


Use the "Job Board" forum for recruitment!

Stick to the rules or you may get "banned"
Answered 11/19/2008 by: AngelD
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login


This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ