[jnetter]

Hi everyone, I am not script kitty so I am hoping someone else has gone down this route and has a solution.

Currently we have transitioned over all Tier 1 tasks to our Call Center (adding users / dealing with lost devices / basic handheld troubleshooting), but I want to automate the removal of terminated users as well.

Here's my high level view of what I would like to see happen.

• Script will query BESMgmt database for user name
• Script will save this list of users in temp file
• Script will query a specific OU in AD (ie.. our Terminations OU)
• Script will use previously created temp file from BES and look for any matches (formatting may be an issue here)
• Temp file or function will list any users who match
• CSV file will be created with any output
• File will be sent automatically to Call Center, they will create ticket and remove any users

Am I asking for too much here? In theory its a simple lookup / compare / output script, but I am not a script master.

Any help would be greatly appreciated. (as always)

Jnetter

[hdawg]

Even better ... use the BESUserAdminService / Client to remove the user so the call center doesn't need to.

[jnetter]

Now that's thinking! But... how do we accomplish the first few objectives? Can anyone assist?

[rsk]

I think the correct term is "script kiddie", although a cat that can script would be cool too.

I did somthing similar to this for creating distribution groups in AD of BES users. Basically just pull the MailboxDN from the UserConfig table of the BESMgmt database and that is your legacyExchangeDN in AD. Dump the container in AD and see if you can match any of the users.

you could even use 'osql' and 'csvde' to get the data you want from SQL and AD, then with a simple batch file you would have 90% of what you want.

you aren't asking too much, it is a pretty simple task. problem is these days nobody bothers to teach their cats how to script at an early age.

I can probably get one of my scripting cats to help you.

[jnetter]

That would be great... I am sorry for the feline comparison.

[rsk]

ok, here is the first part which will dump some of the data we need from SQL into a text file. this is done using the osql command line utility.

I would create a seperate directory for this stuff, something like c:\tools\bes. and you probably want to do this from the BES server, or at least a windows server (for csvde). osql is part of the SQL tools so you will have to have the SQL management studio installed (the appropriate version for whatever SQL you are running)

ok, so put these commands below into a batch file called getSQL.bat, change the <SQLserver> to be whatever your SQL server is called. The command uses trusted auth (the -E) so you will have to be logged in (or runas..) an account with perms to SQL.

osql -E -S "<SQLserver>" -Q "Select MailboxDN from BESMgmt.dbo.UserConfig WHERE (DeviceType <> 0)" | find "/o" > sqldata.txt

we end up with sqldata.txt which is a dump of all the legacyExchangeDN values for your BES users.

next we'll dump the data out of AD using csvde.

[jnetter]

Phenomenal!!!! Thanks for working on this!

[jnetter]

For the people who use SQL authentication user this

osql -S SQLSERVERNAME -U BESMgmt -P SQLPASSWORD -Q "Select MailboxDN from BESMgmt.dbo.UserConfig WHERE (DeviceType <> 0)" | find "/o" > c:\sqldata.txt

[rsk]

maybe we should have just called the batch file getBES.bat or something less specific to SQL, since we are going to add more to it. This is how these things evolve, one command at a time, one piece of data at a time. eventually we will have everything we need in a hopefully meaningful format.

so, lets get the data out of AD. for that we will need to know the container DN we want to export from (the terminations OU). to get this you can use ADSIedit to navigate to the container, get the properties and look for the distinguishedName attribute. copy and paste into the -d option in the command below:

csvde -f ADdata.csv -l DN,legacyExchangeDN,displayName -r "(&(objectClass=user)(legacyExchangeDN=*)(userAccountControl:1.2.840.113556.1.4.803:=2))" -d "<containerDN>"

The LDAP query: (&(objectClass=user)(legacyExchangeDN=*)(userAccou ntControl:1.2.840.113556.1.4.803:=2)) will only return disabled user objects with a mailbox, I assume you disable the accounts once they are placed in the term OU ?

[jnetter]

Now here is an issue for me, for whatever reason Exchange is appending GUI'ds to some accounts (see below)

/o=STG/ou=First Administrative Group/cn=Recipients/cn=bishelley01440364
/o=STG/ou=First Administrative Group/cn=Recipients/cn=bitemple66116589

Any idea of how to get rid of this? is there a differnt way to lookup the account?

Sorry, but these cannot be changed as they were migrations from acquisitions (and there are many of them)

[jnetter]

Excellent, I can get the LDAP path for the Terminations OU. You are correct, we disable the accounts.

[rsk]

Originally Posted by jnetter

