Powershell and filtering results from cmdlest into CSVs

[flaviopbadmin]

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 mail anyone that has not logged in yet.

I have stumbled my way into creating a script, with lots of help and cobbled together from around the internet, now I am just missing a piece, which is to filter out users that have not logged in yet, as in have no data in the LastSignInDateTime field.

This is the script:

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

Start-transcript
$cohort = read-host "Enter cohort to audited"


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
write-host "Report can be found here $path"
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/

It produces a CSV like this:

No idea how to do that, I have tried to add

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

to the export oart of it , but no results from that.

Any suggestions on how to get only people with no log-ins included?

[#2]

@flaviopbadmin 

Edited as my original statement was incorrect. I wasn't paying close enough attention to your hashtable where you use a different variable name.

That being the case, I can't see any issue.

As a basic cross-reference against $Results, if you run the following, do you get a return value for Count greater than zero?

($Results | Where-Object { $_.LastSignInDate -eq $null }).Count

Cheers,

Lain

[#3]

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.

[#4]

@flaviopbadmin 

Oh, I only meant to run it manually in the scenario where you were manually testing from the command line.

If you're going to throw it into the script then you'd need to do so after line 59. There's no point having it on line 3 since $Results doesn't even exist yet, meaning you'll always get a value of 0.

Cheers,

Lain

[#5]

@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

[#6]

@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

[#7]

@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

[#8]

@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

if 

[#9]

@flaviopbadmin 

Line 65 can be removed entirely if you update line 66 to the following. You don't have to read the file back in just to get the count.

New line 66:

write-host "$($NotLogged.Count) learners have failed to log in"

Cheers,

Lain

[#10]

That does not work for some reason. It simply does not count