Forum Discussion

Copper Contributor

Jun 29, 2022

Powershell and filtering results from cmdlest into CSVs

Hi everyone, I need to have a script that outputs a CSV list, to be used for a mail merge, with the last login and the alternative emails (and other useful info for the mail merge), so I can mass...

azure active directory

PoweShell

script

flaviopbadmin

Copper Contributor

Jun 30, 2022

Hi LainRobertson,

Thanks for your reply!

param($path="$PSScriptRoot\reports",$pwdnochangedindays = 480)
cd $path
$CountFail = ($Results | Where-Object { $_.LastSignInDate -eq $null }).Count

Start-transcript
$cohort = read-host "Enter cohort to audited"
# $notlogged = "| where-object {$_.LastSignInDate -ne $null}""


Connect-MgGraph -Scopes "Directory.ReadWrite.All", "Directory.AccessAsUser.All","User.Read.All","AuditLog.Read.All" 
Select-MgProfile -Name beta

$MSGProps = @(
    'id'
    'displayName'
    'CompanyName'
    'State'
    'OfficeLocation'
    'department'
    'signInActivity'
    'userPrincipalName'
    'userType'
    'createdDateTime'
    'accountEnabled'
    'passwordPolicies'
    'mail'
    'lastPasswordChangeDateTime'
    'OtherMails'
    )

$MSGSplat = @{
    Filter = "userType eq 'Member' and AccountEnabled eq true and startsWith(State, '$cohort')"
    all  = $true
    Property = $MSGProps
    }

$MSGUser = Get-MgUser @MSGSplat
$Results = Foreach ($SingleMSG in $MSGUser)
    {
    [pscustomobject]@{
        Id                 = $SingleMSG.id
        DisplayName        = $SingleMSG.displayName
        CompanyName        = $SingleMSG.CompanyName
        State              = $SingleMSG.State
        OfficeLocation     = $SingleMSG.OfficeLocation
        Department         = $SingleMSG.department
        UserPrinciple      = $SingleMSG.userPrincipalName
        UserType           = $SingleMSG.userType
        Created            = $SingleMSG.createdDateTime
        Enabled            = $SingleMSG.accountEnabled
        Mail               = $SingleMSG.mail
        PasswordChange     = $SingleMSG.lastPasswordChangeDateTime
        PasswordPolicy     = $SingleMSG.passwordPolicies
        LastSignInDate     = $SingleMSG.signInActivity.LastSignInDateTime 
        LastNonInteractive = $SingleMSG.signInActivity.LastNonInteractiveSignInDateTime
        OtherMails = $SingleMSG | select-object -expand OtherMails
        }
    }

$Results | Export-Csv -path  "$path\aad_user_report_$((Get-Date -format "dd-MMM-yyyy"))_$cohort.csv" -notypeinformation
# $NotLogged = $Results | Where-Object { $SingleMSG.signInActivity.LastSignInDateTime -eq $Null }
# $NotLogged | Export-Csv -path  "$path\aad_user_report_not_logged_$((Get-Date -format "dd-MMM-yyyy"))_$cohort.csv" -notypeinformation
write-host "Report can be found here $path"
write-host "$CountFail learners have failed to log in"
Stop-transcript

# Based on chadmcox create-AADMGUserReport.ps1 
# https://www.reddit.com/r/PowerShell/comments/vlrvca/expandproperty_csv_exporting_and_general_noobness/
# https://www.reddit.com/r/PowerShell/comments/vi8rcv/getting_a_list_of_all_users_last_login_status_and/

I have tried to include that in the script and I get 0.

"0 learners have failed to log in"

I also ran it after running the script, which I assume the $Results is still in memory, and also get zero. Which is not true as the report is the same as above.

LainRobertson

Silver Contributor

Jun 30, 2022

flaviopbadmin

Anyhow, that aside, now that you've put the "where" clause into your script (on line 61, which is currently commented out), it's possibly a bit clearer where you've gone wrong.

Line 60 should look like this:

