I started messing around with creating scripts that output their information directly to an Excel file.
This mainly came about due to an audit and the auditors wanting to see each step we went through on verifying local admin accounts across our network.
Here are three scripts to show the steps we had to go through.
The first script "List_LocalAdmins.vbs" only requires a text file called "pclist.txt" which contains a list of every machine you want to run the script against. (Each machine must be on it's own line, no blank lines between the machine lines)
Machine
Machine
etc...
'===================================================================
' Title: List all Local Admin Accounts
' Description: Runs from a pclist to check which accounts are setup
' in the local admin group.
' Everything is written to an Excel Worksheet. Sheet(1)
' will list the machine name and the accounts.
' Sheet(2) will list the machines that are "OFFLINE"
'
' Requirements: MSExcel
' pclist.txt containing list of machines to run against.
' Each machine must be on it's own sepearate line.
'
' Created by: Chris Hatt
' Creatd on: 05/01/06
'====================================================================
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oShell = WScript.CreateObject("WScript.Shell")
Set oXLS = CreateObject("Excel.Application")
Const PCL = "pclist.txt"
strXLSFile = "C:\LocalAdmins.xls"
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Force script to run in "CScript" mode
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
If Instr(1, WScript.FullName, "CScript", vbTextCompare) = 0 Then
oShell.Run "cscript """ & WScript.ScriptFullName & """", 1, False
WScript.Quit
End If
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Deletes the old log file
If oFS.FileExists(strXLSFile)Then
oFS.DeleteFile strXLSFile, TRUE
End If
'Creation of the Excel Workbook
oXLS.Visible = TRUE
oXLS.Workbooks.Add
intIndex = 2
oXLS.Sheets(3).Delete 'This removes the 3rd worksheet since it is not in use
'Creates the Column Headers for each page
oXLS.Sheets(1).Cells(1,1).Value = "Computer"
oXLS.Sheets(1).Cells(1,2).Value = "Accounts->"
oXLS.Sheets(1).Cells(1,1).Font.Bold = TRUE
oXLS.Sheets(1).Cells(1,2).Font.Bold = TRUE
oXLS.Sheets(2).Cells(1,1).Value = "Computer"
oXLS.Sheets(2).Cells(1,2).Value = "OFFLINE"
oXLS.Sheets(2).Cells(1,1).Font.Bold = TRUE
oXLS.Sheets(2).Cells(1,2).Font.Bold = TRUE
'Name the worksheets
oXLS.Sheets(1).Name = "LocalAdminAccounts"
oXLS.Sheets(2).Name = "OFFLINE"
strCount = 0
'This is where it grabs the list of machines to run against
If oFS.FileExists(PCL)Then
Set file = oFS.GetFile(PCL)
Set mc = file.OpenAsTextStream(1,-2)
Do While Not mc.AtEndofStream
line = mc.Readline
strCount = strCount + 1 'This gives us a countdown for machines remaining
Loop
mc.Close
Set pc = file.OpenAsTextStream(1,-2)
Do While Not pc.AtEndOfStream
myLine = Trim(pc.ReadLine)
Call stripMachine(myLine,strCount)
Loop
pc.Close
Else
wscript.echo "No " & PCL & " found!" & vbcr & "Script will now close."
wscript.quit
End If
'AutoFit and sort each page
For z = 1 to 2
oXLS.Sheets(z).Activate
oXLS.Sheets(z).Columns.AutoFit
oXLS.Sheets(z).Cells(1,1).Select
Set oRange = oXLS.Range("A:Z")
Set oRange2 = oXLS.Range("A2")
oRange.Sort oRange2,1,,,,,,1
'... for descending, or
'oRange.Sort oRange2,1,,,,,,1
'... for ascending
Next
oXLS.ActiveWorkbook.SaveAs strXLSFile,TRUE
MsgBox "Finished" & vbcr & "File is located at: " & vbcr & strXLSFile,64,"Finished"
'Functions
'------------------------------------------------------------------------------
FUNCTION stripMachine(myLine,strCount)
strComputer = myLine
wscript.echo strCount & vbTab & strComputer
strCount = strCount - 1
Set cPingResults = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2").ExecQuery("SELECT * FROM Win32_PingStatus WHERE Address = '" + strComputer + "'")
For Each oPingResult In cPingResults
If oPingResult.StatusCode <> 0 Then
call LogEvent(strComputer)
Exit Function
Else
call verifyAccount(strComputer)
End If
Next
End Function
FUNCTION verifyAccount(strComputer)
'Some machine may not allow you to pull this information
On Error Resume Next
Set colGroups = GetObject("WinNT://" & strComputer & "")
colGroups.Filter = Array("group")
For Each oGroup In colGroups
If (oGroup.Name = "Administrators") Then
For Each oUser in oGroup.Members
strUser = strUser & ";" & oUser.Name
Next
End If
Next
If Left(strUser,1) = ";" Then
LUser = Len(strUser) - 1
strUser = Mid(strUser,2,LUser)
End If
On Error Goto 0
call logWrite(strComputer,strUser)
END FUNCTION
FUNCTION logWrite(strComputer,strUser)
oXLS.Sheets(1).Activate
oXLS.Cells(intIndex,1).Value = UCase(strComputer)
If strUser = "" Then
oXLS.Cells(intIndex,2).Value = "CANNOT BE MANAGED"
oXLS.Cells(intIndex,1).Interior.ColorIndex = 3
oXLS.Cells(intIndex,2).Interior.ColorIndex = 3
Else
sSplit = Split(strUser,";")
For A = 1 to UBound(sSplit)
Next
For c = 2 to A
x = c - 2
oXLS.Cells(intIndex,c).Value = UCase(sSplit(x))
Next
End If
intIndex = intIndex + 1
oXLS.Cells(intIndex,1).Select
END FUNCTION
FUNCTION LogEvent(strComputer)
oXLS.Sheets(2).Activate
oXLS.Cells(intIndex,1).Interior.ColorIndex = 10
oXLS.Cells(intIndex,2).Interior.ColorIndex = 10
oXLS.Cells(intIndex,1).Value = UCase(strComputer)
oXLS.Cells(intIndex,2).Value = "OFFLINE"
intIndex = intIndex + 1
oXLS.Cells(intIndex,1).Select
End Function
The results from the first script, for us, were handed to upper management to look over. They would delete the rows of the machines that wanted to keep the local admin on (or just remove specific accounts they wanted to keep on that machine). Anything left in the spreadsheet would be removed.
This second script would read through the Excel file that the "List_LocalAdmins.vbs" has created. This will throw all the contents into a text file for the "RemoveAdminAccounts.vbs"
"CreateAdminRemoveList.vbs"
'===================================================================
' Title: Create Admin Remove List
' Description: Reads the Excel Worksheet to create a text file for
' removing the local admin accounts.
' (Used prior to "RemoveAdminAccount.vbs")
' This was created because upper management wanted details
' for each step in this process (auditing purposes).
'
' Requirements: MSExcel
'
' Created by: Chris Hatt
' Creatd on: 05/01/06
'====================================================================
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oExcel = CreateObject("Excel.Application")
Set oWorkbook = oExcel.Workbooks.Open("C:\LocalAdmins.xls") 'Must use full path to Excel file
Const OUTPUT = "AdminAccounts.txt"
If oFS.FileExists("old" & OUTPUT)Then
oFS.DeleteFile "old" & OUTPUT, True
ElseIf oFS.FileExists(OUTPUT)Then
oFS.MoveFile OUTPUT,"old" & OUTPUT
End If
'Skip the "Header" Row; skip the machine name
intRow = 2
intCol = 2
oExcel.Sheets(1).Activate
Set x = oFS.OpenTextFile(OUTPUT,8,True)
Do Until oExcel.Cells(intRow,1).Value = ""
Do Until oExcel.Cells(intRow,intCol).Value = ""
aAccount = aAccount & vbTab & oExcel.Cells(intRow,intCol).Value
intCol = intCol + 1
Loop
strCount = intCol - 2
If aAccount <> "" Then
x.WriteLine strCount & vbTab & oExcel.Cells(intRow,1).Value & aAccount
End If
strTotal = strTotal + strCount
intRow = intRow + 1
intCol = 2
aAccount = ""
Loop
x.WriteLine
x.WriteLine "Total Accounts:" & vbTab & strTotal
x.Close
strTotal = ""
Set x = Nothing
oExcel.Quit
MsgBox "Done"
Now our "AdminAccounts.txt" is ready to run our "RemoveAdminAccounts.vbs"
For each machine, and account listed for the machine in the "AdminAccounts.txt" will be removed.
This script requires the CUSRMGR.EXE tool "RemoveAdminAccount.vbs"
'===================================================================
' Title: Remove Local Admin Accounts
' Description: Runs from RemoveAdmin.txt (a pclist) with the list of
' accounts that need to be removed.
' MachineName;Account;Account;etc...
'
' Requirements: CUSRMGR.EXE
' This command-line tool enables editing of many of the properties
' available through the Local Users and Groups, a Microsoft® Windows®
' 2000 system tool that is part of the Computer Management MMC snap-in.
' With CusrMgr, you can rename or delete users, set passwords, and set
' or reset other user properties.
'
' MSExcel
'
' Created by: Chris Hatt
' Creatd on: 05/01/06
'====================================================================
Set oFS = CreateObject("Scripting.FileSystemObject")
Set oShell = WScript.CreateObject("WScript.Shell")
Set oXLS = CreateObject("Excel.Application")
Const PCL = "AdminAccounts.txt"
Const oCMGR = "cusrmgr.exe "
strXLSFile = "C:\LocalAdminRemain.xls"
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' Force script to run in "CScript" mode
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
If Instr(1, WScript.FullName, "CScript", vbTextCompare) = 0 Then
oShell.Run "cscript """ & WScript.ScriptFullName & """", 1, False
WScript.Quit
End If
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
If oFS.FileExists(strXLSFile)Then
oFS.DeleteFile strXLSFile, TRUE
End If
oXLS.Visible = TRUE
oXLS.Workbooks.Add
intIndex = 2
oXLS.Sheets(3).Delete
oXLS.Sheets(1).Cells(1,1).Value = "Computer"
oXLS.Sheets(1).Cells(1,2).Value = "Accounts->"
oXLS.Sheets(1).Cells(1,1).Font.Bold = TRUE
oXLS.Sheets(1).Cells(1,2).Font.Bold = TRUE
oXLS.Sheets(2).Cells(1,1).Value = "Computer"
oXLS.Sheets(2).Cells(1,2).Value = "OFFLINE"
oXLS.Sheets(2).Cells(1,1).Font.Bold = TRUE
oXLS.Sheets(2).Cells(1,2).Font.Bold = TRUE
'Name the worksheets
oXLS.Sheets(1).Name = "LocalAdminAccounts"
oXLS.Sheets(2).Name = "OFFLINE"
strCount = 0
If oFS.FileExists(PCL)Then
Set file = oFS.GetFile(PCL)
Set mc = file.OpenAsTextStream(1,-2)
Do While Not mc.AtEndofStream
line = mc.Readline
strCount = strCount + 1
Loop
mc.Close
Set pc = file.OpenAsTextStream(1,-2)
Do While Not pc.AtEndOfStream
myLine = Trim(pc.ReadLine)
Call stripMachine(myLine,strCount)
Loop
pc.Close
Else
wscript.echo "No " & PCL & " found!" & vbcr & "Script will now close."
wscript.quit
End If
'AutoFit and sort our pages
For z = 1 to 2
oXLS.Sheets(z).Activate
oXLS.Sheets(z).Columns.AutoFit
oXLS.Sheets(z).Cells(1,1).Select
Set oRange = oXLS.Range("A:Z")
Set oRange2 = oXLS.Range("B1")
oRange.Sort oRange2,1,,,,,,1
'... for descending, or
'oRange.Sort oRange2,1,,,,,,1
'... for ascending
Next
oXLS.ActiveWorkbook.SaveAs strXLSFile,TRUE
MsgBox "Finished" & vbcr & "File is located at: " & vbcr & strXLSFile,64,"Finished"
'Functions
'------------------------------------------------------------------------------
FUNCTION stripMachine(myLine,strCount)
fSplit = Split(myLine,";")
strComputer = fSplit(0)
wscript.echo strCount & vbTab & strComputer
strCount = strCount - 1
Set cPingResults = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2").ExecQuery("SELECT * FROM Win32_PingStatus WHERE Address = '" + strComputer + "'")
For Each oPingResult In cPingResults
If oPingResult.StatusCode <> 0 Then
call LogEvent(strComputer)
Exit Function
End If
For i = 1 to UBound(fSplit)
Set colGroups = GetObject("WinNT://" & strComputer & "")
colGroups.Filter = Array("group")
For Each oGroup In colGroups
If (oGroup.Name = "Administrators") Then
For Each oUser in oGroup.Members
If LCase(oUser.Name) = LCase(fSplit(i)) Then
oShell.Run oCMGR & "-u ""corp\" & fSplit(i) & """ -m " & strComputer & " -dlg ""Administrators""",0,True
End If
Next
End If
Next
Next
call verifyAccount(strComputer)
End Function
FUNCTION verifyAccount(strComputer)
Set colGroups = GetObject("WinNT://" & strComputer & "")
colGroups.Filter = Array("group")
For Each oGroup In colGroups
If (oGroup.Name = "Administrators") Then
For Each oUser in oGroup.Members
strUser = strUser & ";" & oUser.Name
Next
End If
Next
If Left(strUser,1) = ";" Then
LUser = Len(strUser) - 1
strUser = Mid(strUser,2,LUser)
End If
call logWrite(strComputer,strUser)
END FUNCTION
FUNCTION logWrite(strComputer,strUser)
oXLS.Sheets(1).Activate
oXLS.Cells(intIndex,1).Value = strComputer
sSplit = Split(strUser,";")
For A = 1 to UBound(sSplit)
Next
For c = 2 to A
x = c - 2
oXLS.Cells(intIndex,c).Value = sSplit(x)
Next
intIndex = intIndex + 1
oXLS.Cells(intIndex,1).Select
END FUNCTION
FUNCTION LogEvent(strComputer)
oXLS.Sheets(2).Activate
oXLS.Cells(intIndex,1).Interior.ColorIndex = 10
oXLS.Cells(intIndex,2).Interior.ColorIndex = 10
oXLS.Cells(intIndex,1).Value = strComputer
oXLS.Cells(intIndex,2).Value = "OFFLINE"
intIndex = intIndex + 1
oXLS.Cells(intIndex,1).Select
End Function
I do realize that this could have been completed in two (maybe even one) script; but I had to lay it out this way to walk our Auditors through it.