Steve Thompson at myITforum.com

How to list static values in a query

2008-11-30T16:01:31Z

Received an interesting question recently from a friend:

~~~

Currently this query does not work. Basically all I want is this query to return all of the data within the {} as one row for each.

Select * from
From {'Unknown date', '30 Days', '60 Days', '90 Days', '6 Months', '< 1 Year', '< 2 Years', '< 3 Years', '> 3 Years')

So my question is how can I fix this query to get this to work?

~~~

Probably the easiest way would be use of a look up table, insert these values into a table, then use a

select * from LookupTable

Option 1

However, without using a lookup table, you could create a temporary table that is created as part of a batch. Values get inserted into the temp table, then you can select from that temporary table.

-- Batch statement

DROP TABLE #T1
GO
CREATE TABLE #T1
(
    column_1 varchar(30)
);
GO
SET NOCOUNT ON;
INSERT INTO #T1 (column_1)
    VALUES ('Unknown date');
INSERT INTO #T1 (column_1)
    VALUES ('30 Days');
INSERT INTO #T1 (column_1)
    VALUES ('60 Days');
INSERT INTO #T1 (column_1)
    VALUES ('90 Days');

-- add any additional values
GO
SELECT column_1 FROM #T1;
GO

-- results

Option 2

A second method that would also accomplish the same thing without using a temporary table would be use of a UNION clause.

SELECT 'Unknown date' as [Date Range]
UNION
SELECT '30 Days' as [Date Range]
UNION
SELECT '60 Days' as [Date Range]
UNION
SELECT '90 Days' as [Date Range]
UNION
SELECT '6 Months' as [Date Range]

Installing SCCM 2007 R2 SQL Server Reporting Services

2008-11-12T16:25:00Z

I've recently had a chance to work with Microsoft System Center Configuration Manager (SCCM) 2007 SQL Reporting Services Reporting; which is new with the ConfigMgr R2 release.

First impressions are quite positive, with reporting being taken to a new level of robustness. Before proceeding, you'll need to install SQL Server Reporting Services on SQL Server.

The installation steps on ConfigMgr are fairly straightforward.

SCCM 2007 R2 SSRS is a new Site Role.

First we select the role:

Choose a Report Folder Name, it will default to 'ConfigMgr_<site code>':

Summary screen is presented:

Open the SCCM 2007 MMC, under the Reporting Node, you should see the Reporting Service node contain information detailing your installation location.

SQL Views documentation is Live!

2008-10-30T18:38:09Z

A while back, I was able to provide some assistance to Garth Jones in documenting the existing SMS/SCCM SQL Server views. Doug Eby, a Senior Technical Writer at Microsoft created some fine documentation, in part based on our work. Doug did an outstanding job putting this together, the schema view, is well, priceless!

Documentation link available here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=87BBE64E-5439-4FC8-BECC-DEB372A40F4A&displaylang=en.

His blog posting is here:

http://blogs.technet.com/wemd_ua_-_sms_writing_team/archive/2008/10/30/announcement-the-creating-custom-reports-by-using-configuration-manager-2007-sql-views-documentation-package-is-now-available-for-download.aspx.

Feedback is welcomed!

SQL Server Reporting Services Resources

2008-10-07T22:17:02Z

I've recently finished an interesting project that made heavy use of SQL Server 2005 Reporting Services. As you are probably aware, Systems Center Configuration Manager 2007 R2 includes SSRS, I'd like to share some of what I've learned with a series of blogs about this technology and the tools available to author reports.

In sorting out SSRS, I spent time with a few books that are worthy of mention.

Books

Hitchhiker's Guide to SQL Server 2000 Reporting Services - 2005 - Peter Blackburn & William Vaughn (Addison Wesley). ST note: while slightly dated much of the material remains relevant, I'm proud to have a copy signed by the author.

Hitchhiker's Guide to Visual Studio and SQL Server Seventh Edition - 2007 - William Vaughn with Peter Blackburn (Addison Wesley). ST note: there is one chapter that speaks to the updates to SSRS 2005, and much valuable information on using Visual Studio to interact with SQL Server

Pro SQL Server 2005 Reporting Services - 2006 - Rodney Landrum & Walter Voytek (Apress). ST Note: nice description on how to integrate parameter driven reports and stored procedures.

Microsoft SQL Server 2005 Administrator's Companion - 2007 - Whalen, Garcia, Patel, Misner & Isakov (Microsoft Press). ST note: Very good general guide for SQL Server 2005, one chapter on Reporting Services, good overview.

Microsoft MVP 2009 Award - Configuration Manager

2008-10-02T15:46:58Z

I was both humbled and honored to receive the MVP award, effective 10/1/2008, for contributions to the technical community for Microsoft SMS 2003 & System Center Configuration Manager 2007.

Edited version of the email follows:

Subject: Congratulations 2009 Microsoft MVP!

Dear Steve Thompson,

Congratulations! We are pleased to present you with the 2009 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others.

The Microsoft MVP Award provides us the unique opportunity to celebrate and honor your significant contributions and say "Thank you for your technical leadership."

Toby Richards
General Manager
Community Support Services

At Microsoft, we believe that technical communities enhance people's lives and the industry's success because independent experts, like you, help others extract greater value from products and technologies through the free and objective exchange of knowledge. As a Microsoft MVP, you are part of a highly select group of experts that represent technology's best and brightest who share a deep commitment to community and a willingness to help others.

On behalf of everyone at Microsoft, thank you for your contributions to technical communities.

It's been a great experience being part of this community. Please allow me a moment to reflect... I was first awarded MVP status in December 1995 for contributions in Compuserve forums (how many have used Compuserve?? ;) for Microsoft Access. I still have the MVP award letter -- yes, Microsoft sent letters in those days.

I started using these forums to learn Microsoft Access, in the learning process; by answering questions and helping members, it deepened my technical knowledge of the product. Along the way I was recognized by Microsoft for my technical contributions, and awarded MVP status.

In the late 90's when it appeared that Microsoft was going to let Microsoft Access, specifically Jet (the database engine) die a slow death, I transitioned to the Microsoft SQL Server MVP program. That was a great experience, allowing me to further my understanding of SQL Server which made me better at SMS in general. The MVP community in it's entirety is an awesome group, the System Center folks made me feel part of their world, even though, formally I was part of another product group.

Now that I am part of the System Center MVP group, I look forward to things to come.

And, watch this space for continued contributions; I'm planning a series of blogs on SQL Server Reporting Services. I'm always open to suggestions as well.

Installing and configuring Local SQL Server Reporting Services

2008-09-18T19:54:28Z

Your installation may work the first time, if so great! I was not so lucky...

Attempted to launch the Report manager web page and it failed to load.

I was able to find the following error in the Reporting Services log file:

e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.RPCException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled), ;