$Results | Where-Object { $_.LastSignInDate -ne $null } | Export-Csv -path  "$path\aad_user_report_$((Get-Date -format "dd-MMM-yyyy"))_$cohort.csv" -notypeinformation

Line 61 should look like this:

$NotLogged = $Results | Where-Object { $_.LastSignInDate -eq $null }

Cheers,

Lain

flaviopbadmin

Copper Contributor

Jun 30, 2022

LainRobertson, Thanks, that has worked!

Now this part stopped working , but the script has outputted two reports one with everyone and the other with just the people who have not logged in.

$CountFail = ($Results | Where-Object { $_.LastSignInDate -eq $null }).Count
write-host "$CountFail learners have failed to log in"

Any ideas how to fix that?

The script is here:

param($path="$PSScriptRoot\reports",$pwdnochangedindays = 480)
cd $path


Start-transcript
$cohort = read-host "Enter cohort to audited"
# $notlogged = "| where-object {$_.LastSignInDate -ne $null}""


Connect-MgGraph -Scopes "Directory.ReadWrite.All", "Directory.AccessAsUser.All","User.Read.All","AuditLog.Read.All" 
Select-MgProfile -Name beta

$MSGProps = @(
    'id'
    'displayName'
    'CompanyName'
    'State'
    'OfficeLocation'
    'department'
    'signInActivity'
    'userPrincipalName'
    'userType'
    'createdDateTime'
    'accountEnabled'
    'passwordPolicies'
    'mail'
    'lastPasswordChangeDateTime'
    'OtherMails'
    )

$MSGSplat = @{
    Filter = "userType eq 'Member' and AccountEnabled eq true and startsWith(State, '$cohort')"
    all  = $true
    Property = $MSGProps
    }

$MSGUser = Get-MgUser @MSGSplat
$Results = Foreach ($SingleMSG in $MSGUser)
    {
    [pscustomobject]@{
        Id                 = $SingleMSG.id
        DisplayName        = $SingleMSG.displayName
        CompanyName        = $SingleMSG.CompanyName
        State              = $SingleMSG.State
        OfficeLocation     = $SingleMSG.OfficeLocation
        Department         = $SingleMSG.department
        UserPrinciple      = $SingleMSG.userPrincipalName
        UserType           = $SingleMSG.userType
        Created            = $SingleMSG.createdDateTime
        Enabled            = $SingleMSG.accountEnabled
        Mail               = $SingleMSG.mail
        PasswordChange     = $SingleMSG.lastPasswordChangeDateTime
        PasswordPolicy     = $SingleMSG.passwordPolicies
        LastSignInDate     = $SingleMSG.signInActivity.LastSignInDateTime 
        LastNonInteractive = $SingleMSG.signInActivity.LastNonInteractiveSignInDateTime
        OtherMails = $SingleMSG | select-object -expand OtherMails
        }
    }

$Results | Export-Csv -path  "$path\aad_user_report_$((Get-Date -format "dd-MMM-yyyy"))_$cohort.csv" -notypeinformation
$NotLogged = $Results | Where-Object { $_.LastSignInDate -eq $null }
$NotLogged | Export-Csv -path  "$path\aad_user_report_not_logged_$((Get-Date -format "dd-MMM-yyyy"))_$cohort.csv" -notypeinformation
$CountFail = ($Results | Where-Object { $_.LastSignInDate -eq $null }).Count
write-host "Report can be found here $path"
write-host "$CountFail learners have failed to log in"
$print = Import-Csv -Path "$path\aad_user_report_not_logged_$((Get-Date -format "dd-MMM-yyyy"))_$cohort.csv"
Echo $print
Stop-transcript

# Based on chadmcox create-AADMGUserReport.ps1 
# Thanks to everyone on the following threads:
# https://www.reddit.com/r/PowerShell/comments/vlrvca/expandproperty_csv_exporting_and_general_noobness/
# https://www.reddit.com/r/PowerShell/comments/vi8rcv/getting_a_list_of_all_users_last_login_status_and/
# https://techcommunity.microsoft.com/t5/windows-powershell/powershell-and-filtering-results-from-cmdlest-into-csvs/m-p/3562395

