Automating the World

 
smpena81
Topic Author
Posts: 2
Joined: Wed Feb 08, 2017 3:20 pm
Current LT Agent Count: 500+

Active Directory User/Attribute Query

Fri Jun 16, 2017 9:40 am

Does anyone know if its possible to gather a list of all "Active" domain user accounts via labtech? We are looking to provide monthly reports to clients of all their active (enabled) accounts on their network. A lot of services they pay for are per user. Also, a lot of times we find old service accounts or temp accounts that get created and never disabled or removed. Does anyone have any ideas on how such a query could be set up and executed via labtech? I asked support and they just seem to point me here all the time. Any help would be appreciated.
 
Gavsto
Geek
Posts: 67
Joined: Tue Dec 08, 2015 7:35 am

Re: Active Directory User/Attribute Query

Fri Jun 16, 2017 10:13 am

You are likely already using the Active Directory plugin. In which case:

SELECT plugin_ad_users.*, plugin_ad_entries.domainguid, plugin_ad_domains.domainname, plugin_ad_domains.lastupdate, computers.clientid, computers.locationid FROM plugin_ad_users
INNER JOIN plugin_ad_entries ON plugin_ad_users.objectguid=plugin_ad_entries.objectguid
INNER JOIN plugin_ad_domains ON plugin_ad_entries.DomainGUID=plugin_ad_domains.objectguid
INNER JOIN computers ON plugin_ad_domains.infrastructureserverid = computers.computerid


I personally use a view that I then reference in a report:

CREATE
    /*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]*/
    VIEW `labtech`.`v_adusersfulldetail`
    AS
(SELECT plugin_ad_users.*, plugin_ad_entries.domainguid, plugin_ad_domains.domainname, plugin_ad_domains.lastupdate, computers.clientid, computers.locationid FROM plugin_ad_users
INNER JOIN plugin_ad_entries ON plugin_ad_users.objectguid=plugin_ad_entries.objectguid
INNER JOIN plugin_ad_domains ON plugin_ad_entries.DomainGUID=plugin_ad_domains.objectguid
INNER JOIN computers ON plugin_ad_domains.infrastructureserverid = computers.computerid);


That looks like this:

Image
 
smpena81
Topic Author
Posts: 2
Joined: Wed Feb 08, 2017 3:20 pm
Current LT Agent Count: 500+

Re: Active Directory User/Attribute Query

Fri Jun 16, 2017 2:05 pm

Thank you for your quick response. How were you able to enumerate the account status information? We ran your query and we are still unable to see the accounts status. The report you were able to create looked perfect for what we are trying to achieve. Do you have any template or guidance/notes that you can offer to help us create a similar report?
 
Gavsto
Geek
Posts: 67
Joined: Tue Dec 08, 2015 7:35 am

Re: Active Directory User/Attribute Query

Fri Jun 16, 2017 3:18 pm

That's the useraccountcontrol - it's not plain text - http://jackstromberg.com/2013/01/userac ... ag-values/ you have to infer from that the account status which is standard active directory practice.

I don't have a template but you'd start from a client report template in the new report center, add the view in as a table and attach a relationship to the client table. Do a detail band in the report which enumerates then a lot of calculated fields and display logic.