A search of Microsoft knowledge base turned up the following article:

Error message when the Report Server service starts or when you try to access the Reporting Services Web site in SQL Server 2008: "Key not valid for use in specified state"

The cause was apparently related to changing the Reporting service account after the product was installed.

Since I had no backup of the symmetric key, I chose method 2 Delete the encrypted content.

Back up and running!

Microsoft SQL Server sample databases, code, and much more...

2008-09-17T15:58:51Z

Can be found on codeplex, there are many community based projects available for download including Microsoft Reporting Services Samples.

Microsoft SQL Server 2005 Reporting Services

2008-09-17T15:30:00Z

Over the past few weeks, I've been delving deeper into Microsoft SQL Server 2005 Reporting Services (SSRS). In case you are not aware, there are downloads available that allow you to work with SSRS in a local mode, for example on a laptop/desktop. This is great for either learning, or building SSRS reports.

Here are the two main components that you'll need to install.

Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 2

Brief Description

Microsoft SQL Server 2005 Express Edition with Advanced Services is a free, easy-to use version of SQL Server Express that includes more features and makes it easier than ever to start developing powerful data-driven applications for web or local desktop development.

Overview

Microsoft SQL Server 2005 Express Edition with Advanced Services (SQL Server Express) is a free, easy-to-use version of SQL Server Express that includes a graphical management tool and powerful features for reporting and advanced text-based searches. SQL Server Express provides powerful and reliable data management tools and rich features, data protection, and fast performance. It is ideal for embedded application clients, light Web applications, and local data stores.
SQL Server Express with Advanced Services has all of the features in SQL Server 2005 Express Edition, plus you can:

Easily manage and administer SQL Server Express with an easy-to-use graphical management tool -- SQL Server 2005 Management Studio Express (SSMSE).
Issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.
Run SQL Server Reporting Services reports on local relational data.

Free to download, free to deploy, and free to redistribute as an embedded part of an application, SQL Server Express with Advanced Services is the fast and easy way to develop and manage data-driven applications with powerful built-in reporting and full-text search functionality.
For more information about SQL Server Express, including other editions and downloadable components now available, see the SQL Server Express page on MSDN.
For a list of new features and improvements that are included in SQL Server 2005 SP2, review the What's New document.

Microsoft SQL Server 2005 Express Edition Toolkit

Brief Description

Microsoft SQL Server 2005 Express Edition Toolkit provides additional tools and resources for SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services.

SQLEXPR_TOOLKIT.EXE

Free Download Manager...

2008-09-17T15:11:47Z

Every now and then I discover a software product that is excellent. For the past few months, I've been working remotely using a USB data card for connectivity. This can be a challenge when downloading very large files via FTP. Enter Free Download Manager -- it allows you to checkpoint downloads so a connection interrupt will allow you to re-continue a download. Best of all it's free. :)

Microsoft System Center User Groups in Southern California?

2008-09-15T16:24:28Z

I'm looking for any existing Microsoft System Center (centric) User Groups located in Southern California; either San Diego or Orange County. Or, if there is interest in starting one, please send me an email via contact link. Thanks!

Robotic BigDog is US army's best friend

2008-09-05T22:34:12Z

Seems like robotics has come a very long way...

http://www.telegraph.co.uk/news/worldnews/northamerica/usa/2687038/Robotic-BigDog-is-US-armys-best-friend.html

How to find computer NIC's with Speed/Duplex not set to 'Auto'?

2008-09-03T19:10:24Z

This may sound like an odd request, if you've worked in information technology long enough, especially with SMS/ConfigMgr, you've probably gotten a lot of strange requests.

Scenario: Network switches are being replaced, any computer on the network with a NIC that has it's Speed & Duplex property set to something other than 'Auto', will fail to connect to the network and communicate with a DHCP server. Not a good thing. To further complicate this picture, even though computers were originally built with images using a NIC Speed & Duplex set to 'Auto'. Desktop support would occasionally set the NIC to 10 Mb Full in an attempt to correct network connection issues. We need to identify and correct these computers prior to the network hardware being swapped to avoid panic calls to the help desk.

Solution: Create a custom VBScript that is run on every desktop via SMS/ConfigMgr, leaving a file with a unique name and file type of '*.flg' that could be easily queried and/or used to create a collection of computers that could be corrected via automation.

Script: The information we need to locate is in the registry, further, I wanted the ability to log the script activity locally, much like SMS logging. The flag file created for inventory needed to be easy to aggregate via SQL queries. This script was tested with an Intel NIC, other manufactures may use different registry key locations.

Flag file format followed by the possible numeric values:

' flag file to be constructed as SMSEng-NIC-<SpeedDuplex>-<DriverDescription (first 5 char)>

'0 - Auto Detect
    '1 - 10Mbps \ Half Duplex
    '2 - 10Mbps \ Full Duplex
    '3 - 100Mbps \ Half Duplex
    '4 - 100Mbps \ Full Duplex

LocateNIC.vbs

' begin VBscript

Option Explicit
'On Error Resume Next

' Begin log stuff
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

Dim strIN, strTextIn
Dim objFSO, objDictionary, objTextFile, objItem
dim strNextLine
dim i
dim FSOOut
dim Overwrite
dim Unicode
dim FileName, FileOut

Dim strFileIn
Dim strFileOut
Dim f
Dim strMsg
' End log stuff

' Flag file
Dim strFlagFile
Dim fFlag
Dim strSMSRoot

Dim strComputer
dim objReg, strKeyPath, subkey, arrSubKeys, dwValue
dim strValue
dim strDriverValue, strModeValue2
Dim strSpeedDuplex
Dim strDriverDesc
Dim strSubkey

Const HKEY_LOCAL_MACHINE = &H80000002

' log file
strSMSRoot = "C:\Program Files\sms"
strFileOut = "SMSEng-NIC.log"

' Verify/Create Folders
' Flag folder
Call VerifyCreateFolder(strSMSRoot & "\flg")
Call VerifyCreateFolder(strSMSRoot & "\flg\SMSEng-NIC")

' Delete Any Flag files...
Call DeleteFlagFiles(strSMSRoot & "\flg\SMSEng-NIC\*.flg")

' Log folder
Call VerifyCreateFolder(strSMSRoot & "\log")
Call VerifyCreateFolder(strSMSRoot & "\log\SMSEng-NIC")

' file out
Set FSOOut = WScript.CreateObject("Scripting.FileSystemObject")
Overwrite = False
Unicode = False

' start log file
' open client log file
Call OpenClientLog

strMsg = "Started Script Process..."
Call WriteClientLog (strMsg)

' flag file to be constructed as SMSEng-NIC-<SpeedDuplex>-<DriverDescription (first 5 char)>

' test
'strFlagFile = "SMSEng-NIC-0-INTEL.flg"
'Call CreateFlagFile (strFlagFile)

