Automating the World

We have migrated servers, if you encounter any issues, please let us know on Slack, or post it.


We have also disabled registration as we migrate to the new forum systems. The slack (slack.labtechgeek.com) will always be accepting new users.
 
timbo83251
Geek
Topic Author
Posts: 77
Joined: Wed Apr 02, 2014 4:19 pm
Current LT Agent Count: 1500+

Active Directory User List

Wed Oct 05, 2016 12:23 pm

Hello,

Just curious if anyone has created a dataview or report (in the new Report Module) that utilizes the data gathered via the Official AD Plugin. I don't see an "Export to CSV" option available in the plugin itself, but I'm sure everything I need would be in the tables.

Since we are user based billing, our Account Managers like to go to our clients with this user list and true-up. I realize this will be automated via CW User Centric but we aren't quite there yet.

thanks in advance for any ideas..
 
User avatar
Gavsto
Geek
Posts: 105
Joined: Tue Dec 08, 2015 7:35 am

Re: Active Directory User List

Wed Oct 05, 2016 3:57 pm

Yes - I have a report for this. This is the SQL query I am running.

SELECT `plugin_ad_users`.`ObjectGUID` AS `ObjectGUID`, `plugin_ad_users`.`ObjectSid` AS `ObjectSid`, `plugin_ad_users`.`ContactID` AS `ContactID`, `plugin_ad_users`.`AccountName` AS `AccountName`, `plugin_ad_users`.`AccountControls` AS `AccountControls`, `plugin_ad_users`.`AdminCount` AS `AdminCount`, `plugin_ad_users`.`LogonCount` AS `LogonCount`, `plugin_ad_users`.`LastLogon` AS `LastLogon`, `plugin_ad_users`.`LastLogonTimeStamp` AS `LastLogonTimeStamp`, `plugin_ad_users`.`LockoutTime` AS `LockoutTime`, `plugin_ad_users`.`AccountExpires` AS `AccountExpires`, `plugin_ad_users`.`PwdLastSet` AS `PwdLastSet`, `plugin_ad_users`.`FirstName` AS `FirstName`, `plugin_ad_users`.`LastName` AS `LastName`, `plugin_ad_users`.`Address` AS `Address`, `plugin_ad_users`.`POBox` AS `POBox`, `plugin_ad_users`.`City` AS `City`, `plugin_ad_users`.`State` AS `State`, `plugin_ad_users`.`Zip` AS `Zip`, `plugin_ad_users`.`Country` AS `Country`, `plugin_ad_users`.`Phone` AS `Phone`, `plugin_ad_users`.`Pager` AS `Pager`, `plugin_ad_users`.`Fax` AS `Fax`, `plugin_ad_users`.`Cell` AS `Cell`, `plugin_ad_users`.`Website` AS `Website`, `plugin_ad_users`.`Email` AS `Email`, `plugin_ad_users`.`DomainAdministrator` AS `DomainAdministrator`, `plugin_ad_entries`.`DomainGUID` AS `domainguid`, `plugin_ad_domains`.`DomainName` AS `domainname`, `plugin_ad_domains`.`LastUpdate` AS `lastupdate`, `plugin_ad_domains`.`MaxPwdAge` AS `MaxPwdAge`, `plugin_ad_domains`.`MinPwdAge` AS `MinPwdAge`, `plugin_ad_domains`.`MinPwdLength` AS `MinPwdLength`, `plugin_ad_domains`.`PwdProperties` AS `PwdProperties`, `plugin_ad_domains`.`LockoutThreshold` AS `LockoutThreshold`, `plugin_ad_domains`.`LastUpdate` AS `DomainIfoLastUpdated`, `plugin_ad_domains`.`PwdHistoryLength` AS `PwdHistoryLength`, `computers`.`ClientID` AS `clientid`, `computers`.`LocationID` AS `locationid` FROM `plugin_ad_users` JOIN `plugin_ad_entries` ON `plugin_ad_users`.`ObjectGUID` = `plugin_ad_entries`.`ObjectGUID` JOIN `plugin_ad_domains` ON `plugin_ad_entries`.`DomainGUID` = `plugin_ad_domains`.`ObjectGUID` JOIN `computers` ON `plugin_ad_domains`.`InfrastructureServerID` = `computers`.`ComputerID` 


