widsnet.com
24Aug/110

vbs named arguments and extending an array

Update to previous posts (http://widsnet.com/2011/07/set-binary-delta-replication-on-packages) (http://widsnet.com/2011/06/update-distribution-points-on-a-schedule-vbscript) to be able to take input from the commandline and collection IDs from a file.

Const ForReading = 1
Const STORAGE_DIRECT = 2
Const AP_USE_BINARY_DELTA_REP = &H04000000

Set colArgsNamed = WScript.Arguments.Named
If colArgsNamed.Exists("File") And colArgsNamed.Exists("SMSServer") And colArgsNamed.Exists("SMSSite") Then
	arrPackageID = getArrayFromFile(colArgsNamed.Item("File"))
	strSMSServer = colArgsNamed.Item("SMSServer")
	strSMSSiteCode = colArgsNamed.Item("SMSSite")
Else
	WScript.Echo "Usage: cscript " & WScript.ScriptName & " /File:<FileName> /SMSServer:<Server>"
	WScript.Echo vbTab & "/SMSSite:<SiteCode>"
	WScript.Echo "Optional arguments: /UpdateDay:<YYYY-MM-DD> /UpdateTime:<HH:MM> /DeltaRep"
	WScript.Echo ""
	WScript.Echo vbTab & "<Server> is the SMS Server to connect to"
	WScript.Echo vbTab & "<SiteCode> is the SMS Site to connect to"
	WScript.Echo vbTab & "<FileName> is a text file with one CollectionID per row"
	WScript.Echo vbTab & "<YYYY-MM-DD> is the date to redistribute the package"
	WScript.Echo vbTab & "<HH:MM> is the time to redistribute the package"
	WScript.Echo vbTab & "/DeltaRep to enable Binary Delta Replication"
	WScript.QuitEnd If
Set objLocator = CreateObject("WbemScripting.SWbemLocator")
Set objSMSConnection = objLocator.ConnectServer(strSMSServer, "root\sms\site_" &amp; strSMSSiteCode)

If colArgsNamed.Exists("UpdateDay") And colArgsNamed.Exists("UpdateTime") Then
	strUpdateDay = colArgsNamed.Item("UpdateDay")
	strUpdateTime = colArgsNamed.Item("UpdateTime")
	strWMIDate = replace(strUpdateDay, "-", "") &amp; Replace(strUpdateTime, ":", "") &amp; "00.000000+***"
	WScript.Echo "Date and Time: " &amp; strUpdateDay &amp; " " &amp; strUpdateTime
	bUpdateDPsched = True
Else
	bUpdateDPsched = False
End If

'
For Each strPackageID In arrPackageID
	If bUpdateDPSched Then
		setNonRecurringUpdateSchedule objSMSConnection, strPackageID, strWMIDate
	End If
	If colArgsNamed.Exists("DeltaRep") Then
		setBinaryDeltaReplication objSMSConnection, strPackageID
	End If
Next

'
Function getArrayFromFile(strFile)
	Dim arr()
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	If objFSO.FileExists(strFile) Then
		Set objFile = objFSO.OpenTextFile(strFile, ForReading, True)
		i = 0
		Do While Not objFile.AtEndOfStream
			strText = Trim(objFile.ReadLine)
			strText = Replace(strText, Chr(160), "")
			If strText &lt;&gt; "" Then
				ReDim Preserve arr(i+1)
				arr(i) = strText
				i = i+1
			End If
		Loop
		objFile.Close
	End If
	getArrayFromFile = arr
End Function
4Jul/110

Set Binary Delta Replication on Packages

A follow-up post to http://widsnet.com/2011/06/update-distribution-points-on-a-schedule-vbscript/
Some packages didn't have the "Enable binary differential replication" checkbox enabled. That lead to a lot of unnecessary network traffic. So to avoid that this "add-on" script enables binary delta replication.

Const AP_USE_BINARY_DELTA_REP = &H04000000

setBinaryDeltaReplication objSMSConnection, strPackageID

Sub setBinaryDeltaReplication(objSMSConnection, strPackageID)
	Set colPkg = objSMSConnection.ExecQuery("Select * from SMS_Package where PackageID='" & strPackageID & "'")
	If colPkg.Count = 1 Then
		Set objPackage = objSMSConnection.Get("SMS_Package.PackageID='" & strPackageID & "'")
		If Not objPackage.PkgFlags And AP_USE_BINARY_DELTA_REP Then
			objPackage.PkgFlags = objPackage.PkgFlags OR AP_USE_BINARY_DELTA_REP
			objPackage.Put_
		End If
		If objPackage.PkgFlags And AP_USE_BINARY_DELTA_REP Then
			WScript.Echo strPackageID & " Delta Replication: Enabled"
		Else
			WScript.Echo strPackageID & " Delta Replication: Disabled!"
		End If
	End If
End Sub
4Jul/110

All hidden advertisements

A report to show all advertisements that are hidden from the users.

SELECT AdvertisementID, AdvertisementName,
CASE
  WHEN AdvertFlags & 0x02000000 = 0x02000000 Then 'Yes'
  ELSE 'No'
END AS 'No Display'
FROM v_Advertisement
WHERE AdvertFlags & 0x02000000 = 0x02000000

AdvertFlags

  • 0x00000020 (5)
    IMMEDIATE. Announce the advertisement to the user immediately.
  • 0x00000100 (8)
    ONSYSTEMSTARTUP. Announce the advertisement to the user on system startup.
  • 0x00000200 (9)
    ONUSERLOGON. Announce the advertisement to the user on logon.
  • 0x00000400 (10)
    ONUSERLOGOFF. Announce the advertisement to the user on logoff.
  • 0x00008000 (15)
    WINDOWS_CE. The advertisement is for a device client.
  • 0x00020000 (17)
    DONOT_FALLBACK. Do not fall back to unprotected distribution points.
  • 0x00040000 (18)
    ENABLE_TS_FROM_CD_AND_PXE. The task sequence is available to removable media and the pre-boot execution environment (PXE) service point.
  • 0x00100000 (20)
    OVERRIDE_SERVICE_WINDOWS. Override maintenance windows in announcing the advertisement to the user.
  • 0x00200000 (21)
    REBOOT_OUTSIDE_OF_SERVICE_WINDOWS. Reboot outside of maintenance windows.
  • 0x00400000 (22)
    WAKE_ON_LAN_ENABLED. Announce the advertisement to the user with Wake On LAN enabled.
  • 0x00800000 (23)
    SHOW_PROGRESS. Announce the advertisement to the user showing task sequence progress.
  • 0x02000000 (25)
    NO_DISPLAY. The user should not run programs independently of the assignment.
  • 0x04000000 (26)
    ONSLOWNET. Assignments are mandatory over a slow network connection.

http://msdn.microsoft.com/en-us/library/cc146108.aspx

4Jul/110

Packages missing Binary Delta Replication

A report to show which packages is missing Binary Delta Replication checkbox.

SELECT PackageID, Manufacturer, Name, Version,
CASE
WHEN PkgFlags & 0x04000000 = 0x04000000 THEN 'Yes'
ELSE 'No'
END AS 'Use Binary Delta Rep'
FROM v_Package
WHERE PkgFlags & 0x04000000 != 0x04000000
AND PackageType = 0

PackageType

  • 0 Regular software distribution package.
  • 3 Driver package.
  • 4 Task sequence package.
  • 5 Software update package.
  • 6 Device setting package.
  • 257 Image package.
  • 258 Boot image package.
  • 259 Operating system install package.

PkgFlags

  • 0x01000000 (24) DO_NOT_DOWNLOAD. Do not download the package to branch distribution points, as it will be pre-staged.
  • 0x02000000 (25) PERSIST_IN_CACHE. Persist the package in the cache.
  • 0x04000000 (26) USE_BINARY_DELTA_REP. Marks the package to be replicated by distribution manager using binary delta replication.
  • 0x10000000 (28) NO_PACKAGE. The package does not require distribution points.
  • 0x20000000 (29) USE_SPECIAL_MIF. This value determines if Configuration Manager uses MIFName, MIFPublisher, and MIFVersion for MIF file status matching. Otherwise, Configuration Manager uses Name, Manufacturer, and Version for status matching. For more information, see the Remarks section later in this topic.
  • 0x40000000 (30) DISTRIBUTE_ON_DEMAND. The package is allowed to be distributed on demand to branch distribution points.

http://msdn.microsoft.com/en-us/library/cc146062.aspx

1Jul/110

Collection finder

A report to find the parents to a collection to be able to find the collection if you only have the name or ID.

SELECT
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT5ID.parentCollectionID) AS 'Parent 5',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT4ID.parentCollectionID) AS 'Parent 4',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT3ID.parentCollectionID) AS 'Parent 3',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT2ID.parentCollectionID) AS 'Parent 2',
(SELECT Name FROM v_Collection WHERE CollectionID = COLLPARENT1ID.parentCollectionID) AS 'Parent 1',
COLL.Name AS 'Collection Name',
COLL.CollectionID AS 'CollectionID'