strComputer = "."
Set objReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\default:StdRegProv")
strKeyPath = "System\Currentcontrolset\Control\Class\{4D36E972-E325-11CE-BFC1-08002be10318}"
strDriverValue = "DriverDesc"
strModeValue2 = "SpeedDuplex"

objReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys

For Each subkey In arrSubKeys

    '0 - Auto Detect
    '1 - 10Mbps \ Half Duplex
    '2 - 10Mbps \ Full Duplex
    '3 - 100Mbps \ Half Duplex
    '4 - 100Mbps \ Full Duplex
    ' Capture Driver Description
    objReg.GetStringvalue HKEY_LOCAL_MACHINE, strKeyPath & "\" & subkey, strDriverValue, strValue
    strDriverDesc = strValue
    strSubkey = subkey

    ' Capture SpeedDuplex
    objReg.GetStringvalue HKEY_LOCAL_MACHINE, strKeyPath & "\" & subkey, strModeValue2, dwValue
    strSpeedDuplex = CStr(dwValue & "")
'    WScript.Echo " " & strModeValue2 & ": " & strSpeedDuplex
    If Len(strSpeedDuplex & "") > 0 then

strFlagFile = "SMSEng-NIC-" & strSpeedDuplex & "-" & LEFT(UCASE(strDriverDesc),5) & ".flg"
Call CreateFlagFile (strFlagFile)

      ' log entries
      strMsg = "Create Flag file: '" & strFlagFile & "'"
      Call WriteClientLog (strMsg)

      strMsg = "... subkey: '" & strSubkey & "' Full Driver Description: '" & strDriverDesc & "'"
      ' wscript.echo "strMsg: " & strMsg
      Call WriteClientLog (strMsg)

    Else
      strMsg = "... subkey: '" & strSubkey & "' Full Driver Description: '" & strDriverDesc & "'"
      Call WriteClientLog (strMsg)
    ' wscript.echo "strMsg: " & strMsg
    End If

' End If
Next

' Create Flag file subs
Sub CreateFlagFile (strFlagFile)
' strFileOut = strSMSRoot & "\log\SMSEng-NIC.log"