LainRobertson

Silver Contributor

Jun 30, 2022

flaviopbadmin

Yep, all good.

If you change your line 60 to look like the example from my reply above, you should be fine.

Edited to remove the second statement.

Cheers,

Lain

flaviopbadmin

Copper Contributor

Jun 30, 2022

LainRobertson

I made some changes, now it gives me a count and the ones that failed on the shell, useful to know if no changes have happend since last I ran the audit, without opening the CSV.

param($path="$PSScriptRoot\reports",$pwdnochangedindays = 480)
cd $path


Start-transcript
$cohort = read-host "Enter cohort to audited"
# $notlogged = "| where-object {$_.LastSignInDate -ne $null}""


Connect-MgGraph -Scopes "Directory.ReadWrite.All", "Directory.AccessAsUser.All","User.Read.All","AuditLog.Read.All" 
Select-MgProfile -Name beta

$MSGProps = @(
    'id'
    'displayName'
    'CompanyName'
    'State'
    'OfficeLocation'
    'department'
    'signInActivity'
    'userPrincipalName'
    'userType'
    'createdDateTime'
    'accountEnabled'
    'passwordPolicies'
    'mail'
    'lastPasswordChangeDateTime'
    'OtherMails'
    )

$MSGSplat = @{
    Filter = "userType eq 'Member' and AccountEnabled eq true and startsWith(State, '$cohort')"
    all  = $true
    Property = $MSGProps
    }

$MSGUser = Get-MgUser @MSGSplat
$Results = Foreach ($SingleMSG in $MSGUser)
    {
    [pscustomobject]@{
        Id                 = $SingleMSG.id
        DisplayName        = $SingleMSG.displayName
        CompanyName        = $SingleMSG.CompanyName
        State              = $SingleMSG.State
        OfficeLocation     = $SingleMSG.OfficeLocation
        Department         = $SingleMSG.department
        UserPrinciple      = $SingleMSG.userPrincipalName
        UserType           = $SingleMSG.userType
        Created            = $SingleMSG.createdDateTime
        Enabled            = $SingleMSG.accountEnabled
        Mail               = $SingleMSG.mail
        PasswordChange     = $SingleMSG.lastPasswordChangeDateTime
        PasswordPolicy     = $SingleMSG.passwordPolicies
        LastSignInDate     = $SingleMSG.signInActivity.LastSignInDateTime 
        LastNonInteractive = $SingleMSG.signInActivity.LastNonInteractiveSignInDateTime
        OtherMails = $SingleMSG | select-object -expand OtherMails
        }
    }

$Results | Export-Csv -path  "$path\aad_user_report_$((Get-Date -format "dd-MMM-yyyy"))_$cohort.csv" -notypeinformation
$NotLogged = $Results | Where-Object { $_.LastSignInDate -eq $null }
$NotLogged | Export-Csv -path  "$path\aad_user_report_not_logged_$((Get-Date -format "dd-MMM-yyyy"))_$cohort.csv" -notypeinformation
write-host "Report can be found here $path"
$print = Import-Csv -Path "$path\aad_user_report_not_logged_$((Get-Date -format "dd-MMM-yyyy"))_$cohort.csv"
$Countfail = Import-Csv -Path "$path\aad_user_report_not_logged_$((Get-Date -format "dd-MMM-yyyy"))_$cohort.csv" | Measure-Object | Select-Object -expand count
write-host "$CountFail learner(s) have failed to log in"
Echo $print
Stop-transcript

# Based on chadmcox create-AADMGUserReport.ps1 
# Thanks to everyone on the following threads:
# https://www.reddit.com/r/PowerShell/comments/vlrvca/expandproperty_csv_exporting_and_general_noobness/
# https://www.reddit.com/r/PowerShell/comments/vi8rcv/getting_a_list_of_all_users_last_login_status_and/
# https://techcommunity.microsoft.com/t5/windows-powershell/powershell-and-filtering-results-from-cmdlest-into-csvs/m-p/3562395