Important Notice: On February 29th, this community will be put into read-only mode. All existing posts will remain but customers will be unable to add new posts or comment on existing. Please feel to join our Community Discord for any questions and discussions.

Report or Dynamic Collection of computers that do not have a specific user or domain group in the Local Administrators group

Hi

 

Im trying to achieve the below with no success.

I can create Dynamic Collections regarding Computers which contain/have a certain User in the Local Administrators Group , but I am failing to do the opposite report when I try to list all computers which miss/do not have a specific user or domain group in the member of their Local Administrators group.

 

Reason:

On our system we have a 'EU Administrators' group in Active Directory that should be in the local Administrator group on each workstation. I am trying to create a report of computers that do not have that group configured into the local Admin group.

 

All our machines are in AD Domain 

 

I d be the most happy to achieve this with PDQ Inventory but as an alternative if Someone can show me how its done in Powershell I d not mind it either

 

Regards

 

Viktor

 

0

Comments

7 comments
Date Votes
  • Official comment

    This one can, admittedly, get a little counterintuitive.

    Colby, you're on the right track however the reason your suggestion wouldn't always work is because it would return any computer that had a member account in Administrators that isn't named "EU Administrators". Basically any other account in Administrators would pass this filter so even computers missing EU Administrators would make it in the report (if they had other member accounts in Administrators).

    Here are two ways to get what you want. The first is with a basic report. Here are the filters used in a Basic Report. Please note that I am not using the Local Group table but instead the Local Group Member table.

    Notice that I have a second Group Filter. This group filter uses a Not All and then contains the value filters which will effectively filter out records that the account name EU Administrators as members of the Administrators Group.

    It is important to also note that I had to use the Computer Name Contains filter at the top with no data in the Value. This helps to properly join the two tables. I am also adding a filter of Never Scanned to remove computers that have never been scanned. Without this extra filter a computer which has never been scanned would pass since it would technically not have EU Administrators in the Administrators table (because it would have NO data in the table).

    Here is the same (almost) report but instead of using the Basic Report I chose to use a SQL report.

    I won't put an image here but instead I will place the actual SQL that you could use in your own report.

    SELECT Computers.Name as Computer
    FROM Computers
    WHERE Computers.ComputerId NOT IN (SELECT LocalGroupMembers.ComputerId FROM LocalGroupMembers
    WHERE (LocalGroupMembers.GroupName = 'Administrators' AND LocalGroupMembers.UserName = 'EU Administrators'))
    AND Computers.ComputerId IN (SELECT LocalGroupMembers.ComputerId FROM LocalGroupMembers WHERE GroupName = 'Administrators')
    AND <ComputerFilter>

    The last line contains the <ComputerFilter> placeholder. This only works inside of PDQ Inventory. It is used to honor the Select Collection Source feature when running a report against the selected collection. Without this placeholder the query would run against all computers in your database not just the computers specified with the Select Collection Source feature.

     

     

     

     

  • Try this:

    0
  • Here's a good write-up on the "Powershell way" to do it: https://powershell.org/2013/04/02/get-local-admin-group-members-in-a-new-old-way-3/.

    I'd modify it just a bit so that you pull all the computers you care about from AD into an array. Then ForEach(){} loop through them with the code linked in the article.

     

    Snippet for pulling from AD if you are unfamiliar:

     

    $array = New-Object System.Collections.ArrayList

    $hosts = Get-ADComputer -SearchBase "OU=somewhere,DC=DC,DC=com/local/edu/etc" -Server domain.controller.fqdn/ip

    Foreach($h in $hosts){

    [void]$array.Add($h.Name)

    }
    1
  • Doh, you're totally right Shane. Yeah, getting filters to do what you want can be a little tricky :)

    0
  • Hi Everyone

     

    Thank You for All the Answers

     

    Hi Shane

     

    Thank You for Your answer. I can confirm the SQL Way works and reports back the correct machines indeed missing the required/specified Admin Group.

     

    However the basic report way I did not manage to make it work , i still get inconclusive results there ( including machines which indeed have the needed group added correctly)

    Perhaps Im configuring my tables wrong in the basic report?

     



    Regards

     

    Viktor

     

     

    0
  • Hi Viktor,

    The reason your basic report isn't working is because you are listing the Local Group Member Name in your COLUMNS. If you only show the computer name then the results will work. If you want to use a basic report AND you want to show the computer name and local group member name then perform these two steps.

    Create a collection that uses the filters listed above.

    Create a report that contains the columns you want (Computer Name and Local Group Member Name) BUT the filters for your report are much simpler and look like this.





    Basically, after you create your collection your report simply filters out computers that aren't in that collection and you will get the results you want.

    The reason you'd need to do this when using a basic report (vs. the SQL report) is because the Basic Report has a difficult time performing certain sub-select queries. In an effort to make basic reports return the info our customers need we had to put certain safeguards in place to prevent run away queries such as those queries which don't have proper joins or sub selects. This is why we also provide the ability to use SQL reports since some of our users are proficient in SQL and can overcome the limitations of the Basic Reports.

     

     

    0
  • Quick note: I modified the image when I realized I had the wrong comparison. Using the example above you would show computers that ARE members of the collection. The original image showed the wrong comparison of Is Not Member. My apologies.

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post