' On Error Resume Next

   Set fFlag = FSOOut.OpenTextFile(strSMSRoot & "\flg\SMSEng-NIC\" & strFlagFile, ForAppending, True)
   fFlag.Write Now()
   fFlag.Close

End Sub

' Begin log subs
Sub OpenClientLog

On Error Resume Next

Set f = FSOOut.OpenTextFile(strSMSRoot & "\log\SMSEng-NIC\" & strFileOut, ForAppending, True)

End Sub

Sub CloseClientLog

On Error Resume Next
f.Close

End Sub

Sub WriteClientLog (LineOut)

On Error Resume Next
f.Write Now() & "," & LineOut & vbCRLF

End Sub
' End log subs
Sub VerifyCreateFolder(fldr)
Dim lfso, lmsg, lf
Set lfso = CreateObject("Scripting.FileSystemObject")

   On Error Resume Next
   If (lfso.FolderExists(fldr)) Then
'      msg = fldr & " exists."
   Else
'      msg = fldr & " doesn't exist."
      Set lf = lfso.CreateFolder(fldr)
   End If
'   ReportFolderStatus = msg
End Sub

Sub DeleteFlagFiles(FlagFiles)

   Dim lfso, lmsg, lf
   Set lfso = CreateObject("Scripting.FileSystemObject")
   On Error Resume Next
   lfso.DeleteFile(FlagFiles)

End Sub

' End VBscript

Hopefully this gives you some ideas for your own 'special' requests. The next blog will illustrate how to fix these NIC's, with automation, of course. :)

How to change computer NIC Speed/Duplex property to 'Auto'?

2008-08-18T22:13:05Z

In another blog entry

How to find computer NIC's with Speed/Duplex not set to 'Auto'?

I described how to identify computers where the speed duplex is not set to auto. This article explains how to automate correcting those computers where the NIC Speed/Duplex property was not set correctly.

Brief recap, we are able to identify those computers that need to be corrected by inventorying the file type *.flg and creating a collection based on those that are not set to auto detect. Then distribute a advertisement that will run the script to set the NIC property back to Auto. Now, since the script will cause the NIC to temporarily reset, be sure to use the download and execute mode for your advertisement.

This script was written for and tested on Intel and Dell laptop NIC's. It has the added advantage of logging change activity to the local desktop, and recreating the new *.flg file so the corrected computers will fall out of the targeted collection.

As always, test in a lab environment before deploying.

' NIC-Fix-v2.vbs

' Steve Thompson
'
' Detect and revise those network adapters that are not set to auto detect
' v1

Option Explicit
'On Error Resume Next

' Begin log stuff
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

Dim strIN, strTextIn
Dim objFSO, objDictionary, objTextFile, objItem
dim strNextLine
dim i
dim FSOOut
dim Overwrite
dim Unicode
dim FileName, FileOut

Dim strFileIn
Dim strFileOut
Dim f
Dim strMsg
' End log stuff

' Flag file
Dim strFlagFile
Dim fFlag
Dim strSMSRoot

Dim strComputer
dim objReg, strKeyPath, subkey, arrSubKeys, dwValue
dim strValue
dim strDriverValue, strModeValue2, strModeValue3
Dim strSpeedDuplex
Dim strRequestedMediaType
Dim strDriverDesc
Dim strSubkey

Dim strModNewValue, strModSubkey

Const HKEY_LOCAL_MACHINE = &H80000002

' log file
strSMSRoot = "C:\Program Files\sms"
strFileOut = "SMSEng-NIC.log"

' Verify/Create Folders
' Flag folder
Call VerifyCreateFolder(strSMSRoot & "\flg")
Call VerifyCreateFolder(strSMSRoot & "\flg\SMSEng-NIC")

' Delete Any Flag files...
Call DeleteFlagFiles(strSMSRoot & "\flg\SMSEng-NIC\*.flg")

' Log folder
Call VerifyCreateFolder(strSMSRoot & "\log")
Call VerifyCreateFolder(strSMSRoot & "\log\SMSEng-NIC")

' file out
Set FSOOut = WScript.CreateObject("Scripting.FileSystemObject")
Overwrite = False
Unicode = False

' start log file
' open client log file
Call OpenClientLog

strMsg = "Started Process..."
Call WriteClientLog (strMsg)

' flag file to be constructed as SMSEng-NIC-<SpeedDuplex>-<DriverDescription (first 5 char)>

' test
'strFlagFile = "SMSEng-NIC-0-INTEL.flg"
'Call CreateFlagFile (strFlagFile)

objReg.EnumKey HKEY_LOCAL_MACHINE, strKeyPath, arrSubKeys

For Each subkey In arrSubKeys

    ' Capture SpeedDuplex
    objReg.GetStringvalue HKEY_LOCAL_MACHINE, strKeyPath & "\" & subkey, strModeValue2, dwValue
    strSpeedDuplex = CStr(dwValue & "")

    ' Capture RequestedMediaType (Broadcom - DELL)
    objReg.GetStringvalue HKEY_LOCAL_MACHINE, strKeyPath & "\" & subkey, strModeValue3, dwValue
    strRequestedMediaType = CStr(dwValue & "")

    '    WScript.Echo " " & strModeValue2 & ": " & strSpeedDuplex
    If Len(strSpeedDuplex & "") > 0 then
      If strSpeedDuplex <> "0" Then
        ' modify the subkey for SpeedDuplex
        strModNewValue = "0"
        strModSubkey = strModeValue2
        Call ModifyDuplexSetting (strModSubkey, strModNewValue)

        ' Get SpeedDuplex, one more time for flag file, should be different!
        objReg.GetStringvalue HKEY_LOCAL_MACHINE, strKeyPath & "\" & subkey, strModeValue2, dwValue
        strSpeedDuplex = CStr(dwValue & "")
      End If

strFlagFile = "SMSEng-NIC-" & strSpeedDuplex & "-" & LEFT(UCASE(strDriverDesc),5) & ".flg"
Call CreateFlagFile (strFlagFile)

        ' log entries
        strMsg = "*** Create MODIFIED Flag file: '" & strFlagFile & "' ***"
        Call WriteClientLog (strMsg)

        strMsg = "... subkey: '" & strSubkey & "' Full Driver Description: '" & strDriverDesc & "'"
        ' wscript.echo "strMsg: " & strMsg
        Call WriteClientLog (strMsg)

    ElseIf Len(strRequestedMediaType & "") > 0 Then
      If strRequestedMediaType <> "0" Then
        ' modify the subkey for RequestedMediaType (Broadcom - DELL)
        strModNewValue = "0"
        strModSubkey = strModeValue3
        Call ModifyDuplexSetting (strModSubkey, strModNewValue)

        ' Get RequestedMediaType (Broadcom - DELL), one more time for flag file
        objReg.GetStringvalue HKEY_LOCAL_MACHINE, strKeyPath & "\" & subkey, strModeValue3, dwValue
        strRequestedMediaType = CStr(dwValue & "")
       End If

strFlagFile = "SMSEng-NIC-" & strRequestedMediaType & "-" & LEFT(UCASE(strDriverDesc),5) & ".flg"
Call CreateFlagFile (strFlagFile)

        ' log entries
        strMsg = "*** Create MODIFIED Flag file: '" & strFlagFile & "' ***"
        Call WriteClientLog (strMsg)

        strMsg = "... subkey: '" & strSubkey & "' Full Driver Description: '" & strDriverDesc & "'"
        ' wscript.echo "strMsg: " & strMsg
        Call WriteClientLog (strMsg)

    Else
      strMsg = "... subkey: '" & strSubkey & "' Full Driver Description: '" & strDriverDesc & "'"
      Call WriteClientLog (strMsg)
    ' wscript.echo "strMsg: " & strMsg
    End If

' End If
Next

strMsg = "End Process..."
Call WriteClientLog (strMsg)

' Create Flag file subs
Sub CreateFlagFile (strFlagFile)
' strFileOut = strSMSRoot & "\log\SMSEng-NIC.log"

' On Error Resume Next

   Set fFlag = FSOOut.OpenTextFile(strSMSRoot & "\flg\SMSEng-NIC\" & strFlagFile, ForAppending, True)
   fFlag.Write Now()
   fFlag.Close

End Sub

' Begin log subs
Sub OpenClientLog

On Error Resume Next

Set f = FSOOut.OpenTextFile(strSMSRoot & "\log\SMSEng-NIC\" & strFileOut, ForAppending, True)

End Sub

Sub CloseClientLog

On Error Resume Next
f.Close

End Sub

Sub WriteClientLog (LineOut)

On Error Resume Next
f.Write Now() & "," & LineOut & vbCRLF

End Sub
' End log subs
Sub VerifyCreateFolder(fldr)
Dim lfso, lmsg, lf
Set lfso = CreateObject("Scripting.FileSystemObject")

Sub DeleteFlagFiles(FlagFiles)

   Dim lfso, lmsg, lf
   Set lfso = CreateObject("Scripting.FileSystemObject")
   On Error Resume Next
   lfso.DeleteFile(FlagFiles)

End Sub

Sub ModifyDuplexSetting (strModSubkey, strModNewValue)
On Error Resume Next

objReg.SetStringvalue HKEY_LOCAL_MACHINE, strKeyPath & "\" & subkey, strModSubkey, strModNewValue
End Sub

Use of 'flag' files and software inventory...

2008-08-15T18:19:47Z

This is a technique that we pioneered in the SMS 2.0 days, and has continued to find useful applicability.

There a number of techniques to inventory computers, and locate resources within ConfigMgr. However, have you ever found those unique situations where the normal methods of hardware/software inventory either do not work, or require a lot of effort to obtain?

This is a two part process:

1) Enable software inventory of file type: '*.flg'

2) Use a script, package, gpo, login script, etc. to create a <filename>.flg on the computer

ConfigMgr software inventory will find and process the <filename>.flg file and store it as software data for the computer. Now, it is very easy to locate those computers with a web reports, or use a query, which could in turn be the basis for a collection. By using a custom file type, we will likely only process those files that are useful to us without a lot of background noise or huge number of inventory records. Ever enable software collection on *.dll? :)

My next blog post will illustrate a practical example of this technique.

The perfect storm of desktop updates this month?

2008-08-13T16:00:23Z

Interesting article by Gregg Keizer.

Microsoft issues massive security update for Windows, Office

Service Accounts - Part 2 of 2

2008-08-12T16:37:08Z

This script was the outcome of researching a way to automate the replacement of a local service account to manage the cluster service. This automation could be used to change any account for any service.

Note that the script logs the sequence as the script proceeds to the local event log -- this serves both as an audit requirement and as a diagnostic tool.

When changing out the service account, it does require stopping the Cluster Server service, be sure to change one node at a time to allow the cluster fail-over to take place. We sequenced the updates allowing one hour interval between cluster nodes.

ModifyServiceAccount.vbs

' Created by Steve Thompson

' v1.00
'
Option Explicit
Const EVENT_SUCCESS = 0
Const EVENT_FAILURE = 1
Const EVENT_WARNING = 2
Dim strComputer
Dim strAccount
Dim objWMIService
Dim colServiceList
Dim strErr
dim objShell
Dim strLogData

On error resume next
Set objShell = WScript.CreateObject("WScript.Shell")

strComputer = "."

' expects arguments passed as part of the command line

' arg1 - domain\account

' arg2 - password
strAccount = WScript.Arguments.Unnamed.Item(0)
strPassword = WScript.Arguments.Unnamed.Item(1)

' wscript.echo "strAccount= '" & strAccount & "'"

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")

Set colServiceList = objWMIService.ExecQuery _
("Select * from Win32_Service Where Name = 'ClusSvc'")

For each objService in colServiceList

if UCASE(objService.Name) = "CLUSSVC" Then

      ' stop service
       objService.StopService()
       strErr = err
       strLogData = "Stop Service step -- '" & objService.Name & "'. Return Code('" & strErr & "')"
       WriteEventLog EVENT_SUCCESS, strLogData

       ' wait
       Wscript.Sleep 20000

      ' change out service account
       strErr = objService.Change _
         ( , , , , , , strAccount, strPassword)
       strErr = err
       strLogData = "Change Service Account and password step -- " & "Return Code('" & strErr & "')"
       WriteEventLog EVENT_SUCCESS, strLogData

       ' restart service
       objService.StartService()
       strErr = err
       strLogData = "Re-start Service step -- '" & objService.Name & "Return Code('" & strErr & "')"
       WriteEventLog EVENT_SUCCESS, strLogData
   end if
Next

Sub WriteEventLog (zStatus, zData)

objShell.LogEvent zStatus, zData

End Sub

Service Accounts - Part 1 of 2

2008-08-11T17:59:34Z

Microsoft best practice for service accounts, is use the concept of least permissions for the accounts. In other words, if an account needs to have local administrative rights on a server or desktop, add the account to the local administrators group. Do not make the account a Domain Administrator!

In researching Service Accounts that were in use at my last company, we determined that an account commonly used as a Cluster Service (to administer the server Cluster) account was a Domain Administrator account. The solution, was creation of a standard service account with user level permissions, then adding this user account to the local Administrators group and applying the proper nt rights to the account.

Now, how to automate replacing this account on 60 or more server nodes? We created a package in SMS/SCCM with a command file that accepts the name of the domain user account in the format of domanname\account making the command file generic. After experimentation we found the resource kit utility ntrights.exe could adjust the nt rights (the ones listed below are required for a service account). Just place the ntrights.exe in the same package folder as the cmd file.

Tomorrow's blog will be use of automation to swap out the old service account and adding in the new one.

ntrights-modify-admin.cmd

@echo on
REM Pass DomainName\Account to this cmd file
REM Add account to local administrators group...

NET LocalGroup Administrators %1 /add

REM Assign proper rights to the account...

ntrights +r SeIncreaseQuotaPrivilege -u %1
ntrights +r SeLockMemoryPrivilege -u %1
ntrights +r SeServiceLogonRight -u %1
ntrights +r SeTcbPrivilege -u %1

Automate creation of SNMP Trap Destinations with SMS/ConfigMgr!

2008-08-08T22:51:04Z

We had a situation where 1400+ servers needed to be able to communicate with a second SNMP server that was added as a backup to receive SNMP trap alerts.

Changing this information manually would be far too intensive.

I developed the following VBScript that was deployed by SMS to make the revision. Note that you would modify the 'SERVERNAME1' and 'SERVERNAME2' values below. The value for <identifier> relates to unique name that a company would choose.

The SNMP service gets recycled after the update so the trap destination change takes effect immediately.

Enjoy, as always test in a lab before deploying to 1000's of servers ;)

' watch word wrap!

OPTION EXPLICIT
const HKEY_LOCAL_MACHINE = &H80000002
dim strKeyPathRoot
dim strKeyPath
dim strComputer
dim strESXHost
dim strValueName
dim oReg
dim strValue
dim strUpdateMode
dim dwValue
Dim objWMIService
Dim colServiceList
DIM objService

' test mode reads and reports
'strUpdateMode = "Test"
' Update mode updates reg keys and does not report
strUpdateMode = "Update"

strComputer = "."

' adjust SNMP parms
SetSNMP

' recycle SNMP service
RecycleSNMP

' Set StdOut = WScript.StdOut
Sub SetSNMP

Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
strComputer & "\root\default:StdRegProv")

