SQL Code for SCCM Status Message Viewer

I found myself juggling many different Status Message views in SCCM to try to keep on top of various messages that would arise in one environment.  So I did what anyone would do, and through liberal code-reuse and copy pasting, I reinvented the wheel.

What I’ve created here is based off of two built-in SCCM reports.  The first, Component Messages for the Last 12 Hours, and Count Errors for the last 12 hours (should be reports 80 and 89).

The output is a three-drill down view as seen here.

1
The first page is a collection of a count of messages separates by Warnings and Errors, and then a listing of each grouped by Component ID.

 

2
Clicking the link icon takes you to a listing of the warnings and errors for that site. I tried to transpose the actual message into the fields, but ran into a number of road blocks. Instead, I linked the record ID to the web status message viewer.

 

3
The Standard SQL Web Report Status Message Viewer, fed by the RecordID field from the previous report.

And Viola! It all works!

Code is available here:  https://foxdeploy.com/code-and-scripts/sql-report-for-sccm-component-status-messages/

Advertisements

SCCM Resolving Error 620 ‘The Delete Statement Conflicted with the Reference Constraint…’

Occasionally you may connect to SCCM and see this lovely sight.

01

Ah yes, who doesn’t love these pretty red icons.  Like little Christmas ornaments.  Anyway, if you’re like me, you want an all-green and all-happy SCCM Summary page, and not just because you have a scheduled task or Status Filter to reset counts every thirty minutes.  (If you’re doing this, consider yourself admonished!).

So, why does this happen and what does ‘The DELETE statement conflicted with the REFERENCE constraint’ mean?  We’re not DBAs you know!

Well, you will see this sort of message most often if you have the regularly scheduled SCCM Maintenance tasks turned on, specifically the ‘Delete Obsolete Client Data’ Task.  What is happening is that on the appointed schedule, SCCM attempts to delete all obsolete clients.  However, some Constraint is keeping the operation from completing.

I’ve seen this commonly when the obsolete computer to be removed is still referenced in a Computer Association.  Uh-oh!  As you will see in the snippet below from the smsdbmon.log file , the StateMigrationAssociation Table is specifically mentioned in the SQL Error message.  It tried to help us, it really did.

Deleting all obsolete clients that have not reported within 3 days.

*** delete System_DISC from System_DISC where ItemKey = 34879

*** [23000][547][Microsoft][ODBC SQL Server Driver][SQL Server]The DELETE statement conflicted with the REFERENCE constraint “StateMigrationAssociation_MachineIdGroupXRef_FK”. The conflict occurred in database “SCCM_TLA”, table “dbo.StateMigrationAssociation“, column ‘RestoreClientItemKey’. : System_DATA_del

CDiscoverySource_SQL::DeleteDiscoveryItem – could not execute sql- delete System_DISC from System_DISC where ItemKey = 34879

— SMS_DATABASE_NOTIFICATION_MONITOR 14456 (0x3878)

We would see this message every night at first (later every three days when I rescheduled the task because I was sick of seeing the messages.  Shame shame, I know!)  The message would repeat three sets of times, once for each ItemKey, (34879, 35618, 35975).   Well, unfortunately for us, SCCM doesn’t natively list the StateMigration ItemKey in the console for our easy deletion.

However, it turns out that if we fire up SQL and then query the dbo.System_DISC table, we can find the SCCM Resource Name!  (Thanks go out to Vivian Kiarie, she is an amazing SQL DBA and Architect, and very patient as well).

Simply read through your smsdbmon.log for the item keys listed in the error, and add them to the paranthesis at the end.

select * from dbo.System_DISC where ItemKey in (34879,35618,35975)

02

Note the Name0 Column.  This is how the association will appear in the SCCM Console.

Now, open SCCM and go to the Computer Associations area, and delete the offending Computer Associations.  You may find that the Resource in question is either the Source or the Destination for the Computer Association.  No matter, so long as the data has been restored, it is safe to remove the association and get on with life.

03

The next time that the Delete Obsolete Client Discovery Data Task runs, these records will be removed once and for all.

You’re on your way to a happier and healthier SCCM.

SCCM SQL Reporting code “Computers with one of many installed apps”

Today at a Client, the need arose to create a report which would list computers that have any of two applications installed.  The principles used could easily be modified to fit a much larger list of applications, if the need arises.

I started by modifying the canned “Search for Installed software” by stripping a number of the default prompts, and then altering the comparison line of the query to include two or comparisons, one for Mozilla Firefox and another for Google Chrome.  The result was this report, showing a count of how many instances of each version of the app was found, which was pretty neat.

02 - need to edit

It was desired to know beyond this which computers actually have a particular application installed. This was done by using the SCCM reports linking function. However, out of the box we didn’t have any good reports available, so we created one.

As I normally do, I began in the SQL Management Studio. I was able to identify the ProductName0 Column of the v_GS_INSTALLED_SOFTWARE_CATEGORIZED Table as a good identifier to locate the software we needed.  For the actual code, see the link below.

After creating our new report which asks for the input of a Product name and then shows you all of the juicy details including computers which have that product name installed, I went back to the original report in the ConfigMgr console and added a link to the newly created report. In order to link reports, you need to specify one of the columns on the output rows of one report as an input for a prompt on the receiving report. An easy tip to determine which column number is which is to mouse over the header of each line, and note the code tip in your status bar (for most browsers). We’ll mouse over the Product Name field and see what happens.

03 - need to edit

Note the ‘Col=’ bit of the asp.net url, highlighted in Yellow. This is our clue which column to select in the linked field, as depicted below.

04 - need to edit

And, now, in the final product, clicking the Linked icon in the front of any row will launch another report, listing all of the computers with this software installed.

02 - need to edit

05 - need to edit

Sorry for the somewhat gratuitous redacting of information.  Some of my clients are a bit sensitive when it comes to serial numbers and collection names, etc.

I hope that this demonstration was helpful! As always, please let me know if you see any room for improvement or if you have any questions.

Edit:  I now have a better way of doing this, which joins all product versions together.  

Thanks go out to Simon Cartin for helping me figure out how to do the IF Then and SubString commands!

Here is a screen shot of this output.

6 - new version

The code for this is also included in the below link.

All source code is available here – https://foxdeploy.com/code-and-scripts/code_computerswfirefoxorchrome/