Export from AD to excel filter by group

[Oli M]

Hi Im fairly new to all this

I have put this script together from what I have found on the ent

I have searched for and tried to amend the script myself to include being
able to filter out accounts in a certain security group but to no avail.
Basically I have added all the admin use accounts to a group and want this
script to exclude any accounts in that group from the export results.

I have also searched on getting the last true login stamp and found a couple
of things but really couldn't find a way of adding it into this script.
the last login stamp isn't working as we have more than one dc, and it only
gets the information from the dc the computer you are logged into. How can i
get the information from all the dcs and then display the most recent result
for the last login stamp?

Any help, guidance or pointers would be much appreciated.

Here is the script as it is at the moment



Dim ObjWb
Dim ObjExcel
Dim x, zz
Set objRoot = GetObject("LDAP://RootDSE")
strDNC = objRoot.Get("DefaultNamingContext")
Set objDomain = GetObject("LDAP://" & strDNC) ' Bind to the top of the
Domain using LDAP using ROotDSE
Call ExcelSetup("Sheet1") ' Sub to make Excel Document
x = 1
Call enummembers(objDomain)
Sub enumMembers(objDomain)
On Error Resume Next
Dim Secondary(20) ' Variable to store the Array of 2ndary email alias's
For Each objMember In objDomain ' go through the collection
If ObjMember.Class = "user" then
x = x +1 ' counter used to increment the cells in Excel

objwb.Cells(x, 1).Value = objMember.Class
' I set AD properties to variables so if needed you could do Null
checks or add if/then's to this code
' this was done so the script could be modified easier.
SamAccountName = ObjMember.samAccountName
Cn = ObjMember.CN
Department = objMember.Department
LastLogin = objMember.LastLogin
PasswordLastChanged = objMember.PasswordLastChanged

' Write the values to Excel, using the X counter to increment the
rows.

objwb.Cells(x, 2).Value = SamAccountName
objwb.Cells(x, 3).Value = CN
objwb.Cells(x, 4).Value = Department
objwb.Cells(x, 5).Value = LastLogin
objwb.Cells(x, 6).Value = PasswordLastChanged

' Blank out Variables in case the next object doesn't have a value
for the property
SamAccountName = "-"
Cn = "-"
Department = "-"
LastLogin = "-"
PasswordLastChanged = "_"
End If

' If the AD enumeration runs into an OU object, call the Sub again
to itinerate

If objMember.Class = "organizationalUnit" or OBjMember.Class =
"container" Then
enumMembers (objMember)
End If
Next
End Sub
Sub ExcelSetup(shtName) ' This sub creates an Excel worksheet and adds
Column heads to the 1st row
Set ObjExcel = CreateObject("Excel.Application")
Set objwb = objExcel.Workbooks.Add
Set objwb = objExcel.ActiveWorkbook.Worksheets(shtName)
Objwb.Name = "Active Directory Users" ' name the sheet
objwb.Activate
ObjExcel.Visible = True
objwb.Cells(1, 2).Value = "SamAccountName"
objwb.Cells(1, 3).Value = "CN"
objwb.Cells(1, 4).Value = "Department"
objwb.Cells(1, 5).Value = "LastLogin"
objwb.Cells(1, 6).Value = "PassWordLastChanged"
'formatting for header
Set objRange = objExcel.Range("A1","Z1")
objRange.Interior.ColorIndex = 33
objRange.Font.Bold = True
objRange.Font.Underline = True
End Sub
'autofit the output
Set objRange = objwb.UsedRange
objRange.EntireColumn.Autofit()
ObjExcel.Save("ADoutput.xls")
MsgBox "Done" ' show that script is complete

[Oli M]

The last login part isn't that important.

If anyone knows how I can just filter the results so that it only shows
users that are not in this one specific group it would be most appreciated.

Thanks

[Richard Mueller [MVP]]