strKeyPathRoot = "SYSTEM\CurrentControlSet\Services\SNMP\Parameters"

' read TrapConfig key, if not found create new!
strKeyPath = strKeyPathRoot & "\TrapConfiguration\<identifier>\"
strValueName = "1"
oReg.GetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
if strUpdateMode = "Test" Then
    wscript.echo "strValue = " & strValue
    wscript.echo "strValueName = " & strValueName
end if
' strValue contains a NULL if not found...

     strKeyPath = strKeyPathRoot & "\TrapConfiguration\<identifier."
'     wscript.echo "strKeyPath = " & strKeyPath
     oReg.CreateKey HKEY_LOCAL_MACHINE, strKeyPath

    strValue = "SERVERNAME1"
    strValueName = "1"
    oReg.SetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue

    strValue = "SERVERNAME2"
    strValueName = "2"
    oReg.SetStringValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue

End Sub

Sub RecycleSNMP
' stop & restart service...
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")

Set colServiceList = objWMIService.ExecQuery _
("Select * from Win32_Service Where Name = 'SNMP'")
' ("Select * from Win32_Service Where Name = 'ccmexec'")

For each objService in colServiceList

if UCASE(objService.Name) = "SNMP" Then

       ' wscript.echo "objService.Name= '" & objService.Name & "'"
       ' stop service
       objService.StopService()
'     strErr = err
'     strLogData = "Stop Service step -- '" & objService.Name & "'. Return Code('" & strErr & "')"
'     WriteEventLog EVENT_SUCCESS, strLogData
       ' wait
       Wscript.Sleep 20000
       ' change out service account
'      strErr = objService.Change _
'        ( , , , , , , strAccount, "nt$local")
'      strErr = err
'      strLogData = "Change Service Account and password step -- " & "Return Code('" & strErr & "')"
'      WriteEventLog EVENT_SUCCESS, strLogData

       ' restart service
       objService.StartService()
'       strErr = err
'       strLogData = "Re-start Service step -- '" & objService.Name & "Return Code('" & strErr & "')"
'       WriteEventLog EVENT_SUCCESS, strLogData
   end if
Next
End Sub

Technorati Tags: vbscript,sms 2003,sccm 2007

Earthquake country...

2008-08-01T18:47:55Z

On Tuesday, I was in San Clemente, CA meeting with my manager when an earthquake occurred. It was my first experience with an earthquake, a large convex mirror in the corner of the room started shaking, hanging lights started swinging... lady at a nearby table stands up, looking a little scared, clutching the table and says: "oh my, this is a good one!"

I'm sitting at the table with two folks that have had far more experience in earthquake country than I -- they are staying put. Meanwhile, I'm thinking, do I run out of the building, or hide under the table for protection?! My manager is smiling.

