query – NINet.org

TFS Warehouse Cube Status via SOAP and Powershell

October 31, 2012November 18, 2012 Ryan 2 Comments

Well I’ve just been put in charge of managing our TFS infrastructure so here is the first of hopefully many posts around automating and managing TFS.

The attached script runs a SOAP query against the TFS Warehouse Cube and returns the jobs and their status and then either generates an HTML file or sends an HTML email with the results.

I recommend creating a directory called Scripts on the C:\ drive and setting the permissions so that only the System Administrator & User the script runs as can access the contents of the directory.

Programs without MSI Product ID

April 5, 2012March 20, 2013 Ryan Leave a comment

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.

SCCM Patch Compliance Reports

April 3, 2012November 20, 2013 Ryan 51 Comments

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

March 2, 2012March 20, 2013 Ryan Leave a comment

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.

Deployment Management Report

February 28, 2012March 20, 2013 Ryan Leave a comment

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.

Dell & HP Warranty Info in SCCM

July 7, 2011March 20, 2013 Ryan 55 Comments

Updated: 2011-08-18

This is the next evolution in the warranty info fight, Dell keep changing their site which is rather frustrating. I have re-written the Warranty Info Scripts in powershell as in my opinion the code is a lot cleaner and more manageable, I’ve tweaked the MOF edit required to read this.

One major change to the script is that I have added support for HP systems, now I must stress I only have one or two of these, both are out of warranty and only had base warranties so something may have been missed / may not work as expected. You can either fix it and let me know the changes you make or let me know, via the contact page, the tag of one of your systems and I will look at it when I get a chance.

If you use any other version of the script from my site you will need to update sms.mof as the reports have changed.

I am quite shameless so if you find this useful feel free to click on donate and well, donate. :D

What info do you get?

SCCM All Computers, their OU and last Hardware Scan

June 23, 2011March 20, 2013 Ryan 2 Comments

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

April 10, 2011March 20, 2013 Ryan 2 Comments

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

February 7, 2011March 20, 2013 Ryan 2 Comments

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.

SCCM restrict collection membership

September 8, 2010March 20, 2013 Ryan Leave a comment

The snippet allows one to create a collection containing objects that are in some OUs but NOT IN another collection