1) Include 2 valid AD server names with valid descriptions in AD, one with name
2) Include 1 valid AD server name, with no description blank, one non-existent server
3) Include 1 invalid AD server name
· FileserverA (this would have a description in AD)
· FileserverB (no description in AD)
· Test (this would have a description in AD)
· BrZmN (this would be a non-existent server)
'-------------------------------------------------------------------------
' Start of MAIN code (checkservers.vbs)
' VBScript: checkservers.vbs
' Author: Jeff Mason aka TNJMAN aka bitdoctor
' 09/06/2013
'
'Basic premise: 1) Read through all rows of Col 1 on an Excel document
' 2) Read through each AD computer record
' 3) Update Excel with Description from AD
'Assumptions/Notes:
' 1) Create Excel document (c:\scripts\servers.xlsx) with ONE worksheet,
' containing only "server name" in Column 1
‘Other assumptions in the FULL SCRIPT, download now
' Assumptions:
' You must Set excelPath = "C:\scripts\servers.xlsx" (or wherever your xlsx file is)
' You must have at least "read" permissions to AD/LDAP
Option
Explicit
Dim
objExcel
excelPath
worksheetCount
counter
' To count rows and/or columns
currentWorkSheet
‘…
excelPath =
"C:\scripts\servers.xlsx"
‘Full code is listed in the FULL SCRIPT, download now
WScript.Echo
"Reading Data from Path/File: "
& excelPath
Set
objExcel = CreateObject(
"Excel.Application"
)
objExcel.DisplayAlerts = 0
' Don't display any messages about conversion and so forth
"-------------------------------------------------------"
"Reading data from worksheet "
& workSheetCount
& vbCRLF
currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(workSheetCount)
' What is the leftmost column in the spreadsheet that has data in it
left = currentWorksheet.UsedRange.Column
Cells = currentWorksheet.Cells
'-----------------------------------------------------------------------------
' Row Loop - Loop through each row in the worksheet (but only for Column 1)
' Only deal with Cols 1 & 2 of Sheet1, since SERVER=Col1 and DESCRIPTION=Col2
' Column 2 is built by "checksvr" subroutine, based on Column 1)
For
row = 0 to (usedRowsCount-1)
' only look at rows/cols in the "used" range
curRow = row+top
' curCol = column+left
If
IsEmpty(strDescription)
Then
' If Col 2 already populated, skip to next row in sheet
Not
(IsEmpty(server))
End
Next
' End Row loop
' Done with the current worksheet, release the memory
currentWorkSheet =
Nothing
‘Save and close the workbook - Full code is listed in the FULL SCRIPT, download now
"Finished."
' Finished with Excel object, release it from memory & get out !!!
objExcel =
WScript.Quit(0)
' End of MAIN code
' Subroutine (checksvr) to check for the sever name in Active Directory
Sub
checksvr(svr)
On
Error
Resume
' Point to the domain/ldap root
objRootDSE = GetObject(
"LDAP://RootDSE"
' Query all Active Directory (normally, leave this commented, query specific OU(s)
' strRoot = objRootDSE.Get("DefaultNamingContext") 'Uncomment to search ENTIRE AD TREE
' Query a specific Organizational Unit
strRoot =
"OU=Servers,DC=YOUR-DOMAIN,DC=com"
' Comment this out, if searching ALL OF AD
objCn.Provider =
"ADsDSOObject"
objCn.Open
"Active Directory Provider"
' Filter the query for only sAMAccountName,description of any computers in AD
objCmd.commandtext = …
svrcmp = UCase(svr) &
"$"
'Upper-case the Server entry from the spreadsheet for consistent compare
svrflag =
""
'Clear out the "found-server" flag
Do
While
objRes.EOF
' If description is blank/null, set the value to the word "BLANK"
strDescription =
(IsNUll(objRes.Fields(
"description"
).Value))
‘ …
' We want to check ALL descriptions, including null descriptions
' But only for the server passed into this script as an argument
svrcmp = objRes.Fields(
"sAMAccountName"
).Value
'If Excel server name found in AD, set svrflag = "TRUE" & end the subroutine
"TRUE"
'Write this to the Excel spreadsheet / exit the subroutine
Exit
'Move to / read the next AD resource record
objRes.MoveNext
Loop
'If flag never set to "TRUE" then fall out through here - server not found in AD
"NOT FOUND IN AD"
objRes.close
ObjCn.close