When the earthquake ended, he says: "That was about a 5.5." Turns out that was fairly accurate, it was a 5.4 magnitude quake, epicenter was in Chino Hills, CA.

Technorati Tags: misc

California arrival

2008-07-16T00:04:00Z

We arrived in Newport Beach CA today, it took us 7 days to drive cross country. Approximately 3100 mi in all.

At the end of the day yesterday we crossed into CA, drove from the high desert of New Mexico, into Flagstaff AZ, then south to Phoenix, and west on I-10 to CA. Spent the night in Blythe, CA which is on the Colorado river. It is interesting to drive through the Arizona desert, and see so much green, it looked like the Nile river after the desert. This morning it was 83F at 7:30am... it was 105F yesterday. The locals told us it was cool... they were expecting 120F temps later this week.

It was a great experience... if you ever get the opportunity and don't mind driving, I highly recommend a cross country trip.

Day 4 on the road...

2008-07-13T01:16:09Z

This past week has been a whirl wind; quit my job, said goodbye to friends and family and packed up the 5th wheel RV. Additionally, we decided to ship a cargo trailer we had and a 4 door sedan on a flatbed truck, now that was an interesting operation to watch.

This is the 3rd time I have driven cross country. Several things impress me on each journey; the greatness of this country in terms of expanse, variety of terrain and some very interesting people!

We are presently in Western Oklahoma, and will be heading through Texas tomorrow. Will post more as time permits.

Technorati Tags: misc

Time for a change

2008-06-25T15:41:00Z

Time for a change

Last week I accepted a job offer in Southern California. We had been working on a possible west coast relocation from Massachusetts for the past year. With our home selling in less than 5 days on the market, it seemed to be a very good omen. It also appeared to be very good timing, that while we were on the west coast to attend a college graduation ceremony, I spotted a consulting position opportunity on myITForum. Forwarded a copy of my resume, and the rest is history. Thank you myITforum, this is actually the second time that you folks have played a role in finding a new opportunity. I'll be working as a consultant; designing and implementing Microsoft Systems Center Configuration Manager 2007. I'll be roaming mostly in San Diego County, and Orange County.

I am very excited about this new opportunity, will be very busy over the next 6 weeks as we complete our transition. Look for more updates in the future!

Using conditional logic with VBScript (IPStackSize)

2008-03-28T17:57:00Z

One of the things I really like about VBScript is the ability to use conditional logic. As promised in an earlier blog entry, here is a good example.

We found that in order for a particular software product to install (that product name shall remain nameless) and function properly; the IRPStackSize needs to be adjusted to a minimum size. The problem? Many desktops and servers have this key set to a larger value than the minimum required. In that event we do not want to change it to perhaps a smaller value, potentially reintroducing issues. Also, to add to the complexity of the problem at hand this key may, or may not exist.

If you review the logic of this VBScript, we test to see if the value exists, if a null is found, it means the key does not exist, create the key with the proper value. If the key and value exist, check the value to see if the new value needs to replace the existing value.

This gave us the ability to use this script as part of the installation process minimizing risk that it would introduce any new issues.

Here are a couple of kb articles that explain one of the more common errors with IRPStackSize and the resolution.

Event ID 2011

Not enough server storage is available to process this command.

http://support.microsoft.com/kb/106167

Description of the IRPStackSize parameter in Windows 2000, in Windows XP, and in Windows Server 2003

http://support.microsoft.com/kb/285089

Excerpt: The IRPStackSize parameter specifies the number of stack locations in I/O request packets (IRPs) that are used by Windows 2000 Server, by Windows Server 2003, and by Windows XP. You may have to increase this number for certain transports, for media access control (MAC) drivers, or for file system drivers. Each stack uses 36 bytes of memory for each receive buffer.

As always, test before deploying, most of all have fun…

' *********************************************************************

' 2/6/2008

' Adjust size of IRP Stack Size - specifically for xyz product

' install on servers.

' Key location:

' HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters

' > IRPStackSize

' Optimum size of value is 30, or larger... if value is less than 30, modify.

' **********************************************************************

OPTION EXPLICIT

const HKEY_LOCAL_MACHINE = &H80000002

dim strKeyPathRoot

dim strKeyPath

dim strComputer

dim strValueName

dim oReg

dim strValue

dim strNewValue

dim dwValue

Dim objWMIService

Dim colServiceList

DIM objService

strValueName = "IRPStackSize"

‘ change IRPStackSize value here, if required

strNewValue = 30

strComputer = "."

' Read IRP Stacksize... adjust as needed

IRPStackSize

Sub IRPStackSize

On Error Resume Next

Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _

strComputer & "\root\default:StdRegProv")

strKeyPathRoot = "SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters"

' this logic allows us to extend to keys below the path

strKeyPath = strKeyPathRoot

' retrieve the IRPStackSize value

oReg.GetDWordValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strValue

' strValue contains a NULL if not found...

if strValue & "" = "" Then

' key name and value not found, create key set to strNewValue

' wscript.echo "strValue = " & strValue & " - Empty/no key, add one"

oReg.SetDWordValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strNewValue

Else

If strValue < strNewValue Then

' value is set to something less than strNewValue, adjust

' wscript.echo "strValue = " & strValue & " - value < 30, update!"

oReg.SetDWordValue HKEY_LOCAL_MACHINE, strKeyPath, strValueName, strNewValue

Else

' Nothing to do

' wscript.echo "strValue = " & strValue & " - nothing to do"

End If

End if

End Sub

How do I use a wildcard for add remove program information?

2008-03-13T15:19:00Z

Question:

What I really could use is report #96 ("Computers with specific software registered in Add Remove Programs"), that allows me to include a wildcard, so I can find not only "Adobe Acrobat 8.0.0 Professional", but "Adobe Acrobat 7.0.8 Professional", and "Adobe Acrobat 8.1.2 Professional" and all other versions w/in a particular collection.
SMS forces me to pick a specific one. I could use report #99 ("Count of all instances of software registered with Add or Remove Programs"), but I'm only interested in "Adobe Acrobat % Professional".
Anybody have an easy button for this?

~~~

[Steve] Great question, created a report like this to solve a very similar problem. Interestingly, had to use this report today, just before reading your question. I named the report:

“Computers with software (like) registered in Add Remove Programs”

It contains 2 prompts, one for add remove program name, and one for collection id. Here is the base report query (amended 3/18/08 to add the Group by statement):

Select sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0, sys.User_Name0, sys.Operating_System_Name_and0, arp.DisplayName0

FROM v_R_System sys

JOIN v_GS_ADD_REMOVE_PROGRAMS arp ON sys.ResourceID = arp.ResourceID

JOIN v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID

WHERE DisplayName0 LIKE @displayname and fcm.CollectionID=@CollID

