This VBS Script will send the following information for a specifies site server to excel: The Machine Name and whether it is Obsolete or Active and its last Hardware Scan Date.
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 = "Obsolete"
objExcel.Cells(1, 3).Value = "Active"
objExcel.Cells(1, 4).Value = "Hardware Scan Date"
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.Obsolete0, SD.Active0," & _
" Convert(VarChar(11), WS.LastHwScan, 109)Date" & _
" From v_R_System SD" & _
" Join v_GS_WORKSTATION_STATUS WS On SD.ResourceID = WS.ResourceID" _
, 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("Obsolete0").Value
If objExcel.Cells(intRow, 2).Value = "1" Then
objExcel.Cells(intRow, 2).Value = "Yes"
Else
objExcel.Cells(intRow, 2).Value = "No"
End If
objExcel.Cells(intRow, 3).Value = objRecordSet.Fields("Active0").Value
If objExcel.Cells(intRow, 3).Value = "1" Then
objExcel.Cells(intRow, 3).Value = "Yes"
objExcel.Cells(intRow, 3).Value = "No"
objExcel.Cells(intRow, 4).Value = objRecordSet.Fields("Date").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"
This VBS Script will allow you to browse for a directory folder and will delete all of the files in it.
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder (0, "Select The Folder To Enumerate :", (0))
If objFolder Is Nothing Then
Wscript.Quit
Set objFolderItem = objFolder.Self
objPath = objFolderItem.Path
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(objPath)
For each objFile in objFolder.Files
If objFolder.Files.Count > 0 Then
objFile.Delete True
Next
This VBS script will take a list of machine names from a text file called MachineList.Txt and will send the following information to an Excel spreadsheet taken from the “All Systems” Collection: Machine Name, Resource ID, Site Code and Domain name.
On Error Resume Next
strComputer = InputBox ("Enter SMS Server Name")
strSiteCode = InputBox ("Enter Site Code")
objExcel.Cells(1, 2).Value = "Resource ID"
objExcel.Cells(1, 3).Value = "Site Code"
objExcel.Cells(1, 4).Value = "Domain"
Set Fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fso.OpenTextFile("MachineList.Txt")
Do While Not (InputFile.atEndOfStream)
strComputer = InputFile.ReadLine
Set objWMIService = GetObject("winmgmts://" & strComputer & "\root\sms\site_" & strSiteCode)
Set colItems = objWMIService.ExecQuery("Select * from SMS_CM_RES_COLL_SMS00001 Where Name = '" & strComputer & "'")
If Err.Number <> 0 Then
objExcel.Cells(intRow, 1).Value = UCase(strComputer)
objExcel.Cells(intRow, 2).Value = "Not Found"
Err.Clear
For Each objItem in colItems
objExcel.Cells(intRow, 1).Value = objItem.Name
objExcel.Cells(intRow, 2).Value = objItem.ResourceID
objExcel.Cells(intRow, 3).Value = objItem.SiteCode
objExcel.Cells(intRow, 4).Value = objItem.Domain
objExcel.Selection.Interior.ColorIndex = 19
LogonType 10 is RemoteInteractive and covers both Interactive and Remote Terminal Services sessions. This is not avalible for Windows 2000
strComputer = InputBox("Enter Terminal Server Name")
objExcel.Cells(1, 1).Value = "Logon Name"
objExcel.Cells(1, 2).Value = "Full Name"
objExcel.Cells(1, 3).Value = "Timestamp"
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colRts = objWMIService.ExecQuery _
("Select * from Win32_LogonSession Where LogonType = 10")
For Each objSession in colRts
Set colItems = objWMIService.ExecQuery("Associators of " _
& "{Win32_LogonSession.LogonId=" & objSession.LogonId & "} " _
& "Where AssocClass = Win32_LoggedOnUser Role = Dependent" )
objExcel.Cells(intRow, 1).Value = objItem.Domain & "\" & objItem.Name
objExcel.Cells(intRow, 2).Value = objItem.FullName
objExcel.Cells(intRow, 3).Value = ConvWbemTime(objSession.StartTime)
Function ConvWbemTime(IntervalFormat)
sMonth = mid(IntervalFormat,5,2)
sDay = mid(IntervalFormat,7,2)
sYear = mid(IntervalFormat,1,4)
sHour = mid(IntervalFormat,9,2)
sMinutes = mid(IntervalFormat,11,2)
ConvWbemTime = sMonth & "-" & sDay & "-" & sYear & " " & sHour & ":" & sMinutes
End Function
objExcel.Range("A1:C1").Select