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

• Official comment

  

  Shane Corellian

  November 28, 2016 17:11 (Edited November 28, 2016 17:13)

  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.

   

   

   

   
• 

  Bouma, Colby

  November 28, 2016 15:49

  Try this:

  

  0
• 

  Stephen Valdinger

  November 28, 2016 17:02 (Edited November 28, 2016 17:02)

  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
• 

  Bouma, Colby

  November 28, 2016 17:52

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

  0
• 

  Viktor Madarasz

  November 29, 2016 08:25

  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
• 

  Shane Corellian

  December 01, 2016 18:36 (Edited December 01, 2016 18:53)

  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
• 

  Shane Corellian

  December 01, 2016 18:55

  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.