Wednesday, September 13, 2006

Reporting on the number of Users, Contacts and Groups in a OU and sub OU’s

Somebody asked how to do this last week and I’ve had a few other question’s lately on reporting based on Sub Ou’s so I thought I’d put together a sample to show one method of doing this. The method I’ve used is to create a data-shape of all the objects with a Ou and its Sub Ou’s and then relate this information to an enumeration of the OU tree.

What this script does is takes the name of the OU where you want to start the query at as a command-line parameter. It then does an ADSI query to find the distinguished name of the first OU that matches the text entered. It then does 3 separate subtree queries of all the Users, contacts and group objects that are located under the root OU. To group the information that is returned this is where the ADO datashaping provider is used. The information is then stored temporary in a multi dimensional array and then used to finally build a HTM report called “c:\temp\report.htm” which has a table that shows the OU Name, Description, Path and the number of users, groups and contacts within each OU.

The script takes one command-line parameter which is the name of the OU you want to run it against eg to run the script “cscript.exe lobjectsv2.vbs OUname”. I’ve also created a provision to let the script be run against the entire Active Directory domain instead of just and OU branch to do this instead of using the name of the OU as a command-line parameter use rootdse as the command-line parameter eg “cscript lobjectsv2.vbs rootdse”

I’ve put a downloadable copy of the code here the script looks like


queryou = wscript.arguments(0)
report = "<table border=""1"" width=""100%"">" & vbcrlf
report = report & " <tr>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">OU
Name</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">OU
Description</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">OU
Path</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#
Users</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#
Contacts</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#
Groups</font></b></td>" & vbcrlf
report = report & "</tr>" & vbcrlf
set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
ouQuery = "<LDAP://" & ou & strNameingContext & ">;(&(objectCategory=organizationalUnit)(ou="
& queryou & "));description,name,distinguishedName;subtree"
Com.ActiveConnection = Conn
Com.CommandText = ouQuery
Set ouRs = Com.Execute
if ouRs.recordcount = 0 and queryou <> "rootdse" Then
wscript.echo "Cant find Ou"
Else
If queryou = "rootdse" Then
ou = strNameingContext
Else
ou = ouRs.fields("distinguishedName")
End if
Wscript.echo "Users under " & ou
wscript.echo
uarray = ReportonOU("user")
Wscript.echo
Wscript.echo "Contacts under " & ou
wscript.echo
carray = ReportonOU("contact")
garray = ReportonOU("group")

For uic = LBound(uarray,3) To UBound(uarray,4)
report = report & "<tr>" & vbcrlf
report = report & "<td align=""center"">" & uarray(1,1,1,uic) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" & uarray(1,1,0,uic) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" & uarray(1,0,0,uic) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" & uarray(0,0,0,uic) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" & carray(0,0,0,uic) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" & garray(0,0,0,uic) & "&nbsp;</td>" &
vbcrlf
report = report & "</tr>" & vbcrlf
Next
report = report & "</table>" & vbcrlf
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\report.htm",2,true)
wfile.write report
wfile.close
set wfile = nothing
set fso = nothing
End If


function ReportonOU(objecttype)

set conn1 = createobject("ADODB.Connection")
strConnString = "Data Provider=NONE; Provider=MSDataShape"
conn1.Open strConnString
set objParentRS = createobject("adodb.recordset")
set objChildRS = createobject("adodb.recordset")
strSQL = "SHAPE APPEND" & _
" NEW adVarChar(255) AS SOADDisplayName, " & _
" NEW adVarChar(255) AS SOADDescription, " & _
" NEW adVarChar(255) AS SOADDN, " & _
" ((SHAPE APPEND " & _
" NEW adVarChar(255) AS MOADDisplayName, " & _
" NEW adVarChar(255) AS MOADDN " & ")" & _
" RELATE SOADDN TO MOADDN) AS rsSOMO "
objParentRS.LockType = 3
objParentRS.Open strSQL, conn1

adsiQuery = "<LDAP://" & ou &
">;(objectCategory=organizationalUnit);description,name,distinguishedName;subtree"
Com.ActiveConnection = Conn
Com.CommandText = adsiQuery
Set Rs = Com.Execute
While Not Rs.EOF
objParentRS.addnew
objParentRS("SOADDisplayName") = rs.fields("name")
If Not IsNull(rs.fields("description").value) then
For Each val In rs.fields("description").value
objParentRS("SOADDescription") = val
Next
Else
objParentRS("SOADDescription") = " "
End if
objParentRS("SOADDN") = LCase(rs.fields("distinguishedName"))
objParentRS.update
rs.movenext
Wend

If objecttype = "group" then
objQuery = "<LDAP://" & ou &
">;(&(mailnickname=*)(|(objectCategory=group)));description,name,cn,distinguishedName;subtree"
Else
objQuery = "<LDAP://" & ou &
">;(&(mailnickname=*)(|(&(objectCategory=person)(objectClass=" & objecttype &
"))));description,name,cn,distinguishedName;subtree"
End if
Com.ActiveConnection = Conn
Com.CommandText = objQuery
Set Rs1 = Com.Execute
Set objChildRS = objParentRS("rsSOMO").Value
While Not Rs1.EOF
objChildRS.addnew
objChildRS("MOADDisplayName") = rs1.fields("name")
objChildRS("MOADDN") = Replace(LCase(rs1.fields("distinguishedName")),"cn=" &
LCase(rs1.fields("cn")) & ",","")
objChildRS.update
rs1.movenext
Wend
anum = cInt(objParentRS.recordcount)
reDim objcntarray(1,1,1,anum)
i = 0
objParentRS.MoveFirst
Do While Not objParentRS.EOF
Set objChildRS = objParentRS("rsSOMO").Value
wscript.echo objParentRS("SOADDisplayName") & " - " &
objParentRS("SOADDescription") & " : "& objChildRS.recordCount
objcntarray(1,1,1,i) = objParentRS("SOADDisplayName")
objcntarray(1,1,0,i) = objParentRS("SOADDescription")
objcntarray(1,0,0,i) = objParentRS("SOADDN")
objcntarray(0,0,0,i) = objChildRS.recordCount
objParentRS.movenext
i = i + 1
Loop
ReportonOU = objcntarray
objParentRS.close
objChildRS.close
rs.close
rs1.close
Set objParentRS = Nothing
Set objChildRS = Nothing
set conn1 = Nothing
Set Rs1 = Nothing
Set rs = Nothing

End function

3 comments:

Tapio said...

I know this is pretty old but this really would suit my needs but it only reports zero users in my domain although I have users there (reports OUs correct).

Tapio said...

I know this is pretty old but this really would suit my needs but it only reports zero users in my domain although I have users there (reports OUs correct).

nuno said...

Same thing here

I would love to see it in action.

Anyone has a clue why obj count report is null?!?