Jump to content

rmuell01

Members
  • Content Count

    129
  • Joined

  • Last visited

Community Reputation

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

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

    Active Directory User List

    For a tutorial and new report files, please see this post:
  4. 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
  5. 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.
  6. 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!
  7. 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`
  8. 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.
  9. 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.
  10. 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.
  11. 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)
  12. I am trying to create a inventory report that includes information on the VMHost based on what it sees in the virtualization plugin. I am able to output a list of all of the VMs on a VM-Host with the VM-Host hardware information but I would like to make it cleaner. Sort-of like the format below: VMWare ESXI Hostname: (VMWare Version) (Used Memory)/ (Total Memory) (Used Cores)/(Total Cores) (Hardware Model of the server) (Processor Model Information) (Serial Number of server) VMInfo VMInfo ............ VMWare ESXI Hostname: (VMWare Version) (Used Memory)/ (Total Memory) (Used Cores)/(Total Cores) (Hardware Model of the server) (Processor Model Information) (Serial Number of server) VMInfo VMInfo ............ Does anyone know how to accomplish such organization? This is an example of my current SQL statement. This grabs the VMWare ESXI VMs and puts them in a list and adds hardware server in the line item letting you know what ESXi server it is on. SELECT locations.clientID, plugin_vm_esxhosts.DeviceId, plugin_vm_esxvirtualmachines.VmName, plugin_vm_esxhosts.DeviceName, plugin_vm_esxhosts.ProductName, plugin_vm_esxhosts.OtherInformation, plugin_vm_esxhosts.ProductFullVersion, plugin_vm_esxhosts.Vendor, plugin_vm_esxhosts.Model, plugin_vm_esxhosts.BiosVersion, plugin_vm_esxhosts.License, plugin_vm_esxhosts.CpuGhz, plugin_vm_esxhosts.NumberCores, plugin_vm_esxhosts.RamMb, plugin_vm_esxhosts.ProcessorType, plugin_vm_esxhosts.ProcessorSockets, plugin_vm_esxhosts.CoresPerSocket, plugin_vm_esxvirtualmachines.PowerState, plugin_vm_esxvirtualmachines.GuestOSFullName, plugin_vm_esxvirtualmachines.IPAddress, locations.name, networkdevices.IPAddress AS hostIP 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 plugin_vm_esxvirtualmachines.deviceID = plugin_vm_esxhosts.deviceID
  13. rmuell01

    Active directory reporting

    I posted the report that I created in the link below if you would like to use it. If anything you can use the .sql file to import the views into the database and create your own report. My report goes over Groups, Computers, and Users. viewtopic.php?f=10&t=3018
  14. rmuell01

    Active Directory User List

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

    Active Directory User List

    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
×