Active Directory Example Scripts

[ModemJunki]

Hi Water! I have returned from holiday. It was nice to do things that don't involve using a computer for a while.

Shall we continue? How can I help to debug this issue?

[water]

Can you please tell me what you enter in the selection screen? Maybe I can reproduce the problem here.

[ModemJunki]

Sure.

For the OU, I input like so:

xx000.company.net/OU/SubOU/SubSubOU/SubSubSubOU/SubSubSubSubOU/SubSubSubSubSubOU

Then I click the radio-button next to Groupname, and I input a wildcard search, like so: GroupName-ForMyOU*

Then I click the radio button for "Query Groups", and I click Process. Eventually, the script hangs as described. Now, it can be seen in the output from SciTE some errors:

ExcelCOM_UDF.au3(1292,132) : WARNING: $oFound possibly not declared/created yet
$oFound = $oExcel.Selection.Find($sFindWhat, $oExcel.ActiveCell, Default, $iWholeOrPart, Default, Default, $fMatchCase, Default)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
ExcelCOM_UDF.au3(1273,38) : WARNING: $sFound: declared, but not used in func.
Local $iCount, $sA1, $sR1C1, $sFound,
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
ExcelCOM_UDF.au3(2682,34) : WARNING: $iCurrCol possibly not declared/created yet
     $iCurrCol = $iStartColumn

Sorry for the sub-sub-sub things - my company is restrictive about what I can post in public.

[water]

The format of the OU seems to be wrong, has to be something like OU=xx,DC=company,DC=COM.

As you can see from the messages, this are just warnings. The script should run successfully anyway.

Edited October 30, 2012 by water

[ModemJunki]

OK, I reformatted the OU string. Now it hangs at record 380 of 381 (so the result is smaller).

But for the moment I must go home. Perhaps there will be time to look at this again tomorrow?

[water]

Sure. We then will have a look at the other settings.

[ModemJunki]

Hi water, not sure what you mean by other settings?

[water]

What is the last message you get displayed in the SplashText?

"Processing ..."

"Processing ... Get records from AD"

"Processing ... AD records"

"Processing ... Copy to Excel"

"Processing ... Format Excel Sheet"

"Processing ... Transpose array"

"Processing ... Excel row (n of m)"

[ModemJunki]

"Processing .. AD records (380 of 381)" - that is where it appears to hang.

[water]

You said you had a very big AD structure.

Can you please uncomment (put a ";" at the start of) the following line? This will not create the description for the AD groups but might be much faster:

; Get all AD groups and their description
$aDescription = _AD_GetObjectsInOU("", "(&(objectclass=group)(description=*))", 2, "sAMAccountName,description")

[ModemJunki]

That was it, in only a few moments I have the excel-sheet onscreen. So I could try compiling this and running it on a server or one of the workstations that never gets shut down - we could see if it succeeds eventually.

That would have to wait until tomorrow - tonight I will be rebooting many workstations. ;-)

I do see a "COM Error Encountered in !!ADAudit.au3" - it is repeated many times.

========================================================

COM Error Encountered in !!ADAudit.au3

AD UDF version = 1.3.0

@AutoItVersion = 3.3.8.1

@AutoItX64 = 0

@Compiled = 0

@OSArch = X64

@OSVersion = WIN_7

Scriptline = 245

NumberHex = 80020009

Number = -2147352567

WinDescription =

Description =

Source =

HelpFile =

HelpContext = 0

LastDllError = 0

[water]

Line 245? That is "; Insert sum into last row" from the ADAudit version you can download from the forum. What is your line 245?

[ModemJunki]

Ha! In earlier attempts to fix this, I made some other changes. So I have gone back to the original download and made the only two needed changes for it to work in my environment (below).

So now we run with no errors.

I will try to leave it running for a great amount of time - perhaps even make a crude log function to put some timestamps into a textfile to record start/stop - in the next days.

Thanks for all your help and patience, water!

1. Change

If $oExcel.Application.Version <= 12.0 And $aUsers[0][0] >= 254 Then

to

If $oExcel.Application.Version < 12.0 And $aUsers[0][0] >= 254 Then

2. Comment out the $aDescription = ... line

; Get all AD groups and their description
$aDescription = _AD_GetObjectsInOU("", "(&(objectclass=group)(description=*))", 2, "sAMAccountName,description")

[water]

Glad to be of service

If you want to get the description of the AD groups in your Excel sheet you could pass the OU you entered into the GUI as parameter 1 to function _AD_GetObjectsInOU.

This would search only in the specified OU and not the whole AD.

[ModemJunki]

Where is description supposed to be populated? I'm not sure this is working.

I changed line 164 from:

$aGroups = _AD_GetObjectsInOU("", $sQuery, 2, "member,distinguishedname")

to:

$aGroups = _AD_GetObjectsInOU($sOU, $sQuery, 2, "member,distinguishedname")

And removed the comment from line 212 ($aDescription = ...)

[water]

In the ExcelSheet every group with a description as a red triangle on the upper right corner of the cell.. Move the mouse over the cell and the description will be displayed.

[ModemJunki]

Description is not working with the changes I made above. :-(

Edit: change has to be made to the line 212 and it works!

$aDescription = _AD_GetObjectsInOU($sOU, "(&(objectclass=group)(description=*))", 2, "sAMAccountName,description")

Also - possible to have the AD groups as the top columns (for easy filtering of the users by group)?

Edited October 31, 2012 by ModemJunki

[water]

Also - possible to have the AD groups as the top columns (for easy filtering of the users by group)?

Unfortunately not.

[ModemJunki]

Ja! Rotate is an easy manual task, just a few short steps.

Just copy all, go to new sheet, "paste special", and transpose. After doing this the first blank row (nr. 2) has to be deleted.

Thanks again for all the good work and support, water.

http://office.microsoft.com/en-us/excel-help/rotate-data-by-converting-columns-to-rows-or-vice-versa-HP005203138.aspx

Edited November 9, 2012 by ModemJunki

[water]

Glad you've found a solution