Tagged: SQL

Update ConfigMgr packages with Hotfix-information

When you install a hotfix and/or a cumulative update in ConfigMgr you can select the option to let the installer create some packages.

But, those packages are missing some info… For instance Manufacturer and Version.

A quick and dirty SQL update will do the trick.

	Version = LEFT(Replace(Source, '\\server.domain.com\SMS_ABC\hotfix\', ''), 9),
	Manufacturer = 'Microsoft',
	Language = 'All'
WHERE Source LIKE '\\server.domain.com\SMS_ABC\Hotfix%'

This is of course not supported… but afaik it works. 😀

If you want to see what would be updated, run this first

	Version AS OrgVersion,
	LEFT(Replace(Source, '\\server.domain.com\SMS_ABC\hotfix\', ''), 9) AS NewVersion,
	Manufacturer AS OrgManufacturer,
	'Microsoft' AS NewManufacturer,
	Language AS OrgLanguage,
	'All' AS NewLanguage
FROM SMSPackages_G
WHERE Source LIKE '\\server.domain.com\SMS_ABC\hotfix\%'

(And yes, you need to replace servername and sitecode)

SQL in Powershell without cmdlets

I am currently playing around with ConfigMgr 2012 cmdlets… and they only run in X86 (*doh*).

Anyway, I need to query a database with information about some collections and other stuff I need to create… and running SQL Server cmdlets in X86 doesn’t work.

So to get this to work I found a Powershell function that uses SqlClient from .NET instead… I rewrote the function a bit and here is the result:

Function Run-SqlQuery {
		[string] $Server = ".",
		[string] $Database = "master",
		[string] $Query,
		[Int32]  $QueryTimeout=60
	$sqlConnection = New-Object System.Data.SqlClient.SQLConnection
	$sqlConnection.ConnectionString = "Data Source=$($Server);Initial Catalog=$($Database);Integrated Security=True;"
	$sqlCommand = New-Object System.Data.SqlClient.SqlCommand($Query,$sqlConnection)
	$sqlCommand.CommandTimeout = $QueryTimeout
	$sqlDataSet = New-Object System.Data.DataSet
	$sqlDataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($sqlCommand)

	return $sqlDataSet.Tables[0]

Then you can use it like this:

Run-SqlQuery -Query "EXEC SP_HelpDB"
Run-SqlQuery -Query "SELECT Col1, Col2 FROM MyTable" -Database "MyDatabase" -Server "SomeServer" | Format-Table -AutoSize

This can probably be useful if you need to run a SQL command from a box where your’e not sure if you have SQL cmlets or not.

Update ConfigMgr Site Description

I did an install of a secondary site and accidently put in the wrong description…

Anyway, easy to fix with some SQL commands.

If you use this you can see the current sitenames and descriptions (change CM_XYZ to your database name):

SELECT SiteCode, SiteName, SiteServerName
FROM CM_XYZ..SC_SiteDefinition

And here is the way to update it (Change to you database name and the site code of the site you want to change)

UPDATE SC_SiteDefinition
SET SiteName = 'My Site Description'

FYI: When running this you will se “Updated X rows… Updated Y rows…” and so on.

OpsMgrDW Grooming

After a while my demo environment with OpsMgr data warehouse (and loads of other stuff) needed some more space.

I haven’t looked in to grooming of the DW before. So as usual Google is a nice friend. 🙂

Stefan Stranger has a nice post with loads of grooming information.

So what I did was to download the dwdatarp tool.

First I ran it to see the current status

dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW

Looks like most of the datasets are stored 400 or 180 days… That is somewhat to much data for a demo environment.
I took the values and divided them by 4. Then ran the following to free up 75% of the database.

dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Alert -a Raw -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds "Client Monitoring" -a Raw -m 8
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds "Client Monitoring" -a Daily -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Configuration -a Raw -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Event -a Raw -m 25
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Performance -a Raw -m 45
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Performance -a Hourly -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Performance -a Daily -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds State -a Raw -m 45
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds State -a Hourly -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds State -a Daily -m 100

OK… when you have a database full of free space you need to truncate it, but that’s another story…

SCCM Status Message Query for Advertismet

Made a small combination of a couple of queries, so this will show “Clients That Ran, Received, Rejected or Started a Specific Advertised Program

SELECT stat.*, ins.*, att1.*, att1.AttributeTime
FROM SMS_StatusMessage AS stat 
LEFT JOIN SMS_StatMsgInsStrings AS ins ON stat.RecordID = ins.RecordID
LEFT JOIN SMS_StatMsgAttributes AS att1 ON stat.RecordID = att1.RecordID 
INNER JOIN SMS_StatMsgAttributes AS att2 ON stat.RecordID = att2.RecordID

	stat.ModuleName = "SMS Client" AND
		(stat.MessageID = 10005 AND att2.AttributeID = 401)  OR 
		(stat.MessageID >= 10018 AND stat.MessageID <=10019 and att2.AttributeID = 401) OR 
		(stat.MessageID = 10002 AND att2.AttributeID = 401) OR 
		(stat.MessageID >= 10008 AND stat.MessageID <= 10009)
	AND att2.AttributeValue = ##PRM:SMS_StatMsgAttributes.AttributeValue## 
	AND att2.AttributeTime >= ##PRM:SMS_StatMsgAttributes.AttributeTime## 
ORDER BY att1.AttributeTime DESC

Change Collection Refresh Rate

Had some problems with loads of collection refresh taking all of the CPU on the SCCM-server.

So first, to get the SCCM-server to calm down I wrote a small (and somewhat ugly, since it uses SQL) hack:

UPDATE Collections
Set Flags = 17
Where CollectionName LIKE '%Something In The Collection Name%'
AND Flags=18

This script uncheck the box “Update his collection on a schedule” for the collections.

Then, when the SCCM server did go back to a normal CPU-utilization we used this script to set another refresh-rate on the collections.

Const cSccmProvider = "."
Const cWmiUsername = ""
Const cWmiPassword = ""

Const cCollectionNamePattern = "%Something In The Collection Name%"
Const cDoUpdate = True		' Set to false to test
Const cRefreshDays = 0		' 0 - 31
Const cRefreshHours = 12	' 0 - 23
Const cRefreshMinutes = 0	' 0-59

Set oLocator = CreateObject("WbemScripting.SWbemLocator")

' --- Get SCCM Site Code
WScript.Echo "Connecting to: " & cSccmProvider
Set oSccmWmi = oLocator.ConnectServer(cSccmProvider, "root\sms", cWmiUsername, cWmiPassword)
Set oWmiQuery = oSccmWmi.ExecQuery("SELECT SiteCode FROM SMS_ProviderLocation WHERE ProviderForLocalSite=true")
For each currentSite in oWmiQuery
	sSccmSiteCode = currentSite.SiteCode
	Exit For

' --- Connect to site
WScript.Echo "Connecting to: " & cSccmProvider &  " - root\sms\site_" & sSccmSiteCode
Set oSccmWmi = oLocator.ConnectServer(cSccmProvider, "root\sms\site_" & sSccmSiteCode, cWmiUsername, cWmiPassword)

' --- Create interval
WScript.Echo "Creating Interval: " & cRefreshDays & " days, " & cRefreshHours & " hours, " & cRefreshMinutes & " minutes."
Set oInterval = oSccmWmi.Get("SMS_ST_RecurInterval")  
oInterval.DaySpan = cRefreshDays
oInterval.HourSpan = cRefreshHours
oInterval.MinuteSpan = cRefreshMinutes
oInterval.isGmt = False
oInterval.StartTime = "20090101000000.000000+***"

' --- List all collection
set oCollections = oSccmWmi.ExecQuery("SELECT * FROM SMS_Collection WHERE Name LIKE '" & cCollectionNamePattern & "'")
For Each oCollection In oCollections
	' --- Update interval on Collection
	If cDoUpdate Then
		WScript.Echo "Updating: " & oCollection.CollectionID & " - " & oCollection.Name
		Set oCollectionToChange = oSccmWmi.Get("SMS_Collection.CollectionID='"  & oCollection.CollectionID & "'")
		oCollectionToChange .RefreshSchedule = Array(oInterval)
		oCollectionToChange .RefreshType = 2  '1 = Manual, 2 = Periodic refresh
		oCollectionToChange .Put_
		WScript.Echo "Testing: " & oCollection.CollectionID & " - " & oCollection.Name
	End if

The script is attached here: changeCollectionRefresh.vbs