Jump to content
rmuell01

Active Directory Reports (Tutorial)

Recommended Posts

Posted (edited)

I have created 3 reports that pull information from the Active Directory Plugin (You can combine them by copying and pasting everything into one report). I posted my first version of the report on this topic. https://www.mspgeek.com/topic/2900-active-directory-user-list/  and It was requested that I re-post the newest version of the report along with some instructions on how to install it.

Step 1: Create the 4 required views (The names must be exactly as shown below)

Open SQLyog and Browse to the Automate database --> Right click on Views and select Create View. Enter the names and code shown for each view below (The code will replace the "SELECT * FROM ..." in your SQL statement)

Name: v_activedirectory

Code:

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`.`LockoutDuration`   AS `LockoutDuration`,
  `plugin_ad_domains`.`LastUpdate`        AS `DomainIfoLastUpdated`,
  `plugin_ad_domains`.`PwdHistoryLength`  AS `PwdHistoryLength`,
  GROUP_CONCAT(SUBSTR(LEFT(`plugin_ad_memberofxrefs`.`GroupDN`,(LOCATE(',',`plugin_ad_memberofxrefs`.`GroupDN`) - 1)),4) SEPARATOR ',') AS `GroupList`,
  `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_memberofxrefs`
       ON ((`plugin_ad_entries`.`ObjectGUID` = `plugin_ad_memberofxrefs`.`ObjectGUID`)))
    JOIN `plugin_ad_domains`
      ON ((`plugin_ad_entries`.`DomainGUID` = `plugin_ad_domains`.`ObjectGUID`)))
   JOIN `computers`
     ON ((`plugin_ad_domains`.`InfrastructureServerID` = `computers`.`ComputerID`)))
GROUP BY `plugin_ad_users`.`ObjectGUID`

Name: v_activedirectory_compliance

Code:

SELECT
  COUNT(`v_activedirectory`.`AccountControls`) AS `UserCompliance`,
  `v_activedirectory`.`clientid`   AS `clientid`,
  `v_activedirectory`.`domainname` AS `domainname`
FROM `v_activedirectory`
WHERE (((`v_activedirectory`.`AccountControls` = 544)
         OR (`v_activedirectory`.`AccountControls` = 66048)
         OR (`v_activedirectory`.`AccountControls` = 66080))
       AND (NOT((`v_activedirectory`.`GroupList` LIKE '%Report Compliance%')))
       AND (`v_activedirectory`.`AccountName` <> 'administrator')
       AND (`v_activedirectory`.`AccountName` <> 'Administrator'))
GROUP BY `v_activedirectory`.`clientid`

Name: v_activedirectory_computers

Code:

SELECT
  `computers`.`ClientID`                  AS `clientid`,
  `computers`.`LastUsername`              AS `Username`,
  `plugin_ad_entries`.`DomainGUID`        AS `domainguid`,
  `plugin_ad_computers`.`ObjectGUID`      AS `ObjectGUID`,
  GROUP_CONCAT(SUBSTR(LEFT(`plugin_ad_memberofxrefs`.`GroupDN`,(LOCATE(',',`plugin_ad_memberofxrefs`.`GroupDN`) - 1)),4) SEPARATOR ',') AS `GroupList`,
  `plugin_ad_computers`.`OS`              AS `OS`,
  `plugin_ad_computers`.`LogonCount`      AS `LogonCount`,
  `plugin_ad_computers`.`LastLogon`       AS `LastLogon`,
  `plugin_ad_entries`.`Name`              AS `ComputerName`,
  `plugin_ad_entries`.`WhenChanged`       AS `ChangedDate`,
  `plugin_ad_entries`.`WhenCreated`       AS `CreatedDate`,
  `plugin_ad_entries`.`DistinguishedName` AS `OUInfo`
FROM ((((`plugin_ad_computers`
      JOIN `plugin_ad_entries`
        ON ((`plugin_ad_computers`.`ObjectGUID` = `plugin_ad_entries`.`ObjectGUID`)))
     JOIN `plugin_ad_memberofxrefs`
       ON ((`plugin_ad_computers`.`ObjectGUID` = `plugin_ad_memberofxrefs`.`ObjectGUID`)))
    JOIN `plugin_ad_domains`
      ON ((`plugin_ad_entries`.`DomainGUID` = `plugin_ad_domains`.`ObjectGUID`)))
   JOIN `computers`
     ON ((`plugin_ad_domains`.`InfrastructureServerID` = `computers`.`ComputerID`)))
WHERE (NOT(`plugin_ad_entries`.`Name` IN(SELECT
                                           `computers`.`Name`
                                         FROM `computers`
                                         WHERE ((`plugin_ad_entries`.`Name` = `computers`.`Name`)
                                                AND (`computers`.`OS` LIKE CONCAT('%',`plugin_ad_computers`.`OS`,'%'))))))
GROUP BY `plugin_ad_computers`.`ObjectGUID`

Name: v_activedirectory_groups

Code:

SELECT
  `plugin_ad_users`.`ObjectGUID`      AS `ObjectGUID`,
  SUBSTR(LEFT(`plugin_ad_memberofxrefs`.`GroupDN`,(LOCATE(',',`plugin_ad_memberofxrefs`.`GroupDN`) - 1)),4) AS `GroupName`,
  `plugin_ad_users`.`ObjectSid`       AS `ObjectSid`,
  `plugin_ad_users`.`AccountName`     AS `AccountName`,
  `plugin_ad_users`.`FirstName`       AS `FirstName`,
  `plugin_ad_users`.`LastName`        AS `LastName`,
  `plugin_ad_entries`.`DomainGUID`    AS `domainguid`,
  `computers`.`ClientID`              AS `clientid`,
  `computers`.`LocationID`            AS `locationid`,
  `plugin_ad_users`.`ObjectGUID`      AS `UserGUID`,
  `plugin_ad_users`.`AccountControls` AS `AccountControls`
FROM ((((`plugin_ad_users`
      JOIN `plugin_ad_entries`
        ON ((`plugin_ad_users`.`ObjectGUID` = `plugin_ad_entries`.`ObjectGUID`)))
     JOIN `plugin_ad_memberofxrefs`
       ON ((`plugin_ad_entries`.`ObjectGUID` = `plugin_ad_memberofxrefs`.`ObjectGUID`)))
    JOIN `plugin_ad_domains`
      ON ((`plugin_ad_entries`.`DomainGUID` = `plugin_ad_domains`.`ObjectGUID`)))
   JOIN `computers`
     ON ((`plugin_ad_domains`.`InfrastructureServerID` = `computers`.`ComputerID`)))

Step 2: Import the SubPageHeaderLandscape.repx report into automate.

Open Report Center --> Select Report Designer on the left side --> Hit open and browse to the repx file. --> Hit publish to Database and name it "SubPageHeaderLandscape" (Not sure if it matters)

Step 3: Import the other three reports and name them what their file names are. 

Open Report Center --> Select Report Designer on the left side --> Hit open and browse to the repx file. --> Hit publish to Database and name it

----------------------------------------------------------------------

Things to note:

The User Account Compliance graph is looking at if a user account is set to enabled and the password is set to not expire. Currently all accounts with the exact name Administrator and that are in a security group called Report Compliance will be exempted from this graph. This is so you can clean up the report for accounts that you know cant be changing their password all the time. 

The user accounts in the Active Directory User Assessment report are highlighted to let you know that something is wrong with them.

 - All lines that are entirely highlighted in Red have not logged in for over 30 days

- All lines that are entirely highlighted in Grey are disabled accounts and exempted from the User Account Compliance graph.

- All user accounts with the Account status highlighted in Red and Underlined are not in compliance and are what the graph is looking for. 

This report will not be able to be run by anyone but Super Admins. You can get around it by scheduling a daily Automate script to run on the Automate server that looks like this:

image.png.d9377c2e0420eba38057b064b8a1df14.png

(The User1 and User2 are the exact case sensitive Automate Usernames of the users that you want to be able to run these reports. You can do as many users as you would like, just keep the same formatting)

----------------------------------------------------------------------

Below are some screenshots of the reports. The User assessment will list out all users starting on the second page and the group report lists out all users and all the groups they are added to separated by commas. 

image.png.332829bd77d1c597db73c5221817436b.png

image.png.64ea348a46efe6296b9a22411f2cef0f.png

image.png.e600d7c2174f70a407c909388481dd74.png

Active Directory Reports.zip

 

ADViews.sql

Edited by rmuell01
  • Like 1

Share this post


Link to post
Share on other sites
Posted (edited)

Thanks @rmuell01

I was having trouble creating views. The execute button was grayed out in the create view tab. I created a new tab and pasted the above into it. It turns out when I paste your query into the SQL editor, it's saying that the columns do not exist, but there they are as plain as day. I had my DBA look at it and he was stumped too. I took the select subquery and tried to run it, it gives errors. I tried multiple devices and clients all with an error related to the aliases. I noted that the aliases were not really needed (except for the GROUP_CONCAT function) and I took them out. Then started getting unknown column errors. I when thru line by line and recreated the columns using intellisense auto complete. Then it broke on ContactID and I couldn't get it to work by retyping it out. 

Error Code: 1054. Unknown column 'plugin_ad_users.ContactID' in 'field list'

Then I tried about 3 more times with intellisense and it worked on the 4th try. Adding this in case someone else has the same problem but I think it's my PC, it's running slow today so maybe that had something to do with it. 

If at 4th you don't succeed, try again. 


EDIT: turns out there was also a duplicate column: 

  plugin_ad_domains.LastUpdate

 

Edited by TonyPags
Yes

Share this post


Link to post
Share on other sites

I hit another roadblock importing the REPX files. The subreport imported without issue. The 3 other reports failed with a message of "There was an error loading the report. Please verify that the report is in a valid format and try again." 

I tried reloading the report center, no joy. 

I tried reloading system caches for Control Center, no joy. 

I closed the Control Center and cleared the caches using this script. 

	############# clear LTCC ################
	# Close LTCC
	$LTCC = Get-Process ltclient -ea 0
	if($LTCC){$LTCC|Stop-Process -Force}
	# Remove Reg Keys
	Get-childItem 'HKCU:\Software\LabTech\Client\Lists' |
		Remove-Item -Confirm:$false
	# Delete folders
	Get-Item 'C:\ProgramData\Labtech Plugins' -ea 0 |
		Remove-Item -Recurse -Confirm:$false -ea 0
	Get-Item 'C:\ProgramData\Labtech Client\Cache' -ea 0 |
		Remove-Item -Recurse -Confirm:$false -ea 0
	########## end clear LTCC ################

...no joy. 

I tried going directly to my web server and running the Control Center from there and importing the file, no joy. 

I am out of ideas. 

Share this post


Link to post
Share on other sites

You will not be able to import the REPX files until the views have been created and are using the exact names as I specified. Do you have the Active Directory solution installed in your Automate?

Share this post


Link to post
Share on other sites

Hi @rmuell01, 

Yes I did create the views after having some trouble (as described above). There was 1 column I had to remove from the SELECT clause in the first view, because it was a duplicate and caused an error. I don't think this would affect anything down the line since a column is a column and it doesn't matter what order they are in. 

Actually looking at this again, that might be it. The aliases are different. 

  `plugin_ad_domains`.`LastUpdate`        AS `lastupdate`,
  `plugin_ad_domains`.`LastUpdate`        AS `DomainIfoLastUpdated`,

Which one is right? "DomainIfoLastUpdated" looks like a typo as well. Should it be "DomainInfoLastUpdated"? 

Share this post


Link to post
Share on other sites

So I was able to get the reports imported and published, but I cannot run them. 

To get them imported I had to put back the duplicate column and keep the DomainIfoLastUpdated column named as DomainIfoLastUpdated. But something wonky happened when pasting the text from this post into Yog. Even if I used a plain text to paste and re-copy before setting that up. No matter what I did, the view would not be created, it kept giving me the error I originally got above.

I had to take your query and paste it as-is into MySQL Workbench and use the intellisense there, to rewrite the lines that spit back an error. Annoying but not too difficult. What's really weird is that it gave me a duplicate column error yesterday, but today it didn't. 

Now, when I run the reports, all 3 of them give me this error: "Could not find file c:\Program Files (x86)\LabTech Client\PageHeaderLandscape [long GUID]'. And I confirmed this file does not exist on my PC, the web server, nor the DB server (I am split server). I re-imported the header and that didn't help any. 

Any ideas on that path error? 

Share this post


Link to post
Share on other sites

That is from the sub report for the page header. Replace the sub report with the landscape sub report that you imported before the other reports. Maybe it generated a new GUID for it?

Share this post


Link to post
Share on other sites

I ran into the same issue today with the "Could not find file c:\......" the solution that I found to work was Modifying each of the reports, finding where the SubPageHeader is called out in the report:

image.thumb.png.09af76a93252484084d93d3c296094c1.png

 

And on the right hand side properties drop down on the Report Source URL and locate the PageHeaderLandscape in the list and select that and publish the report and the error goes away, my guess is that the original exports called on the location in the original Report Designer directories and you just need to repoint it to the new location in your Report Center. 

image.png.f36d3601ef732e3696bee6187259bda3.png

Share this post


Link to post
Share on other sites
6 hours ago, sholtsy85 said:

I ran into the same issue today with the "Could not find file c:\......" the solution that I found to work was Modifying each of the reports, finding where the SubPageHeader is called out in the report

...

And on the right hand side properties drop down on the Report Source URL and locate the PageHeaderLandscape in the list and select that and publish the report and the error goes away, my guess is that the original exports called on the location in the original Report Designer directories and you just need to repoint it to the new location in your Report Center. 

...

This is the correct way to do it. When I exported the report, I figured it would match the report with the imported one, but I guess not.

Share this post


Link to post
Share on other sites

Same issue as others.  I cannot import the SQL code correctly as I get an error and if I type it all in manually it imports.  Even after typing it all in, I still cannot import the REPX files as I get the same error message: ""There was an error loading the report. Please verify that the report is in a valid format and try again." 

  • Like 1

Share this post


Link to post
Share on other sites

You will only get this error if you dont have the correct nameing for the views or all of the same columns for the views. Can you verify the views show the correct information when you select them in Sqlyog?

Share this post


Link to post
Share on other sites
Posted (edited)

Update: Makes no sense, but I went back and re-did all the steps using Microsoft Edge (Especially the copy/paste into query) And also including the "Importing reports".... worked. Using Chrome, I was getting "plugin errors", and below error. I suspect wysiwyg is not exactly true when copying/pasting and there was some dirty stuff in there. Anyways, hoping this wasn't a fluke and actually helps somebody... "Try the steps with a different browser". Also, I ended up having to republish the Subreport and republish each of the reports with the New GUID created with the Subreport. Everything works now. Thanks to each of you.

 

I am getting this same error when importing reports 

image.png

Edited by Rafe Spaulding

Share this post


Link to post
Share on other sites

Following the updated directions I am now able to successfully create the Tables/Views via the ADViews.sql file but importing the .REPX files continues to fail.
I can successfully import the SubPageHeaderLandscape.REPX file as a Report or Sub-Report but the other three reports fail to import.  I get the same error as @Rafe Spaulding.

The reports appear to start the import and I get a spinning disk for about 2 minutes and then I get the error message.

Share this post


Link to post
Share on other sites

I assume you have the Active Directory plugin installed? And you confirmed the views are showing up and there is data in them?

Share this post


Link to post
Share on other sites
On 6/8/2019 at 4:37 PM, rmuell01 said:

I assume you have the Active Directory plugin installed? And you confirmed the views are showing up and there is data in them?

I assume you have the Active Directory plugin installed?
Yes

And you confirmed the views are showing up and there is data in them?
Yes

 

Share this post


Link to post
Share on other sites
9 minutes ago, HickBoy said:

I assume you have the Active Directory plugin installed?
Yes

And you confirmed the views are showing up and there is data in them?
Yes

 

What version of Automate are you on currently?

Share this post


Link to post
Share on other sites

I am not sure what is going on with yours. Mine is hosted in house, but I don't suspect that would be an issue. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...