This VBS script will take a site server name and site code via input dialog boxes and will send the machine names to Excel that do not have a serial number present in the SMS database.
VBS Script:
strServer = InputBox ("Enter Site Server Name")
strDatabase = InputBox ("Enter Three Letter Site Code")
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
intRow = 2
objExcel.Cells(1, 1).Value = "Machine Name"
objExcel.Cells(1, 2).Value = "Serial Number"
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB;Data Source =" & strServer & ";" & _
"Trusted_Connection=Yes;Initial Catalog =SMS_" & strDatabase
Set objRecordSet = CreateObject("ADODB.Recordset")
objRecordSet.Open "Select Distinct SD.Name0, SN.SerialNumber00" & _
" From v_R_System SD" & _
" Join System_Enclosure_Data SN On SN.MachineID = SD.ResourceID" & _
" Where SD.Client0 = 1" & _
" And SN.SerialNumber00 = 'Not Available' Or SN.SerialNumber00 Is Null", objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
objExcel.Cells(intRow, 1).Value = objRecordSet.Fields("Name0").Value
objExcel.Cells(intRow, 2).Value = objRecordSet.Fields("SerialNumber00").Value
If objExcel.Cells(intRow, 2).Value = "" Then
objExcel.Cells(intRow, 2).Value = "NULL"
End If
objRecordSet.MoveNext
intRow = intRow + 1
Loop
objExcel.Range("A1:B1").Select
objExcel.Selection.Font.ColorIndex = 11
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
Set objRange = objExcel.Range("A1")
objRange.Sort objRange,1,,,,,,1
MsgBox "Done"
142e1885-4219-486c-ac2c-c42265ae5dc2|0|.0