A script to get a count of workstations in AD

I have to create a script that will allow me to retrieve the amount of workstations in Active Directory for certain OU at a time. I have no clue what to do. Last time I did any scripting was back in college.  I somehow create a script below to pull the information I need. However, I am getting a list of everything in AD. I just want to be able to pull information from specific OU and its subOU. Any idea on what I can do?

Below is the script:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



dim objRecordSet
dim objFSO
dim objLogFile
dim objOUFile

Const ADS_SCOPE_SUBTREE = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOUFile = objFSO.OpenTextFile("OUList.txt", 1)
Set objLogFile = objFSO.CreateTextFile("Number_of_Workstations.csv", true)
Set objConnection = CreateObject("ADODB.Connection")

Do Until objOUFile.AtEndOfStream

strOU = objOUFile.Readline

'WScript.Echo strOU

' Connect to AD
objConnection.Open "Provider=ADsDSOObject;"

' Some AD command objects
Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 1000
objCommand.Properties("SearchScope") = ADS_SCOPE_SUBTREE

' Execute command to get all users in our OU
objCommand.CommandText = "SELECT Name FROM 'LDAP://" & strOU & " '" & " WHERE objectcategory='Computer'"
'Wscript.Echo objCommand.CommandText
Set objRecordSet = objCommand.Execute

Wscript.Echo objRecordSet.RecordCount
objLogFile.Write objRecordSet.RecordCount & "," & strOU  & vbNewLine

objRecordSet.Close
objConnection.Close

Loop
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Whitley Bowen JrNetwork Infrastructure Service SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve KnightIT ConsultancyCommented:
Various ways to do that, depending upon what you want the information for you might find this spreadsheet of mine useful?  Press Alt-F11 to see the VBA code doing the AD queries.

http://scripts.dragon-it.co.uk/links/vba-export-server-list-AD

Steve
Whitley Bowen JrNetwork Infrastructure Service SpecialistAuthor Commented:
Steve,

I am not able to open the link. Can you provide it to me another way?
Steve KnightIT ConsultancyCommented:
Not sure why you can't access the site but here you are attached here.
AD-computers.xlsm
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Whitley Bowen JrNetwork Infrastructure Service SpecialistAuthor Commented:
I got it but although helpful it is not what I need. Maybe I should go a little more in detail.  Each OU has 3 OU named exempt, non-complaint, and non-compliant warning within workstations. My goal is to get a numeral count of total workstations that are in each OU; the total of exempt, non-complaint, and non-compliant warning. All I need is a number count. Am I making sense?
omgangIT ManagerCommented:
Per your original Q the following script will count the total number of computer objects from the specified OU.  You'd simply modify the script each time for a different OU
or
Modify the script to prompt for the OU
or
Modify the script to recursively enumerate all OUs
OM Gang

Option Explicit
Dim strMember, strDNSDomain, strContainer, strPath, strFileName
Dim objComputer, objRootDSE, objParentOU, objFSO, objFile
Dim arrMemberOf, intCount

intCount = 0

strContainer = "OU=Workstation-OU,OU=Parent-OU, "   '<--- change this for your environment
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("DefaultNamingContext")

'the script was originally used to write each workstation name to an output file
strPath = "C:\temp\"
strFileName = Trim(strContainer) & ".txt"

'commented out the following since you only want a count of workstation objects
'Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set objFile = objFSO.CreateTextFile(strPath & strFileName)

Set objParentOU = GetObject("LDAP://"& strContainer & strDNSDomain)
objParentOU.Filter = Array("Computer")

For Each objComputer in objParentOU
      intCount = intCount + 1
      'objFile.WriteLine objComputer.CN
Next

'WScript.echo "Completed.  See output file " & strPath & strFileName
WScript.echo "Total workstations in " & strContainer & ":  " & intCount

WScript.Quit

objFile.Close

Set objFile = Nothing
Set objGroup = Nothing
Set objParentOU = Nothing
Set objFSO = Nothing
Set objRootDSE = Nothing

Open in new window

Steve KnightIT ConsultancyCommented:
Apologies for going off track, had mis-read the original requirement.  Once you have that raw data out though all you have to do is select the level of OU field you want and drag that field to the pivot table and you have your numbers.

Steve
omgangIT ManagerCommented:
Steve, I've downloaded a copy of your Excel workbook and it is slick (good work).  For this Q, though, I'm not getting a total count of workstations when I filter for the specific OU; I am seeing the listing of all  computers in the OU but not getting a total count displayed.

OM Gang
Whitley Bowen JrNetwork Infrastructure Service SpecialistAuthor Commented:
Omgang, I downloaded your Q. I am able to get a count but it doesn't create a file with the number. I edit the "strPath=" but still doesn't create a file. The number show up quickly in the cmd prompt then disappear so have to run it several times to see the number. Obviously I am doing something wrong. Can you identify?

