Jump to content
[[Template core/front/profile/profileHeader is throwing an error. This theme may be out of date. Run the support tool in the AdminCP to restore the default theme.]]

Community Reputation

2 Neutral

My Information

  • Agent Count
    500+

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. 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.
  2. I had to uninstall and re-install the extension within control after updating everything. The extension was not loading in Control for some reason.
  3. What version of Automate are you on currently?
  4. I assume you have the Active Directory plugin installed? And you confirmed the views are showing up and there is data in them?
  5. Try to import everything with this .sql file. ADViews.sql
  6. 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?
  7. 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.
  8. 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?
  9. 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?
  10. For a tutorial and new report files, please see this post:
  11. 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: (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. Active Directory Reports.zip ADViews.sql
  12. Ill see what I can do. Would need to generalize the report again. I ended up making several changes from the original report. I split the data into 4 different reports and added some color coding related to the compliance chart along with fixing some weird issues with it.
  13. That would definitely explain the issue lol. If there is no table, a view cannot be created for it. Glad you got it working!
  14. Right click on the main Views folder and create a new view named v_activedirectory_computers and put the following SQL statement in the () 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` 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`
  15. So you see all 4 views in SQLYOG? After those views are in place, it should just import the repx file without an issue. I cant easily give you a new zip file because I have made several changes to the report and the views and I would need to make sure everything is stripped out of the report before putting out a new version of it.
×
×
  • Create New...