Despite of the intent, nowadays I don’t get enough time to get back to blogging. It’s not the case that content is not there but in a way, so many things happening that its hard to decide if anything that important to write about.
Came across a typical issue of getting O365 login reports using PowerShell than usual GUI way, which is not only painfully slow but doesn’t has option to filter out basis user names or target specific list of applications. To solve the issue, have created a small script which may help you on the particular issue if you have OMS subscription for log analytics
# Author Nitish Kumar
# V1.0
# 04/04/2020
# Pre-requisites: This needs PowerShell Module az [installl-module az -scope currentuser] | AzureRM module should be uninstalled and .Net should be 4.7.2 & above
# Connect to Azure Account
Connect-AzAccount -TenantId <xxxxxxxxx> -Subscription <yyyyyyyyyy>
# Would need to find the correct Log Analytics instance (We have two instances and using 1st one)
$wks = Get-AzOperationalInsightsWorkspace
$Sumamry = @()
# Number of days input with validator
Do { $nDays = Read-host "`rLogs for how many days should be check for signIns (only numeric and less than 30) " } While(!($nDays -match '^[0-9]+$'))
# In case if you looking for logins into specific app
$sApp = Read-host "`rAny specific App for which logs needed? (Exact name required) if no, then press enter "
# Take list of users from txt file
$sUserIn = Read-host "`rAny specific user for which logs needed? Exact UPNs required? if yes special a text file containing the same, if no then press enter "
If($sUserIn.Length -ge 1) {
Connect-AzureAD
$AllSpecificGroupUsers = (Get-AzureADGroup -SearchString <group_name> | Get-AzureADGroupMember -All 1).UserPrincipalname
$Users = (Get-Content $sUserIn) | %{Get-AzureADUser -searchstring $_} | Select-Object DisplayName, UserPrincipalName, Mail, AccountEnabled
$UserReport = @()
ForEach($User in $Users){
if($AllSpecificGroupUsers -contains $User.UserPrincipalName){ $GPEnabled = "True" }
else { $GPEnabled = "False" }
$infoObject = New-Object PSObject
$infoObject = $User
Add-Member -inputObject $infoObject -memberType NoteProperty -name "GPEnabled" -value $GPEnabled
$UserReport += $infoObject
}
$UserReport | Select-Object DisplayName, UserPrincipalName, Mail, AccountEnabled, GPEnabled | Export-csv -nti $env:userprofile\desktop\Users_$((Get-Date).ToString('MM-dd-yyyy_hhmm')).csv
$sUser = (Get-Content $sUserIn) -join " "
}
$LoopCount = 6 * $nDays
For ($i=0; $i -le $LoopCount; $i++) {
Write-Progress -activity "Getting logs " -status "Percent Done: " -PercentComplete (($i / $LoopCount) * 100) -CurrentOperation " Timespan $((Get-Date).Addhours(-($(4 * $i)))) - $((Get-Date).Addhours(-($(4 * ($i+1)))))"
$time1 = 4 * ($i)
$time2 = 4 * ($i+1)
If($sUser.Length -ge 1){
If($sApp.Length -ge 1) {
$sQuery = "SigninLogs | where '" + $sUser +"' contains UserPrincipalName and AppDisplayName == '" + $sApp +"' and ConditionalAccessStatus == 'success' | where TimeGenerated > ago(" + $time2 + "h) and TimeGenerated < ago("+ $time1 +"h)| order by TimeGenerated asc"
}
Else {
$sQuery = "SigninLogs | where '" + $sUser +"' contains UserPrincipalName and ConditionalAccessStatus == 'success' | where TimeGenerated > ago(" + $time2 + "h) and TimeGenerated < ago("+ $time1 +"h)| order by TimeGenerated asc"
}
}
Else {
If($sApp.Length -ge 1) {
$sQuery = "SigninLogs | where AppDisplayName == '" + $sApp +"' and ConditionalAccessStatus == 'success' | where TimeGenerated > ago(" + $time2 + "h) and TimeGenerated < ago("+ $time1 +"h)| order by TimeGenerated asc"
}
Else {
$sQuery = "SigninLogs | where ConditionalAccessStatus == 'success' | where TimeGenerated > ago(" + $time2 + "h) and TimeGenerated < ago(" + $time1 + "h)| order by TimeGenerated asc"
}
}
$sResponse = Invoke-AzOperationalInsightsQuery -WorkspaceId $wks[0].CustomerId -Query $sQuery
# Collect the data
$Summary +=$sResponse.Results
}
$Summary | Select-Object userDisplayName,UserPrincipalName, OperationName, Category, AppDisplayName, ClientAppUsed, ConditionalAccessStatus, DeviceDetail, IPAddress, Type, TimeGenerated -Unique | Export-csv -nti $env:userprofile\desktop\AzureSigninLogs_$((Get-Date).ToString('MM-dd-yyyy_hhmm')).csv
Nitish Kumar's Blog 