Azure Sentinel & Log Analytics – Cross correlate between data on Azure Blob Storage and Log Analytics

I just wanted briefly document a quick way to bring external data to Sentinel / Log Analytics for cross-correlation between internal and external data sources.

Use cases

Typical use case is set of data, which is not part of existing logs in Log Analytics. For this case I wanted to have the refresh_token validity part of the logs when correlating with sign-in, and or non interactive sign-in logs

// This is just an example use case; better example use cases exists, but this works as technical example to show how this feature works (Refresh_token use can be correlated also on non interactive user logs)

References

Azure Log Analytics: how to read a file

ExternalDataOperator

Example

• Get the data from the source & Export the data to CSV

Get-AzureADUser -All $true| select userPRincipalName, refresh | Export-Csv -Encoding Default -Delimiter "," c:\temp\lalist.csv -NoTypeInformation

• Upload the data to blob
  • I use the ”right click-upload to storage” -client which I’ve built for use cases like this, to create avoid creating public blob files by use of private blobs and SAS tokens, and getting link for Log Analytics essentially with single click
    • more here Poc Part 0 – Azure Blob Storage right click to share files!

• add the SAStoken generated by the app to the LA query

externaldata(UserPrincipalName:string, RefreshTokensValidFromDateTime:string  ) [@"https://burneraccount.blob.core.windows.net/temp/lalist.csv?st=mysastoken"] 
| extend UserPrincipalNameFromCSV = tolower(UserPrincipalName)
| join kind=inner SigninLogs on $left.UserPrincipalNameFromCSV == $right.UserPrincipalName |distinct  UserPrincipalName, UserPrincipalNameFromCSV, RefreshTokensValidFromDateTime

• Run the query