GROUP BY sys.Netbios_Name0, fcm.SiteCode, sys.User_Domain0,

sys.User_Name0, sys.Operating_System_Name_and0, arp.DisplayName0

Define 2 prompts

Prompt Name:

displayname

Prompt SQL Statement:

begin

if (@__filterwildcard = '')

Select DISTINCT DisplayName0 FROM v_GS_ADD_REMOVE_PROGRAMS order by DisplayName0

else

Select DISTINCT DisplayName0 FROM v_GS_ADD_REMOVE_PROGRAMS

WHERE DisplayName0 like @__filterwildcard

order by DisplayName0

end

Prompt Name:

Collid

Prompt SQL Statement:

begin

if (@__filterwildcard = '')

select CollectionID, Name from v_Collection order by Name

else

select CollectionID, Name from v_Collection

WHERE CollectionID like @__filterwildcard

order by Name

end

When you’ve put it all together is should look like this:

How do I tune SQL Server memory for optimal performance with SMS/SCCM?

2008-03-04T20:48:00Z

Question:

We are in the process of fine tuning our primary site. We are looking at changing some setting in SQL 2005. Is anyone using AWE to allocate memory with SCCM? Also is there a recommendation to use a maximum server memory? We have 4 gb of memory on our Primary Site Server with the database on the same box. Should we up our maximum server memory in SQL to use more then 2 gb?

~~~~

[Steve] When running Microsoft SQL Server 2005 on a server (let’s say Windows Server 2003) with SCCM/SMS, do not let SQL Server dynamically use all available memory. There are circumstances that may allow SQL to consume all available memory. This activity in turn will slow the operating system, SMS/SCCM, or both.

The goal of fine tuning memory usage is to balance the amount of memory allocated to SQL, with leaving enough memory that the OS and SMS/SCCM can function without using the page file. I recommend using AWE, with Dynamic memory configuration. Set a minimum and maximum amount of server memory. Use SQL Server: DBCC MEMORYSTATUS for memory usage metrics as perfmon is not accurate http://support.microsoft.com/kb/907877/en-us

With 4GB of memory, and as a starting point, consider allocating a maximum of 3GB (dynamic memory) to SQL Server. This allows the remainder of memory to be reserved for the operating system and SMS/SCCM. If you decide not to use AWE, use performance monitor logging to monitor key memory counters, page file usage, etc over a few days. Make adjustments as needed to SQL Server dynamic memory usage. Here is one example for illustration (3gb server memory)

From SQL Server 2005 Books Online

Configuring Memory Options

SQL Server 2005 dynamically allocates AWE-mapped memory when running with any of the Windows Server 2003 operating system editions. In other words, the buffer pool can dynamically manage AWE-mapped memory (within the constraints of the min server memory and max server memory options) to balance SQL Server memory use with the overall system requirements. When AWE is enabled, SQL Server 2005 always attempts to use AWE-mapped memory. This applies to all memory configurations, including computers configured to provide applications with less than 3 GB of user mode address space.

We recommend setting AWE as the default memory mode for SQL Server 2005 running under Windows Server 2003. The Hot-Add Memory feature requires AWE to be enabled during SQL Server startup. For information, see Hot Add Memory.

Note:

AWE is not needed and cannot be configured on 64-bit operating systems.

Since AWE-mapped memory is supported below 3 GB, you can define the min server memory and max server memory values within the physical memory range, or use the default values for both options.
You may consider setting max server memory for SQL Server to guarantee additional memory for other applications operating on the computer. Although SQL Server can dynamically release AWE-mapped memory, the current amount of allocated AWE-mapped memory cannot be swapped out to the page file.

How do I return all records when there are no matching rows in a joined table?

2008-02-28T18:30:00Z

Question from the myITforum list:

Need some help from all you experts out there, I have created an SMS report for all machines in a particular collection, but some of the machines are logged onto by local accounts, these are not being picked up on the report as there is no entry in the v_R_User, I have looked at the SQL that creates this view, it only detects accounts that are domain accounts. How can I get the ones that use local accounts to show up, do I need a left, inner, outer, right join somewhere?

SELECT     SYS.Netbios_Name0 AS [Computer Name],
    SYS.User_Name0 AS [KID],
    RUS.Full_User_Name0 AS [Full Name],
    SYS.AD_Site_Name0 AS [AD Site],
    SYS.User_Domain0 AS [Domain],
    CS.Manufacturer0 AS [Manufacturer],
    CS.Model0 AS [Model],
    FCM.SiteCode AS [SMS Site Code]
FROM     v_R_System SYS
JOIN     v_FullCollectionMembership FCM ON SYS.ResourceID=FCM.ResourceID
JOIN     v_R_User RUS ON RUS.User_Name0 = SYS.User_Name0
JOIN    v_GS_Computer_System CS on SYS.ResourceID = CS.ResourceID
WHERE     FCM.CollectionID = 'P000128C'
ORDER BY SYS.Netbios_Name0, SYS.User_Name0

~~~

[Steve] The reason that there are no results shown when matching records are missing from the joined table is due to the specified JOIN statement. A JOIN statement by default, is an equi-join. An equi-join will only return records when a match is found on the specified condition. To return all records regardless of a matching condition from the LEFT side of the JOIN, change the JOIN type to LEFT OUTER JOIN (below).

SELECT     SYS.Netbios_Name0 AS [Computer Name],
    SYS.User_Name0 AS [KID],
    RUS.Full_User_Name0 AS [Full Name],
    SYS.AD_Site_Name0 AS [AD Site],
    SYS.User_Domain0 AS [Domain],
    CS.Manufacturer0 AS [Manufacturer],
    CS.Model0 AS [Model],
    FCM.SiteCode AS [SMS Site Code]
FROM     v_R_System SYS
JOIN     v_FullCollectionMembership FCM ON SYS.ResourceID=FCM.ResourceID
LEFT OUTER JOIN     v_R_User RUS ON RUS.User_Name0 = SYS.User_Name0
JOIN    v_GS_Computer_System CS on SYS.ResourceID = CS.ResourceID
WHERE     FCM.CollectionID = 'P000128C'
ORDER BY SYS.Netbios_Name0, SYS.User_Name0

How do I list computers that do not have a particular software?

2008-02-15T21:01:12Z

This question may be one of the more challenging things to solve in SQL. And, the way to achieve the results may seem unorthodox.

OK, we want to get a list of all computers that do not have Winword.exe installed. Since, we are looking to create a query that executes fast, and can readily be used to join to other tables or views, we’ll just return the machineid (or resourceid in other tables).

Let’s start with the positive, how do we get a list of all computers that have winword.exe installed.

SELECT DISTINCT SMS_G_System_SYSTEM.MachineID

FROM System_DISC AS SMS_R_System

