[rsk]

Originally Posted by scott_perry

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") 


some good stuff here scott, the mailboxDN from SQL matches the legacyExchangeDN in AD so it is best to search for it directly. more like this should work:

PHP Code:

 sFilter = "(&(objectCategory=user)(legacyExchangeDN=" & sMailBoxDN & "))"
 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") 


rich

[rsk]

Originally Posted by 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.

no, mine is self contained.

[jnetter]

ok rsk, what order do I run your batch files? I am a bit confused where the BESterm.csv comes from and I don't want to start mucking around with production.

Can you give me the complete overhaul of how it should work (sequence of batch files)? Should we go down Scott's route so we have a VB version of it as well.

I have always been good with "ideas", and I can read scripting alrgiht, but I can't write it for the life of me.

I appreciate both of your assistance.

[rsk]

in summary here is both batch files that you need to get a list of termed employees with BB's. all you do is call getBES.bat and it produces the output for you.

first is getBES.bat:

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

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

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

::check to see if we have any output and send it if we do.
dir besterm.csv | find /c /i " 0 BESterm.csv"
if NOT "%ERRORLEVEL%" == "0" call blat BESterm.csv -server <smtpServer> -f <from@comapny.com> -s "BES Terminated employee report" -t "<t1support@company.com>" -attacht besterm.csv -body "See attached csv file for terminated employees with BES accounts read for cleanup."

and the second batch file is called checkusr.bat and gets called from getBES.bat:

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

what you end up with is a CSV file of terminated employees with BB devices, the file (BESterm.csv) contains DN, legacyExchangeDN and displayName

you could add a blat command to email it (lmk if you need help with that) and then it can be setup as a scheduled task to run once a day, or once a week.

rich

ps. either method will work, I am lazy so I prefer to write as few lines of code as possible. if I could have done it in VBscript in less than 5 lines I probably would

[jnetter]

Ok, one last question (this is wicked), how does it know to look in the C:\ for sqldata.txt

Currently I have no users so my BESterm.csv is blank, it should be blank if there are no termed users correct?

BLAT, sure if you could paste the command that would be great, I have used SMTPsend.exe before as well.

[jnetter]

So it looks like we have to change where it writes the sqldata.txt so it writes it to the local folder.

Also, is there anyway to make this so it show's the field (Lastname, Firstname, Username)? in the output..

This is still wicked

[rsk]

all the commands called assume the current working directory, this is why I suggested it be put into its own directory ('c:\tools\bes' for example), to keep everything in one place.

yes, if you have no termed employees with BB's it shoud be blank. check to make sure you have some data in sqldata.txt and addata.txt..

you can add extra attributes to the csvde command if you want them to appear in the output file. one thing I hate about csvde is that it won't guarantee the order of fields in the output file, run it once and they will be in one order, run the same command again and you may get the output fields in a different order. the more fields you add the more noticable this will become.

the -l option controls which attributes are written out, so you would want something like this:

-l DN,legacyExchangeDN,displayName,givenName,sn,sAMAccountName

[jnetter]

for whatever reason it only works (or completes) if I copy the sqldata.txt to the working folder, not in its native c:\ path. How do we make it write to the same folder the batch file resides in (and reference it from there).

[rsk]

big sorry there jnetter, made a typo on the first line..

should be like this at the end of the osql command, just take out the refernce to "c:\":

 find "/o" > sqldata.txt

not like this:

 find "/o" > c:\sqldata.txt

[scott_perry]

rsk - thanks for the tip on the legacyExchangeDn, much cleaner.

jnetter - posted below is a VBscript (you will need to change the extension to ".vbs" that will run the batch file from rsk, read the output, check your specified OU, write the results to a CSV and email a link to your specified recipient via SMTP email. You will need to change the values at the top of the script to match your information. You will also need to change the wording in the Email and Output file to match what you are looking for.

To answer your earlier question, this script is only doing a read of AD - although you can modify it with a couple lines and it would do changes in AD if you have the appropriate permissions.

If you need additional help, just PM me. Otherwise, it is enough of a start for you to talk to someone in your company to tweak. I tried to document as much as possible in the code, but I may have missed something. My disclaimer is that you need to thoroughly test this script as I make no guarantees.

Good Luck

[hayabusa]

How about a script that will purge a users account if the pending mail count gets to a certain level or the last contact time reaches a threshold. I have been trying to find a way for a script to run on a monthly basis that looks at the last contact time and the pending count and if both reach a certain threshold it purges the email and or deletes the user from the BES server if it reaches another threshold.

[scott_perry]

Originally Posted by hayabusa

How about a script that will purge a users account if the pending mail count gets to a certain level or the last contact time reaches a threshold. I have been trying to find a way for a script to run on a monthly basis that looks at the last contact time and the pending count and if both reach a certain threshold it purges the email and or deletes the user from the BES server if it reaches another threshold.

I think you can use a combination of VBScript and the Blackberry Resource Kit to do this. I haven't had time to try it, but that is my understanding.

[hayabusa]

I know that the purge and delete user can be taken care of using the BRK but I'm not sure how I would go about creating the sql/vb script to poll the numbers from database and then exacute the actions from the BRK if the threshold is met

[rsk]

hayabusa, how about starting a new thread for this new problem ? I am sure there are enough people here who can help you build this.. it is a good idea.

[jnetter]

Thanks to both RSK and SCOTT_PERRY, this has been a great value add to my organization!!!!!!! If you were in Etown I would be buying you both a beer.

[jnetter]

Dammit, now I am confused. For some reason this isn't working now. Before I embedded everything into the VB file it was good, now its not finding any users as terminated (even though there are some), it looks as though its working but its not comparing properly.

Is anyone able to test this and let me know if I am losing it or?

[jnetter]

It looks as thought the issue is with the compare file, I can gurantee that the termed user is in both locations and that the script sees them, but for some reason they are not being compared or its not making the connection....

Almost there, just this last little peace and what a cool little script.

[scott_perry]

jnetter, I just got around to looking at the output that comes from the osql command. It appears that the output is coming with an extra space at the end. You will need to modify the value returned by the readline command with the Trim function to remove the extra space. That should fix your comparing problem.

So, this line
sEmail = objBES.Readline

should be
sEmail = trim(objBES.Readline)

[jnetter]

The trim fixed the issue! Thanks.

[jnetter]

Man, I must be such a pain in your backside.
Is there a way to only show the terminated accounts (get rid of not found in OU) or say "no terminated users" and have that appended to the body of the email (get rid of the output file all together)?

Our ticketing system is automated via email, so the email sent will become the ticket and assigned to the proper group, if I can get the email with all the information required that would be stellar.

If not I completely understand, thanks again for your assistance with this.