Jump to content
timbo83251

Active Directory User List

Recommended Posts

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..

Share this post


Link to post
Share on other sites

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?

Edited by Guest

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

What's your current experience with the new report center? So i can tailor the instruction appropriately!

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

59ec9441ed739_ADPluginError.PNG.99e453d41087c87dfed5ae17a42f701a.PNG

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Has there been any updates to this? Was really hoping to add some of these to my report! :P

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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.

59ec9445d2dd5_ActiveDirectoryReport.PNG.bd66b37b47908d16bfff800cb0c3463c.PNG

Active Directory Report.zip

Edited by Guest

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Active Directory Report.zip

Share this post


Link to post
Share on other sites
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.

 

 

Awesome Report! Please forgive my stupidity but what do you mean by add the subreports yourself?

Share this post


Link to post
Share on other sites

 

 

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.

 

 

Awesome Report! Please forgive my stupidity but what do you mean by add the subreports yourself?

The spots that add the logo at the top and the report title are controlled by a subreport. It is basically a report within a report. You should be able to edit any of the report templates and see the subreports at the top and bottom of the report. You should be able to double click it and change it to landscape.

Share this post


Link to post
Share on other sites

In order to import this report, you must have the active directory plugin enabled and you must import the .SQL file before attempting to open the report file. I have confirmed it is working after importing the .sql file. I'm sure you also need the new version of report center.

Share this post


Link to post
Share on other sites

I've noticed they finally released the fix for the Known Issue you originally referenced @Gavsto.  (8396574).  AD report still broken so I assume that wasn't the only cause.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×