Now here is an issue for me, for whatever reason Exchange is appending GUI'ds to some accounts (see below)

/o=STG/ou=First Administrative Group/cn=Recipients/cn=bishelley01440364
/o=STG/ou=First Administrative Group/cn=Recipients/cn=bitemple66116589

Any idea of how to get rid of this? is there a differnt way to lookup the account?

Sorry, but these cannot be changed as they were migrations from acquisitions (and there are many of them)

this should not cause any problems because AD will contain the same data (in legacyExchangeDN)

[scott_perry]

Building on what rsk has posted, you can completely automate this task by using Vbscript and Scheduled Tasks. I have posted the AD lookup code below. If you want the rest, I can compile it all and post later.

1. Run rsk's batch file to get your list of users

PHP Code:

set WSHShell = CreateObject("Wscript.Shell")
'' 4 is for normalnofocus and true is for the script to wait for shell process to finish - very important
WSHShell.Run "batch file name", 4, true 


2. Read in the file created by the batch job and loop through it line-by-line, storing the line in sMailboxDN
3. Connect to the termination OU using ADODB.Connection

PHP Code:

' 'sEmailtype = "smtp" or "X500" - check your AD account
 sEmail = sEmailType & ":" & sMailBoxDN
 sFilter = "(&(objectCategory=user)(proxyAddresses=" & sEmail & "))"
 set objConn = CreateObject("ADODB.Connection")
 objConn.Provider = "ADsDSOObject"
 objConn.Open
 set objRS = objConn.Execute("<LDAP://OU=YourOU,dc=YOUR_Domain_Name,dc=COM>;" & sFilter & ";distinguishedName;Subtree") 


4. Write to CSV if found

PHP Code:

 objRs.MoveFirst
 if objRS.Fields(0).Value <> "" then
   'user was found in the terminated OU
   'Write to CSV
 else
   ' do nothing but go on to the next user
end if 


5. Send the Call Center an email using SMTP

Next, set up a scheduled task on the server containing the SQL database to run the VBscript.

Disclaimer: This was thrown together quickly and I am sure there may be room for improvement and I make no guarantees. Before running anything I posted, please test thoroughly.

[jnetter]

Excellent, the AD script works like a charm.
How do we compare the 2? will you need to see the sample of the output data from each?

[scott_perry]

Originally Posted by jnetter

Excellent, the AD script works like a charm.
How do we compare the 2? will you need to see the sample of the output data from each?

compare what two?

[jnetter]

Sorry, ok so I have put your code together into a VB file and edited it to the best of my ability, I keep getting this error

Line: 18
Char: 8
Errpr: Expected "While", "Until" or end of statement
Code: 800A0404
Source: Microsoft VBScript compliation error

I think its erroring on the following line

do nothing but go on to the next user

Also, where does it write the CSV file, what part of the code does the create file?

[scott_perry]

Originally Posted by jnetter

Sorry, ok so I have put your code together into a VB file and edited it to the best of my ability, I keep getting this error

Line: 18
Char: 8
Errpr: Expected "While", "Until" or end of statement
Code: 800A0404
Source: Microsoft VBScript compliation error

I think its erroring on the following line


Also, where does it write the CSV file, what part of the code does the create file?

That part isn't really code, just notes. I'll compile this together and post the whole thing tomorrow.

[jnetter]

Thank you so much, and by reading your code there is no actual changes to either the BES database or AD right, this is a simple query and write to file?

[rsk]

now that we have our two data files (sqldata.txt containg BES data and ADdata.csv with AD data) we need to do some comparing.

Since we only have AD data about the terminated employees if we find a match from the sqldata.txt file we know the termed emp had a BB, and I think this was your end goal to produce a list of termd emps with BB's.

so, this is a bit of a hack but that is because we ended up with a space at the start of the data in sqldata.txt. so we end up having to call another batch file to do the searching.

create a new batch file called checkusr.bat and put this in it:

@echo off
find /i "%*" addata.csv | find /i "="

then back to the getBES.bat, it will need to have this added:

del BESterm.csv /q
for /F "skip=2 tokens=1* delims=*" %%i in (sqldata.txt) do call checkusr%%i >>BESterm.csv

ok, so when this is done you have a csv file called BESterm.csv containg the DN,legacyExchangeDN and displayName of termed employees who have a BB. just use Blat to send it to whoever has to clean it up.

the next step would be to automate the removal of the accounts and just send a report of what you did.

rich

[jnetter]

rsk, are you working off of scott_perry's script or is this still your own solution? I like both, just want to make sure I am not confusing the both.