INNER JOIN System_DATA AS SMS_G_System_SYSTEM

ON SMS_G_System_SYSTEM.MachineID = SMS_R_System.ItemKey

INNER JOIN vSMS_G_System_SoftwareFile AS SMS_G_System_SoftwareFile

ON SMS_G_System_SoftwareFile.ClientId = SMS_R_System.ItemKey

WHERE SMS_G_System_SoftwareFile.FileName = 'WINWORD.EXE'

Will give us that list.

Now, to give us a list of all computers that do not have Winword.exe installed, we use a subselect query. A subselect is a query within a query, the results from the inner query are passed to the outer query.

Now, to answer the question posed earlier, how do we get a list of all computers that do not have winword.exe installed, the following query will give us that information. Check out the NOT IN clause (below), we are selecting the positive in the subquery, then excluding them from the main, or top-level query. This logic could also be used to return a list of computers where one type of software is installed, yet missing another.

SELECT DISTINCT SMS_G_System_SYSTEM.Name0

FROM System_DISC AS SMS_R_System

INNER JOIN System_DATA AS SMS_G_System_SYSTEM

ON SMS_G_System_SYSTEM.MachineID = SMS_R_System.ItemKey

where SMS_G_System_SYSTEM.MachineID

NOT IN

(SELECT DISTINCT SMS_G_System_SYSTEM.MachineID

FROM System_DISC AS SMS_R_System

INNER JOIN System_DATA AS SMS_G_System_SYSTEM

ON SMS_G_System_SYSTEM.MachineID = SMS_R_System.ItemKey

INNER JOIN vSMS_G_System_SoftwareFile AS SMS_G_System_SoftwareFile

ON SMS_G_System_SoftwareFile.ClientId = SMS_R_System.ItemKey

WHERE SMS_G_System_SoftwareFile.FileName = 'WINWORD.EXE')

Reassign an SMS or Configuration Manager Client to another Site

2008-02-11T12:48:00Z

Some time ago, I wrote an article about reassigning an SMS client to another SMS site. Since this topic seems to crop up periodically on both the myITforum list and public newsgroups, it is worth mentioning here.

At the time this script was developed, we were closing a large office in one location and moving the clients to another physical location. This meant transitioning approximately 2,500 clients between buildings. We could have uninstalled the client first, however it seemed to make more sense to script a solution that would detect what was the current site code for the client, and what was the client's assigned site code. If these 2 values were different, reset the client to the discovered site.

This functionality has also proved useful for situations where SMS/ConfigMgr clients become orphaned. This script can be launched as a machine GPO, or sent as an advertisement.

* Note: probably the only "flaw" in this logic, if you have an active advertisement running, a roaming laptop could inadvertently be discovered/assigned to an incorrect site. This script could be tuned to detect and ignore laptop class computers.

' DiscoverSetAssignedSite.vbs

Option Explicit

dim oSMSClient
dim strDiscoverSite
dim strAssignedSite

On Error Resume Next

Set oSMSClient = CreateObject ("Microsoft.SMS.Client")

If err.Number <> 0 then
' wscript.echo "Could not create SMS Client Object - quitting"
Else
strDiscoverSite = CallAutoDiscoverSite ()
strAssignedSite = CallGetAssignedSite ()
If strAssignedSite <> strDiscoverSite Then
Call SetAssignedSite (strDiscoverSite)
End If
End If

Set oSMSClient=nothing

Function CallAutoDiscoverSite ()
dim strDiscoveredSite

strDiscoveredSite = oSMSClient.AutoDiscoverSite

If Len(strDiscoveredSite & "")>0 Then
' we have something
Else
strDiscoveredSite = "DiscoveredSiteNotFound"
End If
' wscript.echo "Discovered Site is : '" & strDiscoveredSite & "'"

CallAutoDiscoverSite = strDiscoveredSite

End Function

Function CallGetAssignedSite ()
dim strAssignedSite

strAssignedSite = oSMSClient.GetAssignedSite

If Len(strAssignedSite & "")>0 Then
' we have something
Else
strAssignedSite = "AssignedSiteNotFound"
End If
' wscript.echo "Assigned Site is : '" & strAssignedSite & "'"

CallGetAssignedSite = strAssignedSite

End Function

Sub SetAssignedSite (SiteCode)

oSMSClient.SetAssignedSite SiteCode,0

End Sub

Other methods to set Registry key values

2008-02-08T17:20:00Z

One of the interesting things about automation, and scripting in general, there are always multiple ways to solve a problem. In yesterday’s blog, the original question “How do set DWord values in a VBScript?” was solved this way.

There were 3 other methods (actually 2 new, one enhancement) posted yesterday that are interesting, and worth breaking down.

Method 1

Using regedit and merging the registry keys. Now; this method has been around for a long time (the article references Windows 3.1 :), and have used it myself with great results. Jeff Gilbert was kind enough to post the method and a link to the article.

Just save the regedit (modify a computer registry and export the key) and add the command line to the program/mst by using

regedit /s:REGEDIT /S /U <your .reg goes here>

http://support.microsoft.com/kb/82814/en-us

Additionally, you could set this as a package/program option in SMS/SCCM making this perhaps the most simple.

Method 2

Using the WScript RegWrite method.

Here’s a partial breakdown (see the link for full disclosure):

Creates a new key, adds another value-name to an existing key (and assigns it a value), or changes the value of an existing value-name.

object.RegWrite(strName, anyValue [,strType])

It is somewhat shortened by a few lines of code (thanks to Sherry Kissinger for the code sample):

Set oWS = Wscript.CreateObject("Wscript.Shell")
‘ Single line each, watch line wrap

oWS.RegWrite "HKLM\SOFTWARE\JavaSoft\Java Update\Policy\EnableJavaUpdate", "00000000", "REG_DWORD"

oWS.RegWrite "HKLM\SOFTWARE\JavaSoft\Java Update\Policy\NotifyDownload", "00000000", "REG_DWORD"

Method 3

Using the same method that I posted yesterday, Maarten van Willigen used an array to enumerate the key names and set the values (note: this works because each of the keys being set are DWord). Simplification is good, also very creative solution.

const HKEY_LOCAL_MACHINE = &H80000002

strComputer = "."

Set objRegistry=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" &_

strComputer & "\root\default:StdRegProv")

strKeyPath = "SOFTWARE\JavaSoft\Java Update\Policy"

For Each strValueName In Array("EnableJavaUpdate","NotifyDownload","NotifyInstall")

dwValue = "0"

objRegistry.SetDWORDValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,dwValue

Summary

As many of you know, I'm partial to scripting based solutions. However, sometimes whatever gets the job done is OK as well.

In a future blog, I'll post an example of why scripting and scripting solutions in general is a vitally important item to learn.

I know there are other ways and languages to solve this, comments, suggestions? Let me know what you think.