Post by Oli M
Hi Im fairly new to all this
I have put this script together from what I have found on the ent
I have searched for and tried to amend the script myself to include being
able to filter out accounts in a certain security group but to no avail.
Basically I have added all the admin use accounts to a group and want this
script to exclude any accounts in that group from the export results.
I have also searched on getting the last true login stamp and found a couple
of things but really couldn't find a way of adding it into this script.
the last login stamp isn't working as we have more than one dc, and it only
gets the information from the dc the computer you are logged into. How can i
get the information from all the dcs and then display the most recent result
for the last login stamp?
Any help, guidance or pointers would be much appreciated.
Here is the script as it is at the moment
Dim ObjWb
Dim ObjExcel
Dim x, zz
Set objRoot = GetObject("LDAP://RootDSE")
strDNC = objRoot.Get("DefaultNamingContext")
Set objDomain = GetObject("LDAP://" & strDNC) ' Bind to the top of the
Domain using LDAP using ROotDSE
Call ExcelSetup("Sheet1") ' Sub to make Excel Document
x = 1
Call enummembers(objDomain)
Sub enumMembers(objDomain)
On Error Resume Next
Dim Secondary(20) ' Variable to store the Array of 2ndary email alias's
For Each objMember In objDomain ' go through the collection
If ObjMember.Class = "user" then
x = x +1 ' counter used to increment the cells in Excel
objwb.Cells(x, 1).Value = objMember.Class
' I set AD properties to variables so if needed you could do Null
checks or add if/then's to this code
' this was done so the script could be modified easier.
SamAccountName = ObjMember.samAccountName
Cn = ObjMember.CN
Department = objMember.Department
LastLogin = objMember.LastLogin
PasswordLastChanged = objMember.PasswordLastChanged
' Write the values to Excel, using the X counter to increment the
rows.
objwb.Cells(x, 2).Value = SamAccountName
objwb.Cells(x, 3).Value = CN
objwb.Cells(x, 4).Value = Department
objwb.Cells(x, 5).Value = LastLogin
objwb.Cells(x, 6).Value = PasswordLastChanged
' Blank out Variables in case the next object doesn't have a value
for the property
SamAccountName = "-"
Cn = "-"
Department = "-"
LastLogin = "-"
PasswordLastChanged = "_"
End If
' If the AD enumeration runs into an OU object, call the Sub again
to itinerate
If objMember.Class = "organizationalUnit" or OBjMember.Class =
"container" Then
enumMembers (objMember)
End If
Next
End Sub
Sub ExcelSetup(shtName) ' This sub creates an Excel worksheet and adds
Column heads to the 1st row
Set ObjExcel = CreateObject("Excel.Application")
Set objwb = objExcel.Workbooks.Add
Set objwb = objExcel.ActiveWorkbook.Worksheets(shtName)
Objwb.Name = "Active Directory Users" ' name the sheet
objwb.Activate
ObjExcel.Visible = True
objwb.Cells(1, 2).Value = "SamAccountName"
objwb.Cells(1, 3).Value = "CN"
objwb.Cells(1, 4).Value = "Department"
objwb.Cells(1, 5).Value = "LastLogin"
objwb.Cells(1, 6).Value = "PassWordLastChanged"
'formatting for header
Set objRange = objExcel.Range("A1","Z1")
objRange.Interior.ColorIndex = 33
objRange.Font.Bold = True
objRange.Font.Underline = True
End Sub
'autofit the output
Set objRange = objwb.UsedRange
objRange.EntireColumn.Autofit()
ObjExcel.Save("ADoutput.xls")
MsgBox "Done" ' show that script is complete

Given the program you have so far, the most direct solution is to bind to
the specified group (using the Distinguished Name of the group) and use the
IsMember method of the group object to check membership. You pass the
AdsPath attribute of the user to the method to check if they are a member.
So, before you call Sub enumMembers, add the statements:

Dim objGroup
Set objGroup = GetObject("LDAP://cn=AdmGroup,ou=West,dc=MyDomain,dc=com")

This makes objGroup a global object reference, available in all Subs. Then
in Sub enumMembers modify this statement:

If ObjMember.Class = "user" then

as follows:

If (objMember.Class = "user") And (objGroup.IsMember(objMember.AdsPath)
= False) Then

Finally, if your domain is at Windows 2003 functional level (or above) you
can retrieve the lastLogonTimeStamp attribute. Although this is only updated
if the old value is more than 14 days (by default) in the past, it is
replicated. It is also Integer8, a 64-bit number that requires code to
convert into a date/time value. All values more than 14 days in the past are
accurate. Otherwise, as you have discovered, the lastLogon attribute is not
replicated, so you must query every Domain Controller in the domain to find
the largest (latest) value for each user. If needed, I have example VBScript
programs to retrieve lastLogon (or lastLogonTimeStamp) for all users and
convert to date/times in the local time zone linked here:

http://www.rlmueller.net/Last%20Logon.htm

Notice that these program use ADO to retrieve information in bulk from
Active Directory. Your program could also be converted to use ADO. This
would make it more efficient, but this effort may be more than you want to
tackle at this point. For more on using ADO in VBScript (including an
example), see this link:

http://www.rlmueller.net/ADOSearchTips.htm

[Oli M]

Richard

Thank you for that, that is brilliant.

I have incorporated the group filter part and it is working exactly how I
need.

I actually have already read your last login scipt recently, but am unsure
how I can incorporate that into my existing script.

