Use the following Vbs script to count the number of client machines for your site code using the _Res_Coll_Sms00001 collection IsClient attribute.
Note: Change the “ServerName” in line to your server name and change “SMS_XXX” to your three letter site code.
Vbs Script:
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
intRow = 2
objExcel.Cells(1, 1).Value = "Client Count"
objExcel.Cells(1, 2).Value = "Site Code"
objExcel.Cells(1, 3).Value = "Report Date"
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
"Provider = SQLOLEDB; Data Source = ServerName;" & _
"Initial Catalog = SMS_XXX;" & _
"Integrated Security = SSPI;"
objRecordSet.Open "Select * From _Res_Coll_Sms00001 Where IsClient = 1 and SiteCode = 'XXX'",_
objConnection, adOpenStatic, adLockOptimistic
Do Until objRecordset.EOF
objExcel.Cells(intRow, 1).Value = objRecordSet.RecordCount
objRecordset.MoveNext
objExcel.Cells(intRow, 2).Value = objRecordSet("SiteCode")
Loop
objExcel.Cells(intRow, 3).Value = Now
intRow = intRow + 1
objExcel.Range("A1:C1").Select
objExcel.Selection.Interior.ColorIndex = 19
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit
Set objConnection = Nothing
Set objRecordSet = Nothing
Set objExcel = Nothing
No Comments