Option Explicit
Dim strMember, strDNSDomain, strContainer, strPath, strFileName
Dim objComputer, objRootDSE, objParentOU, objFSO, objFile
Dim arrMemberOf, intCount

intCount = 0

strContainer = "OU=Workstations,OU=,OU=,OU=,OU=, "   '<--- change this for your environment
Set objRootDSE = GetObject("LDAP://RootDSE")
strDNSDomain = objRootDSE.Get("DefaultNamingContext")

'the script was originally used to write each workstation name to an output file
strPath = "C:\Users\Whit\Desktop\Operation Count\test.txt"
strFileName = Trim(strContainer) & ".txt"

'commented out the following since you only want a count of workstation objects
'Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set objFile = objFSO.CreateTextFile(strPath & strFileName)

Set objParentOU = GetObject("LDAP://"& strContainer & strDNSDomain)
objParentOU.Filter = Array("Computer")

For Each objComputer in objParentOU
      intCount = intCount + 1
      'objFile.WriteLine objComputer.CN
Next

'WScript.echo "Completed.  See output file " & strPath & strFileName
WScript.echo "Total workstations in " & strContainer & ":  " & intCount

WScript.Quit

objFile.Close

Set objFile = Nothing
Set objGroup = Nothing
Set objParentOU = Nothing
Set objFSO = Nothing
Set objRootDSE = Nothing
omgangIT ManagerCommented:
Try this
OM Gang


Option Explicit
 Dim strMember, strDNSDomain, strContainer, strPath, strFileName
 Dim objComputer, objRootDSE, objParentOU, objFSO, objFile
 Dim arrMemberOf, intCount

 intCount = 0

 strContainer = "OU=Workstations,OU=DOL,OU=IMCOM,OU=Lewis1,OU=Installations, "   '<--- change this for your environment
 Set objRootDSE = GetObject("LDAP://RootDSE")
 strDNSDomain = objRootDSE.Get("DefaultNamingContext")

 'the script was originally used to write each workstation name to an output file
 strPath = "C:\Users\Whit\Desktop\Operation Count\test.txt"
 strFileName = Trim(strContainer) & ".txt"

 'commented out the following since you only want a count of workstation objects
 Set objFSO = CreateObject("Scripting.FileSystemObject")   '<---- this line needed to be uncommented
 Set objFile = objFSO.CreateTextFile(strPath & strFileName)    '<---- this one too

 Set objParentOU = GetObject("LDAP://"& strContainer & strDNSDomain)
 objParentOU.Filter = Array("Computer")

 For Each objComputer in objParentOU
       intCount = intCount + 1
       objFile.WriteLine objComputer.CN     '<---- and this one -- this is where the computer name gets written to the file  -- leave it commented if you don't need
 Next

  'these lines are new -- the first writes a blank line -- the second writes to total count to the file
  objFile.WriteLine ""
  objFile.WriteLine  "Total workstations in " & strContainer & ":  " & intCount

 'WScript.echo "Completed.  See output file " & strPath & strFileName
 WScript.echo "Total workstations in " & strContainer & ":  " & intCount

 WScript.Quit

 objFile.Close

 Set objFile = Nothing
 Set objGroup = Nothing
 Set objParentOU = Nothing
 Set objFSO = Nothing
 Set objRootDSE = Nothing

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Whitley Bowen JrNetwork Infrastructure Service SpecialistAuthor Commented:
OM Gang,

It worked! I am able to get a file with the computer names and number of workstations. I need to get the number count for non complaint warnings and non compliant.  both of them are within the workstation OU. I want to run this script and get all 3 numbers (workstation, non complaint warnings, and non compliant) in the txt file instead of just workstation.  Now, is it possible to add 2 additional "strContainer="?   I added them of it but it only show me the result of the last "strcontainer=" not all 3.
Steve KnightIT ConsultancyCommented:
"Steve, I've downloaded a copy of your Excel workbook and it is slick (good work).  For this Q, though, I'm not getting a total count of workstations when I filter for the specific OU; I am seeing the listing of all  computers in the OU but not getting a total count displayed."

You'd go to the pivot table report tab, click somewhere on the pivot table so the config area comes up.
drag the OU levels required to the row area, either both to the rows or first level to row and second level to columns and in values put something set to "count".

Then end up with a table like:

                 Sub OU1                  Sub OU 2              Sub OU 3
OU1                1                              7                             9
OU2
OU3
OU4

etc. but like all said not what was asked for!

Steve
omgangIT ManagerCommented:
Easiest is to run the script three times changing the strContainer value each time.  This will produce three output files; you can copy-paste the data from each into a single file.

It's also possible to modify the script to process multiple OUs but that will take some work.
OM Gang
Whitley Bowen JrNetwork Infrastructure Service SpecialistAuthor Commented:
Okay. Thank you OM Gang and Steve. Both of you helped.
omgangIT ManagerCommented:
@Steve Knight, it works well.  I like it.  I am not terribly familiar with pivot tables so thanks for the explanations.
OM Gang
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.