Recently I was asked to get Sign-In logs from Azure LogAnalytics for specific group members. As the group contains large amount of members and I had to get this data from the last 6 months I decided to create PowerShell + Azure Kusto script and run it from the tooling server.

Get Sign-In logs (LogAnalytics)

To get Sign-in logs from Azure first we need to know what is the WorkSpace ID of our Log Analytics. It can be found in Log Analytics workspace overview tab, example:

Next, we need to create our query using Azure Kusto language. A Kusto query is a read-only request to process data and return results. The request is stated in plain text, using a data-flow model designed to make the syntax easy to read, author, and automate. The query uses schema entities that are organized in a hierarchy similar to SQL’s: databases, tables, and columns.

In this example, I had to get logs for some specific group members. So I looped each user of PowerShellBros group and gather all logs since 2019-05-01 by UserPrincipalName:

#Get group members UPNs
$UserList    = (Get-ADGroupMember 'PowerShellBros' -Recursive) | select -Unique | %{(Get-ADUser -Identity $_).UserprincipalName} | sort

#Azure Kusto query
$Query = "SigninLogs | where UserPrincipalName == '$User' | where TimeGenerated > datetime('2019-05-01')"

Additionally, I displayed a summary for each user in the console and appended details like TotalSignIns to CSV file as query can take long hours to complete:

	        #Add to collection
	        $Overview = New-Object PSCustomObject -Property ([ordered]@{
	            Index               = $Index
	            ReportStartDate     = $RunTime
	            QueryExecutionDate  = (Get-Date).ToUniversalTime()
	            UserPrincipalName   = $User       
	            TotalSignIns        = $RawResultCount
	        })

	        $Overview | Export-Csv $OverviewCsv -NoTypeInformation -Append
	        $Overview

Overview and Raw Data results will be saved in Reports folder on your desktop:

Please note that you need AzureRM module to run the query:

Invoke-AzureRmOperationalInsightsQuery -WorkspaceId $WorkspaceID -Query $Query | select Results -ExpandProperty Results

Final script:

	#Authenticate to AzureRM
	Login-AzureRmAccount
	 
	
	#==============================================================
	# Define environment variables
	#==============================================================
	 
	    $ReportPath  = "$env:userprofile\desktop\Reports"
	    $FileDate    = Get-Date -Format "yyyy-MM-dd"
	    $WorkspaceID = "xxxxx-xxxxx-xxxxx-xxxxx-xxxxxx"
	    $UserList    = (Get-ADGroupMember 'PowerShellBros' -Recursive) | select -Unique | %{(Get-ADUser -Identity $_).UserprincipalName} | sort
	    $RunTime     = (Get-Date).ToUniversalTime()
	 
	    #Signin logs output CSV
	    $OutputCsv   = "$ReportPath\PowerShellBros_Raw Data $FileDate.csv"
	    $OverviewCsv = "$ReportPath\PowerShellBros_Overview $FileDate.csv"
	 
	    #Check if folder exist
	    $Folder = Test-Path $ReportPath; if ( -not $Folder) { [void] (New-Item $ReportPath -Type Directory) }
	 
	
	#======================================================================
	# Get signin logs from Log Analytics
	#======================================================================
	 
	    $Index = 1
	    foreach ($User in $UserList){
	        
	        #Log analytics query
	        $Query = "SigninLogs | where UserPrincipalName == '$User' | where TimeGenerated > datetime('2019-05-01')"
	 
	        #Get results from log analytics
	        $RawResult = Invoke-AzureRmOperationalInsightsQuery -WorkspaceId $WorkspaceID -Query $Query | select Results -ExpandProperty Results
	        $RawResultCount = ($RawResult | Measure-Object).Count
	 
	        #Add to collection
	        If ($RawResultCount -ge 1){
	            foreach ($Result in $RawResult){
	                $Result | Select * -ExcludeProperty ConditionalAccessPolicies,Results,LocationDetails,IPAddress | Export-Csv $OutputCsv -NoTypeInformation -Append
	            }
	        }
	 
	        #Add to collection
	        $Overview = New-Object PSCustomObject -Property ([ordered]@{
	            Index               = $Index
	            ReportStartDate     = $RunTime
	            QueryExecutionDate  = (Get-Date).ToUniversalTime()
	            UserPrincipalName   = $User       
	            TotalSignIns        = $RawResultCount
	        })
	 
	        $Overview | Export-Csv $OverviewCsv -NoTypeInformation -Append
	        $Overview
	 
	        #Clear variables
	        Remove-Variable Overview,RawResult
	        [System.GC]::Collect() 
	        $Index++
	    } 
 

I hope this was informative for you 🙂 See you in the next articles.