SCCM SQL Query for Installed Software
SCCM Configmgr SSRS Report Quick way to check if Clients are compliant or not for all the approved patches? February 6, 2016 SCCM Configmgr 2012 Troubleshoot Client software update issues. SCCM Configmgr SQL Report get list of machines with one Version of application and exclude other versions. Installed, report, SCCM. 2012 Troubleshoot Client.
- System Center Configuration Manager sccm 2012, sccm 2007, ConfigMGR 2012, ConfigMGR 2007, System Center Configuration Manager.
- SCCM Report To Get All Microsoft office versions. We’re back with new and interesting post by Manoj Kumar. Many times you get a request to track your software deployment progress. An example, you are working on new office version deployment using SCCM and you want to track the progress on install office count.
If you are using Configuration Manager, you will be aware that you can create a number of custom reports using the SQL data and SSRS to produce the reports.
The best way to start to create a custom report is to ensure that you get your SQL query correct first. We have written hundreds of various queries over the years for our own use and by request through our website. This guide will show you how to pull data from the Configuration Manager database and give you the following information:
- Machine Name
- Username
- Make
- Model
- Architecture
- RAM
- Publisher
- Program
- Version
- Install Date
- Product ID
You can add and remove any of these columns where you feel the need to customise them but for this guide, these are what we will use. By running our first query, it will bring back a huge amount of data (depending on your environment size of course) – it will basically show everything that is in the Add/Remove Programs list for every machine in your Configuration Manager database.
To do this, run the following query:
As you can probably see from the returned data, it is a lot and looks a bit of a mess. So, how can we break it down a little? Our first suggestion is to remove the possible junk from the query return, so you can add the following to the end of your query:
This will now remove any “Program” that is “NULL” or have KB in the title – this means removing all/most of the Windows Update information. If you run this query now, you should get a return which looks much tidier.
But what if I want to run this report based on a collection?
Well, you can do that too, if you have a number of set collections be it for machine types or for departments etc, then you may want to just return all installed software on all machines within the Finance department…
To do this, you first need to find the Collection ID of the Configuration Manager Collection that you want to base this query on – as I’m assuming it will be mostly System Center Pros reading this, I will assume you know how to find this, if you do not, then please leave a comment below and I will be happy to explain…
Once you have your Collection ID, you need to make a declaration in your SQL query, so you should add the following to the start of your query:
Replacing the SMS000AA with your Collection ID (do not remove the quotation marks.).
You will also need to JOIN a new table to your query, so you should add this:
Sccm Report Installed Software
Once you have done that, you need to add a WHERE statement at the bottom of the query like this:
This should be after your JOIN but before your other WHERE statements.
So, if we use the full query from above, your new SQL query which is now based only on machines within the declared Collection, it should look like this:
You should now see only machines within your declared collection and all programs that are installed on the machines (that have report Add/Remove Programs information…).
What if I want more information?
If you want to expand the data that is returned, you can always either join more tables or run the following query and select the columns that you want:
Once you are happy with your SQL query, you can then use it in SSRS to produce a professional looking report.
How Do I Create The SSRS Report?
We have now created a guide that will help you to create your SSRS reports based on the data that this guide will pull into the Configuration Manager database.
You can access this guide by clicking the link below:
More Queries
Our full range of SQL and WQL Collection queries are available here.
Feedback
Cios dvd dumper settings. If you have any questions or feedback about this post, or if you would like us to create any queries for you, please go ahead and leave us a message below in the comments section and we will get back to you as quick as we can.
Free Subscription!Subscribe to our site to receive updates via email!
Enter your email address below and click the Subscribe button to receive email notifications about new technical guides, latest news and MSI installer downloads that are available through our blog at https://blog.techygeekshome.info
The Purdue University research homepage has several resources to help you get started with research as an undergraduate. This includes a list of research opportunities sorted by college, information on what research entails, and information about the undergraduate research symposium. Summer Undergraduate Research Fellowships (SURF) Program; The SURF program provides undergraduates opportunity to gain hands-on research experience by exploring a topic of interest alongside a Purdue professor and graduate student. An undergraduate summer research program will help your application to graduate school. You have the opportunity to learn new skills and preview the graduate school experience. Purdue University offers a wide variety of summer research programs to match your abilities and interests. Summer Stay Scholar program; Other summer undergraduate research programs. Other institutions also offer summer undergraduate research programs. As we receive information about these, we will post links here. In addition, the American Foundation for Pharmaceutical Education (AFPE) offers several Gateway Undergraduate Research Fellowships. Purdue undergraduate summer research program 2017. Summer Undergraduate Research Fellowship Program The SURF program is helping students across engineering, science, and technology disciplines discover a world of opportunity available to them through research.
We will not spam you and we will not pass on any of your details to anyone else. We tend to post new content once or twice a week.
You can unsubscribe any time you like.
The following SCCM collection query can be used to list all computers with a particular application and version installed, in this example Java version 6 update 21. Firmware for samsung galaxy s2 t989.
You could use this custom collection list to deploy an update or software package only if they already have another application installed. For example, deploy a Java update only if a version of Acrobat Reader 9.x is already installed.
To use the query you will need to create a new collection and add as a Membership Query Rule.
How to create a new collection in SCCM
Sccm 2012 Software Updates Process
- With the Configuration manager Console window open
- Right-click the ‘Collections’ node and select ‘New Collection’
- Enter in an appropriate name for the collection, for example “Workstations with Office 2010 SP1”
- Click ‘Next’
- Click on the ‘Query Rule’icon (it looks like a yellow cylinder)
- Enter an appropriate name for the query and limit to your workstation collection (if required – but NOT suggested for the Apple Mac Systems query!)
- Click on the ‘Edit Query Statement’ button
- Select the ‘Show Query Language’ button
- Delete any text already in the ‘Query Statement’ box and copy and paste the query above into the box
- Click ‘OK’ and then ‘OK’ to return to the New Collection Wizard
- Click ‘Next’,‘Next’,‘Next’and then ‘Finish’
- You may need to refresh the collection before you’re able to see the clients listed, this is done by right-clicking on the new collection and selecting ‘Update Collection Membership’