FROM v_Collection COLL
LEFT JOIN v_CollectToSubCollect COLLISPARENT ON COLL.CollectionID = COLLISPARENT.parentCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT1ID ON COLL.CollectionID = COLLPARENT1ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT2ID ON COLLPARENT1ID.parentCollectionID = COLLPARENT2ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT3ID ON COLLPARENT2ID.parentCollectionID = COLLPARENT3ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT4ID ON COLLPARENT3ID.parentCollectionID = COLLPARENT4ID.subCollectionID
LEFT JOIN v_CollectToSubCollect COLLPARENT5ID ON COLLPARENT4ID.parentCollectionID = COLLPARENT5ID.subCollectionID

WHERE CollectionID=@CollName

Prompt:

begin
   if (@__filterwildcard = '')
      SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
   else
      SELECT DISTINCT CollectionID, Name FROM v_Collection
      WHERE Name like @__filterwildcard
      ORDER BY Name
end
1Jul/110

Collection history

A report to show who created, modified or deleted a collection, and when.

SELECT SM.Time, SMIS.InsStrValue AS 'Collection',
CASE SM.MessageID
   WHEN 30015 THEN 'Created'
   WHEN 30016 THEN 'Modified'
   WHEN 30017 THEN 'Deleted'
END AS 'Message', SMA.AttributeValue AS 'By User', SM.MachineName AS 'From Computer'
FROM v_StatMsgInsStrings SMIS
LEFT JOIN v_StatMsgAttributes SMA on SMA.RecordID=SMIS.RecordID
LEFT JOIN v_StatusMessage SM on SM.RecordID=SMIS.RecordID
WHERE SMIS.RecordID in (SELECT RecordID FROM v_StatMsgAttributes WHERE AttributeValue=@CollectionID)
      AND SMA.AttributeID=403 AND SMIS.InsStrIndex > 1 AND SM.MessageID in (30015, 30016, 30017)