I would like this current script to query all of the DCS compare the
restults for the lastlogin stamp and then record the latest one for each user
in the outputted excel results.

Sorry if I am asking a bit much and thank you for the help

[Oli M]

Or would it be easier to edit your last logon script to include the other
information that i require to be exported into an excel file with the filter
on user by group?

[Oli M]

I have looked into combining my script with your ADO last login version but
obviously the syntax is different. So I'm a bit stuck.

Any ideas or pointers please?

[Richard Mueller [MVP]]

Post by Oli M
I have looked into combining my script with your ADO last login version but
obviously the syntax is different. So I'm a bit stuck.
Any ideas or pointers please?

The best solution is to retrieve the lastLogonTimeStamp attribute, if your
domain is at Windows Server 2003 functional level. If your domain is not at
this functional level, it is difficult to add lastLogon to your script. The
best solution I can think of is as follows:
=======
Use the LastLogon.vbs script, but skip the last step that loops through all
users and displays the results. That is, remove the following:
=======
' Output latest lastLogon date for each user.
For Each strUser In objList.Keys
Wscript.Echo strUser & " ; " & objList.Item(strUser)
Next
=======
You now have the dictionary object objList with the last logon date/time for
all users, specified by the user Distinguished Names. Then, add your code
that enumerates all users, but outputs only members of the specified group.
In Sub enumMembers in place of the following:

LastLogin = objMember.LastLogin

instead use the following to retrieve the last logon date/time for the user
with the given Distinguished Name:

LastLogin = objList.Item(objMember.distinguishedName)

[Oli M]

Richard

I have been trying various things, but I need to to outut the true laslogin
of whcih you have explained how to do, but how do i get it to output the
results to excel with the last login, department, password last set, Display
name.

On your ado search page it explains that ado searching can only search for
certain fields.

[Oli M]

Richard I have tried doing what you have said but am getting an error
Variable is undefined Objrange

Can you show me how you think the whole script should now look so I can see
where I can have gone wrong.

Many Thanks

Post by Richard Mueller [MVP]

Post by Oli M
I have looked into combining my script with your ADO last login version but
obviously the syntax is different. So I'm a bit stuck.
Any ideas or pointers please?

The best solution is to retrieve the lastLogonTimeStamp attribute, if your
domain is at Windows Server 2003 functional level. If your domain is not at
this functional level, it is difficult to add lastLogon to your script. The
=======
Use the LastLogon.vbs script, but skip the last step that loops through all
=======
' Output latest lastLogon date for each user.
For Each strUser In objList.Keys
Wscript.Echo strUser & " ; " & objList.Item(strUser)
Next
=======
You now have the dictionary object objList with the last logon date/time for
all users, specified by the user Distinguished Names. Then, add your code
that enumerates all users, but outputs only members of the specified group.
LastLogin = objMember.LastLogin
instead use the following to retrieve the last logon date/time for the user
LastLogin = objList.Item(objMember.distinguishedName)
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

[Oli M]

Richard

It didn't work if I amended your script, but I amended mine to include yours
and it is now working perfectly.

Many Many Thanks for all of your help

Regards

Oli

Post by Richard Mueller [MVP]

Post by Oli M
I have looked into combining my script with your ADO last login version but
obviously the syntax is different. So I'm a bit stuck.
Any ideas or pointers please?

The best solution is to retrieve the lastLogonTimeStamp attribute, if your
domain is at Windows Server 2003 functional level. If your domain is not at
this functional level, it is difficult to add lastLogon to your script. The
=======
Use the LastLogon.vbs script, but skip the last step that loops through all
=======
' Output latest lastLogon date for each user.
For Each strUser In objList.Keys
Wscript.Echo strUser & " ; " & objList.Item(strUser)
Next
=======
You now have the dictionary object objList with the last logon date/time for
all users, specified by the user Distinguished Names. Then, add your code
that enumerates all users, but outputs only members of the specified group.
LastLogin = objMember.LastLogin
instead use the following to retrieve the last logon date/time for the user
LastLogin = objList.Item(objMember.distinguishedName)
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

[g***@yahoo.com]

You want somthing more like this:

Set ObjExcel = CreateObject("Excel.Application")
Set objwb = objExcel.Workbooks.Add
objwb.Visible = True
Set objws = objwb.Worksheets(shtName)
Objws.Name = "Active Directory Users" ' name the sheet
objws.Activate
objws.Cells(1, 2).Value = "SamAccountName"
objws.Cells(1, 3).Value = "CN"
objws.Cells(1, 4).Value = "Department"
objws.Cells(1, 5).Value = "LastLogin"
objws.Cells(1, 6).Value = "PassWordLastChanged"
'formatting for header
Set objRange = objws.Range("A1","Z1")