SMS

Building SQL2000 queries the easy way (word document)

Rob — Tue, 15 Apr 2008 14:13:02 GMT

A word document (due to so many screenshots) showing how to easily create SQL2000 queries for use in SMS2003 Reporting

I get a lot of SQL query creation questions, so thought writing up a short document on how to use SQL2000 Enterprise Manager to build complicated (well reasonably) SQL queries would be helpful.

If you're experienced with SQL, then you won't learn much at all from this document. If you've shied away from writing SMS2003 Reports due to lack of SQL knowledge then this is a great aid to getting "skilled up" enough to put you on the path that'll take you forwards to carving out epic-style reports

Edit: I added the chatter about creating a proper join (including screenshots) to the document

Temporary download for Alice at FAQSHOP

Rob — Thu, 03 Apr 2008 10:50:02 GMT

Here Alice, this should work this time :>

Set SDK Site Control File Serial Number

Rob — Thu, 07 Feb 2008 12:10:45 GMT

A VBScript that will obtain the current value of the SDK Site Control File Serial number from a specified SMS site server, for a specified SMS site servers site sode and change it to a value specified on the command line.

Use this script only after you understand the underlying concepts of SDK values for Site Control files, their location in the registry, their purpose and the implications of changing them. Google Is Your Friend, so is Technet.

This is useful for changing the SDK to that lower than the value set by a parent\grand-parent SMS site server, so that Type 2 Site Control file Delta's are processed correctly by the child SMS site server and not rejected\ignored.

To determine if you have a build-up of Type 2 Site Control files, go to your Central SMS Site server, open ISQLW (SQL Query Analyzer) then change the focus to your SMS database, then type in and execute the following SQL command:

Select SiteCode, TypeFlag, SerialNumber from SiteControl where TypeFlag = '2'

If any rows are returned, you can always visit the SMS site server that these Deltas are destined for, open the SiteCtrl.log file and see if any references are made to Site Control files being ignored due to them having an older Delta value.

The delta site control file "D:\SMS\inboxes\sitectrl.box\incoming\zwfbgc5o.CT1" contains a record submitted by the SMS Administrator Console running as user <DOMAIN>\<USER> on computer <COMPUTER> at site <SITECODE> via the SMS SDK on Tue Jan 15 10:23:17 GMT. The record was assigned the serial number 2 at site <SITECODE>.

SMS_SITE_CONTROL_MANAGER will ignore this record because it was already processed as part of an older delta site control file. SMS_SITE_CONTROL_MANAGER

The first line tells you who, where and when the site control file originated from. It also tells you what SDK Delta this Site Control File change was given. The last Delta to be processed is recorded as the minimum value +1 for the next Delta that comes in. If the next Delta is lower it is ignored, as per the second line above. Just change the last known Delta value using this tool to that lower than the Delta being rejected, and it will be processed when the Delta is replicated to the server (every 1hr).

This is a loose and fast description of the entire process, please do read up and understand this subject before you use the tool. Otherwise you could be scratching your head wondering why the Site Control Manager is not processing your Site Control file changes.

02/04/2008 - Script wasn't working, a sitecode was hard-coded in, resolved so sitecode specified on command line is used. Sorry!

Manage Local Group membership

Rob — Wed, 07 Nov 2007 14:47:35 GMT

A simple script to remove a User or Group from a machines Local Group, and replace it with a domain User or Group.

The script can be tweaked so that you can point it at remote machines, but it has been designed to run locally on a machine. Ideally this should be deployed using SMS.

As it stands no error handling is included. The script will return an exit code of 0 which SMS will treat as a success.

Use at your own risk, review the code, make sure your command line is correctly formated, and finally perform testing before you deploy to machines.

Create SMS RDP Sessions

Rob — Thu, 02 Aug 2007 10:30:44 GMT

Script to generate shortcuts to MSTSC.EXE pointing at all SMS Sites discovered on a Central SMS Site server

Rather than creating these manually, modify the script to point at your Central site server, modify the destination folder the shortcuts will be created in and add the SMS Site Code of the Central, then run this script with enough rights to read the v_site view on the Central. You'll end up with a bunch of RDP sessions. Easy.

This was updated on the 6th August ... it now displays the SMS version in the RDP Shortcut description and filename.

Examples:

<SITECODE> - <SERVERNAME> - SMS2003 RTM
<SITECODE> - <SERVERNAME> - SMS2003 SP3
<SITECODE> - <SERVERNAME> - SMS2.0 SP5

It also now has a section checking for "3.00" on the front of the Version string for System Center configuration Manager, although I cannot confirm the Version string for SCCM as I'm not running it here at the moment. If you need it to spew out SCCM references, get the Version string from the V_SITE view and add that to the script. It's documented enough for you to figure it out.

Direct To Query

Rob — Wed, 13 Dec 2006 16:31:33 GMT

VBScript that will read all User, UserGroup and System Direct Membership rules from a source collection and compile them in to a query which is inserted in to a Destination collection.

This is useful for collapsing Direct Membership rules in to a single query for each type specified above. The main purpose of this script was to convert Direct Membership rules which index off the Resources ID to Query Rules that index off the resources Name.

SMS2003 Backup archival script

Rob — Wed, 10 May 2006 18:03:31 GMT

A small batch script that once invoked by the SMS backup task will take a snapshot copy of the resulting backup and archive it to a destination location. The amount of archives retained and the destination be it UNC or local drive are configurable.