My report looks like this:

http://i.imgur.com/f8ZwZ3L.png

I can help you build it up if you want in the report center? Do you have any experience with it?
Last edited by Gavsto on Wed Oct 05, 2016 4:03 pm, edited 1 time in total.
 
timbo83251
Geek
Topic Author
Posts: 77
Joined: Wed Apr 02, 2014 4:19 pm
Current LT Agent Count: 1500+

Re: Active Directory User List

Wed Oct 05, 2016 4:02 pm

Wow that is perfect! I wasn't sure how you'd be able to link to clientID...

So, pardon my ignorance, but how can I get this into the reporting center, would you mind sharing how you did this or perhaps the template needed?

Thank you!
 
User avatar
Gavsto
Geek
Posts: 105
Joined: Tue Dec 08, 2015 7:35 am

Re: Active Directory User List

Wed Oct 05, 2016 4:05 pm

What's your current experience with the new report center? So i can tailor the instruction appropriately!
 
timbo83251
Geek
Topic Author
Posts: 77
Joined: Wed Apr 02, 2014 4:19 pm
Current LT Agent Count: 1500+

Re: Active Directory User List

Wed Oct 05, 2016 4:15 pm

Unfortunately I have not delved much into the designer other than to make minor formatting changes. I've mostly run the canned reports...

I assume I somehow use the data source editor to import your query?
 
User avatar
Gavsto
Geek
Posts: 105
Joined: Tue Dec 08, 2015 7:35 am

Re: Active Directory User List

Wed Oct 05, 2016 4:33 pm

OK,

1) New Report from Template > Client Base, set your name/description and publish. You will end up in a template report
2) Edit data source, remove clientsummary query and the relationship too - you should be left with just Client in query.
3) Add a new query, query name ADUsers, source > custom query, custom SQL is above
4) Add a relationship, name ClientToADUsers Parent Data source Clients, Member ClientID. Child Data source ADUsers, member clientid. Click OK on data source editor.
5) In the report in the detailBandClient delete everything but [ClientCompany]
6) Right click in the detailbandclient and choose insert detail report > clienttoadUsers - this will add another section
6) Middle right hand side hit "Field List", expand Clients and then expand clienttoadusers. Drag the fields you want onto the new detail report you just created.
7) Single click in the detailbandclient to select it and in the property grid (bottom right) change page break to none.

I'd advise you test it at this point to make sure you're getting back what you expect. You can do that quickly by hitting "Print Preview". The rest is just display and formatting which I'm sure you can figure out.

Make sure you "publish to database" when finished or you will loose the changes.
 
timbo83251
Geek
Topic Author
Posts: 77
Joined: Wed Apr 02, 2014 4:19 pm
Current LT Agent Count: 1500+

Re: Active Directory User List

Mon Oct 10, 2016 9:02 am

This was a huge help, thank you for taking the time to explain how to do it. One stupid thing I am getting stuck on -- when I run the report, it actually comes out with "Client Report Name" and "Client Report description here" instead of the actual name which is "Active Directory Report" and actual description.

Also, any tricks with the account status flags? And which field is the password expiration date, is it calculated from max age somehow?

Thanks again!
 
User avatar
Gavsto
Geek
Posts: 105
Joined: Tue Dec 08, 2015 7:35 am

Re: Active Directory User List

Mon Oct 10, 2016 10:21 am

I hit the same when I started out.

You actually need to edit them in Field Lists > Clients > Report Name and Report Description - edit the expression on those and the report name will change.

For the account controls, you'll need to create a new calculated field and put the following code in

Iif([AccountControls] == 512,'Enabled Account',Iif([AccountControls] == 514,'Disabled Account',Iif([AccountControls] == 544,'Enabled, Password Not Required',Iif([AccountControls] == 546,'Disabled, Password Not Required',Iif([AccountControls] == 66048,'Enabled, Password Does not Expire',Iif([AccountControls] == 66050,'Disabled, Password Does Not Expire',Iif([AccountControls] == 66080,'Enabled, Password Does Not Expire or Required',Iif([AccountControls] == 66082,'Disabled, Password Does Not Expire or Required','Unknown'))))))))