ORDER BY SM.Time desc

Prompt:

begin
   if (@__filterwildcard = '')
      SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
   else
      SELECT DISTINCT CollectionID, Name FROM v_Collection
      WHERE Name like @__filterwildcard
      ORDER BY Name
end
1Jul/110

Packages missing on DP and referenced by TS

A report to show all packages that are targeted to a distribution point bur aren't there yet, for some reason, and are referenced by a specific Task Sequence.

SELECT ps.PackageID, ps.SiteCode,
SUBSTRING(ps.ServerNALPath, CHARINDEX('\\', ps.ServerNALPath), CHARINDEX('"]',    ps.ServerNALPath) - CHARINDEX('\\',
     ps.ServerNALPath)) AS ServerNALPath,
ps.SourceVersion, ps.State, ps.LastCopied, ps.SummaryDate,
ps.InstallStatus, p.Manufacturer, p.Name
FROM
     (
         SELECT DISTINCT v_TaskSequenceReferencesInfo.ReferencePackageID AS PackageID
         FROM v_TaskSequenceReferencesInfo
         WHERE v_TaskSequenceReferencesInfo.PackageID=@TaskSequenceID
     ) RefPkgs
JOIN v_PackageStatusDistPointsSumm ps ON ps.PackageID=RefPkgs.PackageID
JOIN v_Package p ON p.PackageID=ps.PackageID
WHERE ps.InstallStatus!='Package Installation complete'
ORDER BY ps.PackageID

