Forum Discussion
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 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?
9 Replies
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 }).CountCheers,
Lain
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.
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" -notypeinformationLine 61 should look like this:
$NotLogged = $Results | Where-Object { $_.LastSignInDate -eq $null }Cheers,
Lain
