November 2006 Patching report
Note: No Extended or office scanner updates this month
SELECT Bulletin, QNumber, Product, (100 * Verified / (Retrying + PreSuccess + Uninstalled + PendReboot + Verified + NoStatus + Failed)) AS '% Compliant',
Retrying + PreSuccess + Uninstalled + PendReboot + Verified + NoStatus + Failed AS Total, Verified, NoStatus, Retrying, PreSuccess, Uninstalled,
PendReboot, Failed, Issue
FROM (SELECT QNumbers AS QNumber, ID AS Bulletin, Product, Title AS Issue, LocaleID AS Locale,
SUM(CASE WHEN patch.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS NoStatus,
SUM(CASE WHEN patch.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) AS Verified,
SUM(CASE WHEN patch.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS Retrying,
SUM(CASE WHEN patch.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS PreSuccess,
SUM(CASE WHEN patch.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS Uninstalled,
SUM(CASE WHEN patch.LastStateName = 'Reboot pending' THEN 1 ELSE 0 END) AS PendReboot,
SUM(CASE WHEN patch.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS Failed
FROM v_GS_PatchStatus AS patch INNER JOIN
dbo.v_R_System ON patch.ResourceID = dbo.v_R_System.ResourceID
WHERE (patch.id in ('MS06-066', 'MS06-067', 'MS06-068', 'MS06-069', 'MS06-070', 'MS06-071', 'MS03-017', 'MS06-041'))
AND (dbo.v_R_System.Operating_System_Name_and0 LIKE '%workstation%')
GROUP BY QNumbers, ID, Product, Title, LocaleID) AS ps
ORDER BY Bulletin DESC, product
2006 Year in review report
SELECT Bulletin, QNumber, Product, (100 * Verified / (Retrying + PreSuccess + Uninstalled + PendReboot + Verified + NoStatus + Failed)) AS '% Compliant',
Retrying + PreSuccess + Uninstalled + PendReboot + Verified + NoStatus + Failed AS Total, Verified, NoStatus, Retrying, PreSuccess, Uninstalled,
PendReboot, Failed, Issue
FROM (SELECT QNumbers AS QNumber, ID AS Bulletin, Product, Title AS Issue, LocaleID AS Locale,
SUM(CASE WHEN patch.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS NoStatus,
SUM(CASE WHEN patch.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) AS Verified,
SUM(CASE WHEN patch.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS Retrying,
SUM(CASE WHEN patch.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS PreSuccess,
SUM(CASE WHEN patch.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS Uninstalled,
SUM(CASE WHEN patch.LastStateName = 'Reboot pending' THEN 1 ELSE 0 END) AS PendReboot,
SUM(CASE WHEN patch.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS Failed
FROM v_GS_PatchStatus AS patch INNER JOIN
dbo.v_R_System ON patch.ResourceID = dbo.v_R_System.ResourceID
WHERE ((patch.id LIKE 'MS06-%') AND (dbo.v_R_System.Operating_System_Name_and0 LIKE '%workstation%'))
GROUP BY QNumbers, ID, Product, Title, LocaleID) AS ps
ORDER BY Bulletin DESC, product
------------------------------------------------------------------------------
SELECT [ ], [ ], CASE Bulletin WHEN 'None' THEN Product ELSE Bulletin END AS Bulletin,
(100 * Verified / (Retrying + PreSuccess + Uninstalled + PendReboot + Verified + NoStatus + Failed)) AS '% Compliant',
Retrying + PreSuccess + Uninstalled + PendReboot + Verified + NoStatus + Failed AS Total, Verified, NoStatus, Retrying, PreSuccess, Uninstalled,
PendReboot, Failed, Issue
FROM (SELECT CASE LocaleID WHEN '1033' THEN '' ELSE '' END AS [ ], QNumbers AS QNumber, ID AS Bulletin, Product, Title AS Issue,
LocaleID AS Locale, SUM(CASE WHEN patch.LastStateName = 'No Status' THEN 1 ELSE 0 END) AS NoStatus,
SUM(CASE WHEN patch.LastStateName = 'Install Verified' THEN 1 ELSE 0 END) AS Verified,
SUM(CASE WHEN patch.LastStateName = 'Retrying' THEN 1 ELSE 0 END) AS Retrying,
SUM(CASE WHEN patch.LastStateName = 'Preliminary Success' THEN 1 ELSE 0 END) AS PreSuccess,
SUM(CASE WHEN patch.LastStateName = 'Uninstalled' THEN 1 ELSE 0 END) AS Uninstalled,
SUM(CASE WHEN patch.LastStateName = 'Reboot pending' THEN 1 ELSE 0 END) AS PendReboot,
SUM(CASE WHEN patch.LastStateName = 'Failed' THEN 1 ELSE 0 END) AS Failed
FROM v_GS_PatchStatus AS patch INNER JOIN
dbo.v_R_System ON patch.ResourceID = dbo.v_R_System.ResourceID
WHERE ((patch.title LIKE 'Office XP Service Pack 3%') OR
(Patch.Title LIKE '%889167%') OR
(Patch.Title LIKE '%89284%') OR
(Patch.Title LIKE '%904443%') OR
(Patch.Title LIKE '%905553%') OR
(Patch.Title LIKE '%905555%') OR
(Patch.Title LIKE '%90564%') OR
(Patch.Title LIKE '%90575%') OR
(Patch.Title LIKE '%90911%') OR
(Patch.Title LIKE '%908981%') OR
(Patch.Title LIKE '%911831%') OR
(Patch.Title LIKE '%911701%') OR
(Patch.Title LIKE '%917627%') OR
(Patch.Title LIKE '%911907%') OR
(Patch.Title LIKE '%914451%') OR
(Patch.Title LIKE '%9165%') OR
(Patch.Title LIKE '%9173%') OR
(Patch.Title LIKE '%914455%') or
(Patch.Title Like '%914796%') or
(Patch.Title Like '%914797%') or
(Patch.Title Like '%917150%') or
(Patch.Title Like '%917151%') or
(Patch.Title Like '%917152%') or
(Patch.Title Like '%918419%') or
(Patch.Title Like '%918420%') or
(Patch.Title Like '%918424%') or
(Patch.Title Like '%92082%') or
(Patch.Title Like '%92156%') or
(Patch.Title LIKE '%89454%') or
(Patch.Title LIKE '%MSXML%') or
(Patch.Title LIKE '%Security Update for Word%895%') OR
(Patch.Title LIKE '%2003 service Pack%')) AND (dbo.v_R_System.Operating_System_Name_and0 LIKE '%workstation%')
GROUP BY QNumbers, ID, Product, Title, LocaleID) AS ps
ORDER BY Bulletin, Product
Errors year in review
SELECT CASE WHEN ID = 'None' THEN Title ELSE ID END AS ID, QNumbers, SUM(CASE WHEN lastexecutionresult = '61686' THEN 1 ELSE 0 END)
AS '61686 - Invalid_Switch', SUM(CASE WHEN lastexecutionresult = '61482' THEN 1 ELSE 0 END) AS '61482 - STATUS MUST RESTART FIRST',
SUM(CASE WHEN lastexecutionresult = '61957' THEN 1 ELSE 0 END) AS '61957 - STATUS UPDATE ALREADY RUNNING',
SUM(CASE WHEN isNull(lastexecutionresult, '') = '' THEN 1 ELSE 0 END) AS '<Null> - No Idea',
SUM(CASE WHEN lastexecutionresult = '-532459699' THEN 1 ELSE 0 END) AS '-532459699 - Yet to figure out',
SUM(CASE WHEN lastexecutionresult = '-1073741818' THEN 1 ELSE 0 END) AS '-1073741818 - The remote procedure call was cancelled.',
SUM(CASE WHEN lastexecutionresult = '-1073741819' THEN 1 ELSE 0 END)
AS '-1073741819 - Binding Handle does not contain all required information',
SUM(CASE WHEN lastexecutionresult = '-1073740972' THEN 1 ELSE 0 END) AS '-1073740972 - Yet to figure out',
SUM(CASE WHEN lastexecutionresult = '299' THEN 1 ELSE 0 END) AS '299 - ReadProcessMemroy or WriteProcessMemory Failed',
SUM(CASE WHEN lastexecutionresult = '999' THEN 1 ELSE 0 END) AS '999 - Error performing Inpage Operation',
SUM(CASE WHEN lastexecutionresult = '1' THEN 1 ELSE 0 END) AS '1 - Incorrect Function',
SUM(CASE WHEN lastexecutionresult = '3' THEN 1 ELSE 0 END) AS '3 - Cant find path', SUM(CASE WHEN lastexecutionresult = '5' THEN 1 ELSE 0 END)
AS '5 - Access Denied', SUM(CASE WHEN lastexecutionresult = '32' THEN 1 ELSE 0 END) AS '32 - Cannot access the file',
SUM(CASE WHEN lastexecutionresult = '53' THEN 1 ELSE 0 END) AS '53 - The network path was not found.',
SUM(CASE WHEN lastexecutionresult = '59' THEN 1 ELSE 0 END) AS '59 - An unexpected network error occurred',
SUM(CASE WHEN lastexecutionresult = '64' THEN 1 ELSE 0 END) AS '64 - The specified network name is no longer available',
SUM(CASE WHEN lastexecutionresult = '1231' THEN 1 ELSE 0 END) AS '1231 - Cant find path',
SUM(CASE WHEN lastexecutionresult = '3010' THEN 1 ELSE 0 END) AS '3010 - Needs Restart',
SUM(CASE WHEN lastexecutionresult = '1603' THEN 1 ELSE 0 END) AS '1603 - Fatal Error', SUM(1) AS 'total errors'
FROM v_gs_patchstatusex AS patch
WHERE ((patch.id LIKE 'MS06-%') OR (patch.title LIKE 'Office XP Service Pack 3%') OR
(Patch.Title LIKE '%889167%') OR
(Patch.Title LIKE '%89284%') OR
(Patch.Title LIKE '%904443%') OR
(Patch.Title LIKE '%905553%') OR
(Patch.Title LIKE '%905555%') OR
(Patch.Title LIKE '%90564%') OR
(Patch.Title LIKE '%90575%') OR
(Patch.Title LIKE '%90911%') OR
(Patch.Title LIKE '%908981%') OR
(Patch.Title LIKE '%911831%') OR
(Patch.Title LIKE '%911701%') OR
(Patch.Title LIKE '%917627%') OR
(Patch.Title LIKE '%911907%') OR
(Patch.Title LIKE '%914451%') OR
(Patch.Title LIKE '%9165%') OR
(Patch.Title LIKE '%9173%') OR
(Patch.Title Like '%914796%') or
(Patch.Title Like '%914797%') or
(Patch.Title Like '%917150%') or
(Patch.Title Like '%917151%') or
(Patch.Title Like '%917152%') or
(Patch.Title Like '%918419%') or
(Patch.Title Like '%918420%') or
(Patch.Title Like '%918424%') or
(Patch.Title Like '%92082%') or
(Patch.Title Like '%92156%') or
(Patch.Title LIKE '%89454%') or
(Patch.Title LIKE '%MSXML%') or
(Patch.Title LIKE '%Security Update for Word%895%') OR
(Patch.Title LIKE '%2003 service Pack%')) AND laststatename = 'failed'
GROUP BY ID, Qnumbers, title
ORDER BY ID
Their is another script out in the Code Repository of MyITForum.com.
10762Tricle_Feed.zip
The orginal script uses queries and grabs a random (possibly repeating) number of clients from a source collection. Then adds that query to the destination collection. This works well when you do large number of machines... but does not work so well on smaller groups.
My version attached and below:
- Does Direct Memberships
- Outputs to a log to check whats going on
- Takes a Source Collection with PCs added (assumes machine names are instance names)
- Grabs Total # of machines / divides by number you want to move at a time = #
- Takes the calculated # (in above step) machine each to not make it random but evenly dispersed
- Adds those machine names as direct membership rules in destination collection
'Script Updated by Shaun Cassells 11072006
'scassells at aegonusa.com
'**************************************************************************************
' Add scheduled task on server to run script every 30 minutes!
' Prompts for
' SMS Server
' SMS Site Code
' Number of machines to be moved per call
' Source Collection
' Destination Collection
'**************************************************************************************
Option Explicit
Dim SourceCollection, SourceCollectionID, DestinationCollection, DestinationCollectionID
Dim SMSSiteServerName, SMSSiteCode, MaximumMachinePerInterval
Dim instCollection, instDirectRule, objArgs
Dim objLocator, objSMS, objEnumerator, strQuery, instance, instances, objNameDictionary, objResrouceIDDictionary
err.clear
set objArgs = WScript.Arguments
'Read in Input
If objArgs.Count <> 5 Then
WScript.Echo "Missing arguements" & VbCrLf &_
"Please pass Two Collection Names (in quotes)" & VbCrLf &_
" The SMS Server Name " & VbCrLf & _
" The Site Code " & VbCrLf & _
" # of machines to be migrated at each interval " & VbCrLf & _
" The Source Collection Name " & VbCrLf & _
" The Destination Collection Name "
WScript.Quit
Else
SMSSiteServerName = ObjArgs(0)
SMSSiteCode = ObjArgs(1)
MaximumMachinePerInterval = ObjArgs(2)
SourceCollection = ObjArgs(3)
DestinationCollection = ObjArgs(4)
End If
FileAddLineToLogFile "************************************************************************************************************************"
FileAddLineToLogFile " " & Date & " " & Time & " *******************************************************************************"
FileAddLineToLogFile "************************************************************************************************************************"
FileAddLineToLogFile "Starting Script"
FileAddLineToLogFile "MaximumMachinePerInterval = " & MaximumMachinePerInterval
FileAddLineToLogFile "SourceCollection = " & SourceCollection
FileAddLineToLogFile "DestinationCollection = " & DestinationCollection
FileAddLineToLogFile "SMSSiteServerName = " & SMSSiteServerName
FileAddLineToLogFile "SMSSiteCode = " & SMSSiteCode
FileAddLineToLogFile "connect to scripting WMI interface"
Set objLocator = CreateObject("WbemScripting.SWbemLocator")
FileAddLineToLogFile "Connect to the server and Name space"
set objSMS=objLocator.ConnectServer(SMSSiteServerName, "root\sms\site_" & SMSSiteCode)
FileAddLineToLogFile "set the security level to use impersonation"
objSMS.Security_.ImpersonationLevel = 3
FileAddLineToLogFile "Get the SourceCollection contents"
strQuery = "select CollectionID, Name from SMS_Collection where (Name in ('" & SourceCollection & "', '" & DestinationCollection & "'))"
Set instances = objSMS.ExecQuery(strQuery)
If instances.Count <> 2 Then
FileAddLineToLogFile "Collection Name ID lookup failed on one of the 2"
Wscript.QUIT
Else
Dim CountInstance
For Each instance In instances
Select Case instance.Name
Case SourceCollection
SourceCollectionID = instance.CollectionID
Case DestinationCollection
DestinationCollectionID = instance.CollectionID
End Select
Next
End If
FileAddLineToLogFile "SourceCollectionID = " & SourceCollectionID
FileAddLineToLogFile "DestinationCollectionID = " & DestinationCollectionID
FileAddLineToLogFile "Create Dictionary Object"
Set objNameDictionary = CreateObject("Scripting.Dictionary")
Set objResrouceIDDictionary = CreateObject("Scripting.Dictionary")
Dim objDicItem, TotalSourceCollection, InstancestoPull, PositionDictionary
FileAddLineToLogFile "************************************************************************************************************************"
FileAddLineToLogFile "Pull The Collection contents machine Names"
strQuery = "SELECT Name, ResourceID FROM SMS_CM_RES_COLL_" & SourceCollectionID
FileAddLineToLogFile strQuery
Set objEnumerator = objSMS.ExecQuery(strQuery)
FileAddLineToLogFile "Now we have all of the system Names and ResouceIDs in a Dictionary Object;"
For Each instance In objEnumerator
FileAddLineToLogFile instance.Name
FileAddLineToLogFile instance.ResourceID
Next
FileAddLineToLogFile "************************************************************************************************************************"
TotalSourceCollection = objEnumerator.Count
FileAddLineToLogFile "TotalSourceCollection = " & TotalSourceCollection
'Round to the nearest whole number
InstancestoPull = round((TotalSourceCollection / MaximumMachinePerInterval), 0)
FileAddLineToLogFile "MaximumMachinePerInterval = " & MaximumMachinePerInterval
'If value is less than 1 that means pull all remaing values
If InstancestoPull < 1 Then
InstancestoPull = 1
End If
FileAddLineToLogFile "InstancestoPull = " & InstancestoPull
CountInstance = 0
PositionDictionary = 0
FileAddLineToLogFile "************************************************************************************************************************"
FileAddLineToLogFile "Get Values out into Dictionary Objects"
For Each instance in objEnumerator
FileAddLineToLogFile "CountInstance mod InstancestoPull = " & (CountInstance mod InstancestoPull)
FileAddLineToLogFile "CountInstance = " & CountInstance
FileAddLineToLogFile "InstancestoPull = " & InstancestoPull
If (CountInstance mod InstancestoPull) = 0 Then
'-------------------------------------------------------------------
'Get the Values and add to the Dictionary
FileAddLineToLogFile instance.Name & " at place " & CountInstance
objNameDictionary.Add PositionDictionary, instance.Name
objResrouceIDDictionary.Add PositionDictionary, instance.ResourceID
PositionDictionary = PositionDictionary + 1
End If
CountInstance = CountInstance + 1
Next
FileAddLineToLogFile "************************************************************************************************************************"
FileAddLineToLogFile "Time to remove objects out of SourceCollection"
FileAddLineToLogFile "Connect to the Source Collection = " & SourceCollection
FileAddLineToLogFile "Connect to the Source CollectionID = " & SourceCollectionID
Set instCollection = objSMS.Get("SMS_Collection.CollectionID=""" & SourceCollectionID & """")
FileAddLineToLogFile "Connect to the Direct Rule Source"
FileAddLineToLogFile "************************************************************************************************************************"
For Each objDicItem In objNameDictionary
FileAddLineToLogFile "Remove Machine ResourceID from Source Collection = " & UCase(objResrouceIDDictionary.Item(objDicItem))
Set instDirectRule = objSMS.Get("SMS_CollectionRuleDirect").SpawnInstance_
instDirectRule.ResourceID = UCase(objResrouceIDDictionary.Item(objDicItem))
instCollection.DeleteMembershipRule instDirectRule
Next
FileAddLineToLogFile "Initiate a Refresh of Source Collection ONLY"
instCollection.RequestRefresh False
FileAddLineToLogFile "************************************************************************************************************************"
FileAddLineToLogFile "Free the Query from memory"
Set objEnumerator = Nothing
Set instDirectRule = Nothing
Set instCollection = Nothing
FileAddLineToLogFile "************************************************************************************************************************"
FileAddLineToLogFile "Now we have all of the system Names and ResouceIDSin a Dictionary Object;"
For Each objDicItem In objNameDictionary
FileAddLineToLogFile UCase(objNameDictionary.Item(objDicItem))
FileAddLineToLogFile UCase(objResrouceIDDictionary.Item(objDicItem))
Next
FileAddLineToLogFile "************************************************************************************************************************"
FileAddLineToLogFile "Connect to the Destination Collection = " & DestinationCollection
FileAddLineToLogFile "Connect to the Destination CollectionID = " & DestinationCollectionID
set instCollection = objSMS.Get("SMS_Collection.CollectionID=""" & DestinationCollectionID & """")
FileAddLineToLogFile "Connect to the Direct Rule"
For Each objDicItem in objNameDictionary
Set instDirectRule = objSMS.Get("SMS_CollectionRuleDirect").SpawnInstance_
instDirectRule.ResourceClassName = "SMS_R_System"
instDirectRule.ResourceID = UCase(objResrouceIDDictionary.Item(objDicItem))
FileAddLineToLogFile "Adding ResourceID = " & instDirectRule.ResourceID
instDirectRule.RuleName = UCase(objNameDictionary.Item(objDicItem))
FileAddLineToLogFile "Adding RuleName = " & instDirectRule.RuleName
instCollection.AddMembershipRule instDirectRule
FileAddLineToLogFile "Adding Direct Rule"
Next
FileAddLineToLogFile "Initiate a Refresh of this Destination Collection ONLY"
instCollection.RequestRefresh False
FileAddLineToLogFile "************************************************************************************************************************"
FileAddLineToLogFile "Empty variables from memory"
Set instDirectRule = Nothing
Set instCollection = Nothing
Set objSMS = Nothing
Set objLocator = Nothing
Set objArgs = Nothing
FileAddLineToLogFile "Notify user of script completion"
'**************************************************************************************************************
Sub FileAddLineToLogFile (DataToAdd)
'Function
'Add a log entry to where the current script is located with extention of .log
'Input
'Data to be added to the log
'Output
'Log file in script directory contains the new log entry
on error resume next
Const ForAppending = 8
Dim objFSO, objLogFile, LogFileName
Set objFSO = CreateObject("Scripting.FileSystemObject")
LogFileName = wscript.scriptfullname & ".log"
Set objLogFile = objFSO.OpenTextFile ( LogFileName, ForAppending, True)
'Write the Data
objLogFile.WriteLine Date & " " & Time & " " & DataToAdd
'Close the file
objLogFile.Close
End Sub