Prompts:

BEGIN
 IF (@__filterwildcard = '')
  SELECT PackageID AS TaskSequenceID, Name, Description FROM v_TaskSequencePackage
  ORDER BY Name
 ELSE
  SELECT PackageID AS TaskSequenceID, Name, Description FROM v_TaskSequencePackage
  WHERE PackageID LIKE @__filterwildcard
  ORDER BY Name
END
30Jun/110

Update distribution points on a schedule vbscript

A script to set a "Update distribution point on a schedule" on several PackageIDs to a single non recurring date and time. Doing like this to avoid distributing production packages under working hours. And scripting it since I need to update around 150 packages.

Const STORAGE_DIRECT = 2

strSMSServer = "server"
strSMSSiteCode = "CEN"
arrPackageID = Array("CEN00001", "CEN00002", "CEN00003")
strUpdateDay = "2011-07-01"
strUpdateTime = "01:00"
strWMIDate = replace(strUpdateDay, "-", "") & Replace(strUpdateTime, ":", "") & "00.000000+***"

Set objLocator = CreateObject("WbemScripting.SWbemLocator")
Set objSMSConnection = objLocator.ConnectServer(strSMSServer, "root\sms\site_" & strSMSSiteCode)
WScript.Echo "Date and Time: " & strUpdateDay & " " & strUpdateTime
For Each strPackageID In arrPackageID
	setNonRecurringUpdateSchedule objSMSConnection, strPackageID, strWMIDate
Next

Sub setNonRecurringUpdateSchedule(objSMSConnection, strPackageID, strWMIDate)
	Set Token = objSMSConnection.Get("SMS_ST_NonRecurring")
	Token.DayDuration = 0
	Token.HourDuration = 0
	Token.MinuteDuration = 0
	Token.IsGMT = 0
	Token.StartTime = strWMIDate

	setUpdateSchedule objSMSConnection, strPackageID, Token
End Sub

Sub setUpdateSchedule(objSMSConnection, strPackageID, Token)
	Set colPkg = objSMSConnection.ExecQuery("Select * from SMS_Package where PackageID='" & strPackageID & "'")
	If colPkg.Count = 1 Then
		Set objPackage = objSMSConnection.Get("SMS_Package.PackageID='" & strPackageID & "'")
		If objPackage.PkgSourceFlag = STORAGE_DIRECT Then
			objPackage.RefreshSchedule = Array(Token)
			objPackage.Put_
			WScript.Echo strPackageID & " Software Package updated"
		Else
			WScript.Echo strPackageID & " Software Package not using STORAGE_DIRECT!"
		End If
	Else
		Set colPkg = objSMSConnection.ExecQuery("Select * from SMS_DriverPackage where PackageID='" & strPackageID & "'")
		If colPkg.Count = 1 Then
			Set objPackage = objSMSConnection.Get("SMS_DriverPackage.PackageID='" & strPackageID & "'")
			If objPackage.PkgSourceFlag = STORAGE_DIRECT Then
				objPackage.RefreshSchedule = Array(Token)
				objPackage.Put_
				WScript.Echo strPackageID & " Driver Package updated"
			Else
				WScript.Echo strPackageID & " Software Package not using STORAGE_DIRECT!"
			End If
		Else
			WScript.Echo strPackageID & " Package not found!"
		End If
	End If
End Sub