Any SQL/database programmers?

[CmdrFly]

Another oddball question.

I have a database designed in Access that I use to manage our phone system. Both our phone and voicemail servers run SQL, so I have about a dozen queries I run that collect various chunks of data which is automatically sorted into custom reports.

The problem: my voicemail server runs Exchange, and thus is a domain controller (Exchange must be run on a domain). This voicemail domain is completely independant of our corporate domain. When we delete users from the voicemail server, it only deletes their license - the AD username and the user's mailbox folder remain on the server. This presents wasted space and a possible security issue since the user accounts are not disabled, and can still be used to log in to the server.

I want to run a report that will list all these defunct accounts so I can delete them manually.

So, my next add-on to this database needs to do a few things:

1. Get a list of all AD user accounts (I can do this by scripted command line)
2. Import this list into Access as a new table (possibly using Excel as a mediary?)
3. Compare this table of all usernames to a table of active subscribers (which I have)

So my question to the community: is there a way to run an SQL query on an AD user group and get a listing of all usernames within that group? If this is possible, I can eliminate the first 2 steps of the process above and shave 5-10 minutes off the entire process (which eventually will be used in about a dozen offices).

Thanks in advance.

 

[Hmkiroko]

Yup should be easy to do.

If you have a table in your Access DB, you can do an outer join comparing your Voicemail users to your Active Directory users (displaying all the ones in the AD table that are not found (your criteria in Access might be ="" or null)).

You should get a list of all the usernames that do not have a subscription on your voice-mail, and then you can start deleting out of active directory.

 

[CmdrFly]

Buh? I can't pull the data from AD right now via SQL, that's my roadblock.

Currently, I have to:

1. manually get a listing of the AD users
2. manually import to Excel spreadsheet
3. manually import spreadsheet to Access table
4. then compare.

I want to run a query that polls the AD server for the usergroup listing, compares that, and does it all in one step.

I know how to do the compare, I just don't know how to have Access pull the usergroup contents.

 

[Hmkiroko]

There isn't a database and respective table that stores AD information? Make a new OBDC connection to whatever that table is, hope to hell you have Write Access, and just link to the table within the Access DB.

If you have done that, the rest should be cake.

Make a select command getting all the records you want, and then you should be able to change it to a delete query.

OR in SQL View, you can type

delete from AD_table

where
(criteria)

 

[CmdrFly]

As far as I know, AD does not run on SQL. I'm not up to par on it's internal structure, only on it's design and workings from an external point of view. I also have full read/write to all resources on the server, and if I don't then I would be the one to escalate my sec level anyways.

My voicemail application does run on SQL, so there is the SQL service running on the system. However, it populates about 600 tables and from the ones I bombed through I could not find a complete listing of all AD users - only listings of users that applied to the voicemail program.

 

[Hmkiroko]

There has to be a way to get that information, possibly through an API call?

I'd talk to someone who is really experienced with AD and they would likely tell you how to access this information.

 

[CmdrFly]

Turns out Hyena is the app I'm looking for. 3rd-party app that scours domains and dumps to .csv's. I can import those and filter out the unnecessary crap, so this is all good and done. Thanks for the input Hmk.