Report for successful MFA logins


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

Author: Nitish Kumar

I love to write and raising voice, sharing thought and heated debate is a kind of passion for me. Jobwise I am just another Computer professional handling Infra and designing solutions for a big Indian Media house but I love to write, sketch, photography and a lot more.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.