retrieve multiple values from same query

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 10 years and 7 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
notarat
Posts: 24
Last visit: Mon Feb 12, 2018 6:56 am

retrieve multiple values from same query

Post by notarat »

I'm still pretty new at power shell so please be gentle.

I'm currently trying to retrieve a listing of all security groups in an OU, count the total members assigned, and also count accounts in a group that are inactive (either locked out or disabled)

My current code does that(I think) but it currently calls the Get ADGroupmember cmdlet twice which seems to be inefficient. My problem is, I am not yet skilled enough to pull multiple values from the same query, nor am I skilled enough to know if it is even possible to pull two types of info in one query like that

My code is:
PowerShell Code
Double-click the code block to select all.
Import-Module ActiveDirectory
$groups = Get-ADGroup -f * -searchbase 'MY OU HERE'
    foreach ($group in $groups) {
    $groupname=($group.name)
    $membercount=@(Get-ADGroupMember $groupname).count
    $inactive=@(Get-ADGroupMember $groupname | Get-ADUser -Property * | where {$_.lockedout -OR $_.Enabled -Like 'false'}).count
    $strdone = $groupname +','+ $membercount +','+ $inactive
    Add-content c:\outputfiles\CountofUsersinEachGroup.txt -value $strdone
 }
I would appreciate it if someone could show me how to combine queries to pull multiple values from the same cmdlet (if it's even possible in this case)
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: retrieve multiple values from same query

Post by jvierra »

This is closer to what you want. It uses PowerShell methods to gather data. The result can be formatted or saved any way you need. Just concentrate on gathering the information.
PowerShell Code
Double-click the code block to select all.
Import-Module ActiveDirectory
$groups = Get-ADGroup -f * -searchbase 'MY OU HERE'

foreach ($group in $groups) {
    $members=Get-ADGroupMember $groupname | Get-ADUser -Property * 
    $props=@{
        GroupName=$group.name
        MemberCount=$members.count
        InactiveCount=($members|?{$_.lockedout -or $_.Enabled -eq $false}).count
    }
    New-Object PsObject -Property $props
 } |
 Format-Table -AutoSize
This only uses on call to each Cmdlet for each group.
User avatar
notarat
Posts: 24
Last visit: Mon Feb 12, 2018 6:56 am

Re: retrieve multiple values from same query

Post by notarat »

So $props allows for the creation and population of multiple items from 1 query? Nice!

I will have to research this (obviously, D'Uh) and see if I can't make my older scripts a bit more efficient.

Thanks a bunch!
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: retrieve multiple values from same query

Post by jvierra »

Not required but easier to use when managing a large number of assigned properties. We create a custom object and use the $props hash to generate the object. This guarantees uniformity in objects. It is also easier to read and change.

It is the same as doing this:
PowerShell Code
Double-click the code block to select all.
New-Object PsObject -Property @{
                                GroupName=$group.name
                                Active=$members.count
                                Inactive=($members|?{$_.lockedout -or $_.Enabled -eq $false}).count
                              }
It is just easier to use by storing these in a separate variable.
User avatar
notarat
Posts: 24
Last visit: Mon Feb 12, 2018 6:56 am

Re: retrieve multiple values from same query

Post by notarat »

I seem to be receiving a lot of errors when I run the code. The odd thing is, if I manually do the steps, it works

Example:
PowerShell Code
Double-click the code block to select all.
$m=Get-ADGroupMember Security_GroupName  | Get-ADUser -Property *
$m.count 
PS c:\> 47
$i=($m|?{$_.lockedout -or $_.enabled -like 'false'}).count
PS c:\> 27
These numbers are correct for this example group.

I have not tried creation of the new PSObject for output yet because it seems the errors I am receiving are appearing during the Get-ADGroupMember and Get-ADuser portions of the code.

The errors are like

ADIdentityNotFoundException
FullyQualifiedErrorId : Cannot find an object with identity: 'CN=securitygroupname,OU-Resource....
Get-ADUser : A referral was returned from the server
At line:3 char:51
+ $members=Get-ADGroupMember $groupname | Get-ADUser <<<< -Property *
+ CategoryInfo : ResourceUnavailable: (CN=user.name...
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: retrieve multiple values from same query

Post by jvierra »

Try it this way:
PowerShell Code
Double-click the code block to select all.
Import-Module ActiveDirectory
$groups = Get-ADGroup -f * -searchbase 'MY OU HERE' 
$results=foreach ($group in $groups){
    Write-Host $group.Name -fore green
    $members=Get-ADGroupMember $group.Name -ea 0 | Get-ADUser  -Property * -ea 0
    $props=@{
        GroupName=$group.name
        MemberCount=$members.count
        InactiveCount=($members|?{$_.lockedout -or $_.Enabled -eq $false}).count
    }
    New-Object PsObject -Property $props
 }

$results|ft GroupName, MemberCount, InactiveCount -auto
User avatar
notarat
Posts: 24
Last visit: Mon Feb 12, 2018 6:56 am

Re: retrieve multiple values from same query

Post by notarat »

jvierra wrote:Try it this way:
PowerShell Code
Double-click the code block to select all.
Import-Module ActiveDirectory
$groups = Get-ADGroup -f * -searchbase 'MY OU HERE' 
$results=foreach ($group in $groups){
    Write-Host $group.Name -fore green
    $members=Get-ADGroupMember $group.Name -ea 0 | Get-ADUser  -Property * -ea 0
    $props=@{
        GroupName=$group.name
        MemberCount=$members.count
        InactiveCount=($members|?{$_.lockedout -or $_.Enabled -eq $false}).count
    }
    New-Object PsObject -Property $props
 }

$results|ft GroupName, MemberCount, InactiveCount -auto
Thanks for the assistance so far!

I ran the code in the quote above, using my OU. Running your code lists several security groups (in green) with no accompanying counts of members or inactive members. The output looks like the following:

Security Group1
Security Group2
Security Group3
Security Group4


Then, it starts throwing errors like the one below when it encounters random groups

Get-ADGroupMember : Cannot find an object with identity: ' Security Group5' under: 'DC=fabrikam1,DC=fabrikam2,DC
=fabrikam3,DC=com'.
At line:5 char:31
+ $members=Get-ADGroupMember <<<< $group.Name -ea 0 | Get-ADUser -Property * -ea 0
+ CategoryInfo : ObjectNotFound: (Security Group5') [Get-ADGroupMember], ADIdentityN
otFoundException
+ FullyQualifiedErrorId : Cannot find an object with identity: Security Group5'' under: 'DC=fabrikam1
,DC=fabrikam2,DC=fabrikam3,DC=mil'.,Microsoft.ActiveDirectory.Management.Commands.GetADGroupMember

Security Group6
Security Group7

Etc…it lists more groups, then more errors.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: retrieve multiple values from same query

Post by jvierra »

The following uses a different for of identity to avoid the annoying errors. The errors wouldn't occur if you used a proper searchbase.
PowerShell Code
Double-click the code block to select all.
$groups = Get-ADGroup -f * -searchbase 'MY OU HERE'
$results=foreach ($group in $groups){
    Write-Host $group.Name -fore green
    $members=Get-ADGroupMember $group.distinguishedName -ea 0 | Get-ADUser  -Property * -ea 0
    $props=@{
        GroupName=$group.name
        MemberCount=$members.count
        InactiveCount=($members|?{$_.lockedout -or $_.Enabled -eq $false}).count
    }
    New-Object PsObject -Property $props
 }
 
$results|ft GroupName, MemberCount, InactiveCount -auto
User avatar
notarat
Posts: 24
Last visit: Mon Feb 12, 2018 6:56 am

Re: retrieve multiple values from same query

Post by notarat »

jvierra wrote:The following uses a different for of identity to avoid the annoying errors. The errors wouldn't occur if you used a proper searchbase.
PowerShell Code
Double-click the code block to select all.
$groups = Get-ADGroup -f * -searchbase 'MY OU HERE'
$results=foreach ($group in $groups){
    Write-Host $group.Name -fore green
    $members=Get-ADGroupMember $group.distinguishedName -ea 0 | Get-ADUser  -Property * -ea 0
    $props=@{
        GroupName=$group.name
        MemberCount=$members.count
        InactiveCount=($members|?{$_.lockedout -or $_.Enabled -eq $false}).count
    }
    New-Object PsObject -Property $props
 }
 
$results|ft GroupName, MemberCount, InactiveCount -auto
As I searched through the errors thrown, I saw that when the script encounters a user account in another domain or OU in our forrest it throws an error.

I agree about using the proper searchbase. Unfortunately, I am not allowed to look beyond my own OU. Company Policy.(srsly) :roll:

I guess I'll try to implement a try/catch to count the accts the script encounters which aren't in my OU.
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: retrieve multiple values from same query

Post by jvierra »

If you use the distinguished name and nrrow the search to the OU the errors won't occur.
This topic is 10 years and 7 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked