This VBS script will allow you to find all client machines that have a specified product installed. And will send the results to an excel spreadsheet.
strProductName Examples:
Microsoft .NET Framework 3.0
%NET Framework 3%
VBS Script:
strServer = InputBox ("Site Server Name")
strDatabase = InputBox ("Site Code")
strProductName = InputBox ("Product Name")
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 = "User Name"
objExcel.Cells(1, 3).Value = "Product Name"
objExcel.Cells(1, 4).Value = "Product Version"
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 SD.Name0, SD.User_Name0, SP.ProductName, SP.ProductVersion" & _
" From vSMS_G_System_SoftwareProduct SP" & _
" Join System_Disc SD on SD.ItemKey = SP.ClientId" & _
" Where SP.ProductName = '" & strProductName & "' " _
, 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("User_Name0").Value
objExcel.Cells(intRow, 3).Value = objRecordSet.Fields("ProductName").Value
objExcel.Cells(intRow, 4).Value = objRecordSet.Fields("ProductVersion").Value
objRecordSet.MoveNext
intRow = intRow + 1
Loop
objExcel.Range("A1:D1").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"
No Comments