Jump to content

rmuell01

Members
  • Content Count

    133
  • Joined

  • Last visited

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

    Active Directory Reports (Tutorial)

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

    Active Directory Reports (Tutorial)

    Try to import everything with this .sql file. ADViews.sql
  3. rmuell01

    Active Directory Reports (Tutorial)

    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?
  4. rmuell01

    Active Directory Reports (Tutorial)

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

    Active Directory Reports (Tutorial)

    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?
  6. rmuell01

    Active Directory Reports (Tutorial)

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

    Active Directory User List

    For a tutorial and new report files, please see this post:
  8. rmuell01

    Active Directory Reports (Tutorial)

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

    Active Directory User List

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

    Active Directory User List

    That would definitely explain the issue lol. If there is no table, a view cannot be created for it. Glad you got it working!
  11. rmuell01

    Active Directory User List

    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`
  12. rmuell01

    Active Directory User List

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

    Active Directory User List

    Check your database and see if you can see that view in there. I am not aware of any issues with the latest patch, however, I am on patch 10.
  14. rmuell01

    Active Directory User List

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

    Inventory report that includes ESXI and HyperV Info?

    I just used this SQL statement to get separate inventory information on the ESXI servers. This is sufficient for me. SELECT locations.clientID, plugin_vm_esxhosts.DeviceId, plugin_vm_esxhosts.DeviceName, GROUP_CONCAT(DISTINCT plugin_vm_esxvirtualmachines.VmName) AS VMList, plugin_vm_esxhosts.ProductName, SUBSTRING(SUBSTRING_INDEX(plugin_vm_esxhosts.OtherInformation, ';', -1 ), 13) AS SERIAL, plugin_vm_esxhosts.ProductFullVersion, plugin_vm_esxhosts.Vendor, CONCAT(plugin_vm_esxhosts.Vendor, ' ', plugin_vm_esxhosts.Model) AS ModelString, plugin_vm_esxhosts.BiosVersion, plugin_vm_esxhosts.License, plugin_vm_esxhosts.CpuGhz, plugin_vm_esxhosts.NumberCores, CONCAT(plugin_vm_esxhosts.overallmemoryusage DIV 1024, 'GB', '/', plugin_vm_esxhosts.RamMb DIV 1024, 'GB') AS MEMUsed, CONCAT(plugin_vm_esxhosts.ProcessorSockets,'x',' ',plugin_vm_esxhosts.ProcessorType, ' (',plugin_vm_esxhosts.CoresPerSocket * plugin_vm_esxhosts.ProcessorSockets, ' Total Cores)' ) AS CPU, CONCAT(plugin_vm_esxhosts.HyperThreadActive,'/',plugin_vm_esxhosts.HyperThreadAvailable)AS HTActiveAvail, plugin_vm_esxhosts.NumberNics, plugin_vm_esxhosts.CoresPerSocket, locations.name AS LocationName, networkdevices.IPAddress AS hostIP, GROUP_CONCAT(DISTINCT CONCAT(ROUND(plugin_vm_esxdatastorage.CapacityGB - plugin_vm_esxdatastorage.FreeGB,0),' GB', '/',ROUND(plugin_vm_esxdatastorage.CapacityGB,0), ' GB')) AS Datastorage FROM plugin_vm_esxhosts JOIN networkdevices ON networkdevices.deviceID = plugin_vm_esxhosts.deviceid JOIN locations ON networkdevices.locationid = locations.locationid JOIN plugin_vm_esxvirtualmachines ON networkdevices.deviceID = plugin_vm_esxvirtualmachines.deviceID JOIN plugin_vm_esxdatastorage ON plugin_vm_esxvirtualmachines.deviceid = plugin_vm_esxdatastorage.deviceID GROUP BY SUBSTRING(SUBSTRING_INDEX(plugin_vm_esxhosts.OtherInformation, ';', -1 ), 13)
×