You will need to generate a calculated field for the password expiration date too then drag it on. The expression should be

AddDays([PwdLastSet],[MaximumPasswordAgeInDays])
 
rmuell01
Geek
Posts: 117
Joined: Fri Dec 27, 2013 11:35 am
Current LT Agent Count: 500+

Re: Active Directory User List

Fri Jan 20, 2017 7:58 am

I am currently getting a error when I try to sort the report by clientID. See the attached picture. Does anyone know how to fix this? It will work if I don't specify a client that I want to run the report on.

Thanks in Advanced!
You do not have the required permissions to view the files attached to this post.
 
Mathesonian
Geek
Posts: 35
Joined: Tue Nov 05, 2013 6:21 am
Current LT Agent Count: 7500+

Re: Active Directory User List

Thu Jan 26, 2017 8:54 am

I am getting the same error. Anyone know what the problem is?
 
User avatar
Gavsto
Geek
Posts: 105
Joined: Tue Dec 08, 2015 7:35 am

Re: Active Directory User List

Fri Jan 27, 2017 8:03 am

For those looking at my query and having problems, I am working on it.

LabTech changed certain things in the report center recently and it broke that query.

Will post back when I have something but I've not got much time to look at it at the moment
 
rmuell01
Geek
Posts: 117
Joined: Fri Dec 27, 2013 11:35 am
Current LT Agent Count: 500+

Re: Active Directory User List

Fri Jan 27, 2017 8:46 am

Gavsto wrote:
For those looking at my query and having problems, I am working on it.

LabTech changed certain things in the report center recently and it broke that query.

Will post back when I have something but I've not got much time to look at it at the moment


Thank you! I was thinking I missed something for a while there.
 
User avatar
Gavsto
Geek
Posts: 105
Joined: Tue Dec 08, 2015 7:35 am

Re: Active Directory User List

Wed Feb 01, 2017 6:28 pm

So, for those following this I have spent this evening investigating. It appears to be a LabTech bug. It's in as known issue 8396574. The title is:
"Reporting - 'Filter String' work within Print Preview but not within View Report" which is what we are all seeing here. Annoying, to say the least. Status is currently DEV - IN PROGRESS.
 
User avatar
dleth
Posts: 1
Joined: Tue Jun 30, 2015 9:02 pm
Current LT Agent Count: 100+

Re: Active Directory User List

Sun Jun 25, 2017 8:06 pm

Has there been any updates to this? Was really hoping to add some of these to my report! :P
 
Fitzzz
Posts: 14
Joined: Thu Mar 09, 2017 8:09 am
Current LT Agent Count: 500+

Re: Active Directory User List

Mon Jun 26, 2017 9:00 am

Interest here as well! Would love to add this to our reports.
 
dallasproit
Posts: 12
Joined: Wed Aug 03, 2016 4:56 pm
Current LT Agent Count: 200+

Re: Active Directory User List

Thu Jul 13, 2017 4:43 pm

We gave up on LT reports a long time ago. Even with the introduction of the new reports, there have been constant bugs. In LT 11, they changed data tables and broke their own reports and took them a long time to update them. We are much happier with CFAR reports. Thank you for releasing the code! I was able to work with Rick today and use the code to create a report in CFAR that accomplishes what I was wanting to do.
 
User avatar
Gavsto
Geek
Posts: 105
Joined: Tue Dec 08, 2015 7:35 am

Re: Active Directory User List

Thu Jul 27, 2017 8:44 pm

For those wondering what this turned out to be, I still don't fully know. I raised a request with LabTech basically begging to help determine what was causing the problem and got knocked back because it was a "custom" report.

Incidentally what I do know is that the report center is massively flakey when it comes to any custom query with a join in, it's one of the only things I've never been able to truly sort in the report center.

