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

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

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

×