Jump to content
timbo83251

Active Directory User List

Recommended Posts

So recently i noticed my AD views was missing. I ran the SQL to import but now my report is saying it cant find the v_activedirectory_computers. When I try to import the repx to Report Designer, I now get the format issue even with the file that does not have the subreport. Not sure if there is a workaround or if the latest patch does not let me use the report.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Yeah im on patch 12. I ran the report two days ago but that same night I ran a Report Center update and the next day it said it couldnt find that view. When I imported the SQL file, i see it re-created in SQLYOG but it still missing the _computers. But even when I tried to import the original repx file from the zip file here, im seeing the format error and wont let me republish it. Can you provide me a recent zip file if possible? 

Edited by rmjr718

Share this post


Link to post
Share on other sites
4 hours ago, rmjr718 said:

Yeah im on patch 12. I ran the report two days ago but that same night I ran a Report Center update and the next day it said it couldnt find that view. When I imported the SQL file, i see it re-created in SQLYOG but it still missing the _computers. But even when I tried to import the original repx file from the zip file here, im seeing the format error and wont let me republish it. Can you provide me a recent zip file if possible? 

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.

Share this post


Link to post
Share on other sites

I see 3 views actually. v_activedirectory, v_activedirectory_compliance and v_activedirectory_groups. That shows after importing the SQL file in the zip file. 

Share this post


Link to post
Share on other sites
27 minutes ago, rmjr718 said:

I see 3 views actually. v_activedirectory, v_activedirectory_compliance and v_activedirectory_groups. That shows after importing the SQL file in the zip file. 

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`

 

Share this post


Link to post
Share on other sites

thank you. noticed I dont have plugin_Ad_computers anymore either. I am working on getting that restored. I do see it in my Labtech Backups zip file. 

Share this post


Link to post
Share on other sites
17 minutes ago, rmjr718 said:

Got it! Thanks. Not sure why or how that table disappeared. Thank god for backups. 

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

Edited by rmuell01

Share this post


Link to post
Share on other sites

Man this looks really cool, but the ZIP files I found on this thread have SQL in them that doesn't seem to do anything other than drop 4 views that aren't there by default, and a lot of commented out stuff. I just looked at the code, didn't run it. Is it possible to re-post the original ZIP? I'm on 12.0.12 and latest RC, if that kills my chances. 

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

×