The answer to this is to generate an SQL view. This brings its own challenges in terms of permissions (IE you need to be super admin to see the tables etc) and though there are ways to automate the adding of permissions to the view it's a pain in the ass.

If anyone from CWA is reading this please contact me so we can get this sorted and I can release reports to the community that they can easily import instead of hacking workarounds.
 
rmuell01
Geek
Posts: 117
Joined: Fri Dec 27, 2013 11:35 am
Current LT Agent Count: 500+

Re: Active Directory User List

Thu Aug 10, 2017 3:13 pm

Gavsto wrote:
The answer to this is to generate an SQL view...


I have been waiting for this answer for a while. Thanks for posting this solution! Now I can actually have working AD reports.
 
rmuell01
Geek
Posts: 117
Joined: Fri Dec 27, 2013 11:35 am
Current LT Agent Count: 500+

Re: Active Directory User List

Mon Sep 25, 2017 1:56 pm

For those who are looking for a good Active Directory report I have attached a version of my report. It outlines the Users, Groups, and PCs as well as password complexity and compliance.

You will need to import the SQL dump (It imports 4 views that the report uses) and the landscape subpageheader report to get the header to display properly. The last thing to do is import the AD report. The report will only be able to be ran by people with sufficient privileges due to me needing to create the views.

I would also note that I talked to Automate support and they told me that the plugin will not delete users from the database that you have deleted, there is a element of database cleanup that you will need to do. I created a script to clear the tables and re-initialize a update from all Domain Controllers.

Enjoy!

(P.S. I am not responsible for any damage that you do to your labtech system as a result of importing these files)

EDIT* I updated the attachment on this post to the same one as posted below.
You do not have the required permissions to view the files attached to this post.
Last edited by rmuell01 on Thu Oct 05, 2017 9:22 am, edited 1 time in total.
 
starbucksgold
Geek
Posts: 535
Joined: Tue Jun 04, 2013 2:25 pm
Current LT Agent Count: 500+
Location: Pensacola, FL
Contact:

Re: Active Directory User List

Wed Oct 04, 2017 10:59 am

rmuell01 wrote:
For those who are looking for a good Active Directory report I have attached a version of my report. It outlines the Users, Groups, and PCs as well as password complexity and compliance.

You will need to import the SQL dump (It imports 4 views that the report uses) and the landscape subpageheader report to get the header to display properly. The last thing to do is import the AD report. The report will only be able to be ran by people with sufficient privileges due to me needing to create the views.

I would also note that I talked to Automate support and they told me that the plugin will not delete users from the database that you have deleted, there is a element of database cleanup that you will need to do. I created a script to clear the tables and re-initialize a update from all Domain Controllers.

Enjoy!

(P.S. I am not responsible for any damage that you do to your labtech system as a result of importing these files)



Attempted to import the repx file in RC but indicates invalid format. Can you export and repost? Thanks.

Jeff
 
rmuell01
Geek
Posts: 117
Joined: Fri Dec 27, 2013 11:35 am
Current LT Agent Count: 500+

Re: Active Directory User List

Thu Oct 05, 2017 9:19 am

starbucksgold wrote:
rmuell01 wrote:
For those who are looking for a good Active Directory report I have attached a version of my report. It outlines the Users, Groups, and PCs as well as password complexity and compliance.

You will need to import the SQL dump (It imports 4 views that the report uses) and the landscape subpageheader report to get the header to display properly. The last thing to do is import the AD report. The report will only be able to be ran by people with sufficient privileges due to me needing to create the views.

I would also note that I talked to Automate support and they told me that the plugin will not delete users from the database that you have deleted, there is a element of database cleanup that you will need to do. I created a script to clear the tables and re-initialize a update from all Domain Controllers.

Enjoy!

(P.S. I am not responsible for any damage that you do to your labtech system as a result of importing these files)



Attempted to import the repx file in RC but indicates invalid format. Can you export and repost? Thanks.

Jeff


Try this attached report file. I removed the subreports out of the main report. You will need to add them yourself. You will still need the views in SQL.
You do not have the required permissions to view the files attached to this post.