Recently I had an issue where ActiveSetup wasn’t installing some applications when deployed. It turned out that it was due to some of the packaged applications not having a program with the Windows installer information populated. As it was believed that this may not an isolated issues I was tasked with producing a report of all Packages where the information was missing. This is not actually possible via the default reports as none of the views exposed to SCCM actually contain this information. The only way to get this information is to query the SCCM database table “PkgPrograms”, and to do that and have a permenant report SSRS is needed as it can run with the credentials of the user generating it or with an execution account which may have more access.
Tag: sql
SCCM Patch Compliance Reports
Updated: 2013-11-20 – Fixes for rdl files and 2012 report, I’ve edited the RDLs but I have no infrastructure currently to test on so feedback is welcome.
I was watching SCCM Guru Episode 9 with Garth Jones, when I saw in his powerpoint presentation two reports for patch compliance that I liked the look of. So I decided to see if I could emulate the reports, I will say here having only seen the final product in his slide my reports will look similar to his, and may even share code, however I have not seen ANY of his code so any similarities here are purely coincidental and not me just copying his code and taking credit.
Task Sequence and Package Management Reports
There are 3 reports in this group;
The first returns a list of all packages in a Task Sequence and the DPs they are targeted at that are in a “not installed” state. Currently this only detects targeted and not installed, once I figure out the SQL i’ll update this so it includes DPs that it is not targeted at as well.
The second lists all Task Sequences that reference a specific package, the idea here is if you wish to replace a package with a newer version or decommission a package you can get a list of all of the TSes that include it.
The third and final report compares two Task Sequences and returns a list of packages that are in one but not the other along with the ID of the Task Sequence that it is in.
Software Distribution Reports
I have 85 or so DPs across more than 5 Primary sites and sometimes I see failures in newly created Task Sequences (or even old ones) during an OS deployment due to a package not being available.
- Distribution status of a specific package on a specific distribution point – This report shows the installation status of a package on the specified distribution point.
- Distribution points without a specific package – This report shows any Distribution Points a Package is not installed on. N.B. Package MUST be targeted to the site in order to be detected
- Distribution points without a specific package in a site – This report shows any Distribution Points a Package is not installed on in the specified site. N.B. Package MUST be targeted to the site in order to be detected
- Distribution status of a specific package in a site – This report shows the installation status of a package on each of its distribution points in the specified site
Deployment Management Report
Sorry it’s taken so long but here is my 1st post (of many) since my change of Job.
In my new environment machine history isn’t retained upon a machine rebuild, so how do I determine what a machine had before it was rebuilt? Well there is no “easy” answer to this, the company historically used a vbs script to diff the uninstall reg key against a list of standard or core applications. This worked to some extent but was far from perfect and could result in very large lists of applications that were not even installed by SCCM.
SCCM All Computers, their OU and last Hardware Scan
A quick Report to return all of the computers, the OU they are in and the last time they had a Hardware scan.
First we create a temporary table to hold the 1st pass results, then we query that table looking for the longest OU for each computer ( this way we only get 1 record per computer returned, otherwise each level of the OU a computer is a member of will be returned)
SCCM Serial Numbers and Asset Tags
This is just a quick one, if you have dell machines (not sure if other manufacturers do it) but one of the BIOS options is ASSET TAG. Now this does not, as you would expect, appear in Win32_BIOS, instead it appears under Win32_SystemEnclosure.
Forefont Endpoint Protection Query Details
Those of us that installed FEP 2010 are no doubt annoyed that we cannot see the details of the queries used by SCCM to populate the FEP created collections.
The following will require acess to the SQL instance that SCCM uses as the querys are to be executed driectly on that instance.