KQL - Add location name to the alert "Anomalous sign-in location"

Alon Keilin 141 Reputation points
2022-10-11T09:19:35.163+00:00

I have implemented the alert "Anomalous sign-in location"
When i look up the events for the incidents, I can't see the actually locations, but only the location count.

How can i extend the location details in a string? These details are taken from the signinglogs and aadnoninteractivesigninglogs tables.

Can someone help me to extend / join the details in question?

Here is the query of the alert:

// The query_now parameter represents the time (in UTC) at which the scheduled analytics rule ran to produce this alert.
set query_now = datetime(2022-10-10T04:15:43.1306258Z);
let lookBack_long = 7d;
let lookBack_med = 3d;
let lookBack = 1d;
let aadFunc = (tableName: string) {
table(tableName)
| where TimeGenerated >= startofday(ago(lookBack_long))
| extend
DeviceDetail = todynamic(DeviceDetail),
Status = todynamic(DeviceDetail),
LocationDetails = todynamic(LocationDetails)
| extend locationString = strcat(tostring(LocationDetails.countryOrRegion), "/", tostring(LocationDetails.state), "/", tostring(LocationDetails.city), ";")
| project TimeGenerated, AppDisplayName, UserPrincipalName, locationString
// Create time series
| make-series dLocationCount = dcount(locationString)
on TimeGenerated
in range(startofday(ago(lookBack_long)), now(), 1d)
by UserPrincipalName, AppDisplayName
// Compute best fit line for each entry
| extend (RSquare, Slope, Variance, RVariance, Interception, LineFit)=series_fit_line(dLocationCount)
// Chart the 3 most interesting lines
// A 0-value slope corresponds to an account being completely stable over time for a given Azure Active Directory application
| where Slope > 0.3
| top 50 by Slope desc
| join kind = leftsemi (
table(tableName)
| where TimeGenerated >= startofday(ago(lookBack_med))
| extend
DeviceDetail = todynamic(DeviceDetail),
Status = todynamic(DeviceDetail),
LocationDetails = todynamic(LocationDetails)
| extend locationString = strcat(tostring(LocationDetails.countryOrRegion), "/", tostring(LocationDetails.state), "/", tostring(LocationDetails.city), ";")
| project TimeGenerated, AppDisplayName, UserPrincipalName, locationString
| make-series dLocationCount = dcount(locationString)
on TimeGenerated
in range(startofday(ago(lookBack_med)), now(), 1d)
by UserPrincipalName, AppDisplayName
| extend (RSquare, Slope, Variance, RVariance, Interception, LineFit)=series_fit_line(dLocationCount)
| where Slope > 0.3
| top 50 by Slope desc
)
on UserPrincipalName, AppDisplayName
| join kind = leftsemi (
table(tableName)
| where TimeGenerated >= startofday(ago(lookBack))
| extend
DeviceDetail = todynamic(DeviceDetail),
Status = todynamic(DeviceDetail),
LocationDetails = todynamic(LocationDetails)
| extend locationString = strcat(tostring(LocationDetails.countryOrRegion), "/", tostring(LocationDetails.state), "/", tostring(LocationDetails.city), ";")
| project TimeGenerated, AppDisplayName, UserPrincipalName, locationString
| make-series dLocationCount = dcount(locationString)
on TimeGenerated
in range(startofday(ago(lookBack)), now(), 1d)
by UserPrincipalName, AppDisplayName
| extend (RSquare, Slope, Variance, RVariance, Interception, LineFit)=series_fit_line(dLocationCount)
| where Slope > 5
| top 50 by Slope desc
// Higher threshold requirement on last day anomaly
)
on UserPrincipalName, AppDisplayName
| extend timestamp = TimeGenerated, AccountCustomEntity = UserPrincipalName
};
let aadSignin = aadFunc("SigninLogsMultiWS");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogsMultiWS");
union isfuzzy=true aadSignin, aadNonInt

Microsoft Sentinel
Microsoft Sentinel
A scalable, cloud-native solution for security information event management and security orchestration automated response. Previously known as Azure Sentinel.
959 questions
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
19,098 questions
0 comments No comments
{count} votes

Accepted answer
  1. Clive Watson 5,391 Reputation points MVP
    2022-10-11T16:23:26.857+00:00

    I manged (ages ago) to get the Country to show, but never looked past that or fully tested it

    let lookBack_long = 7d;  
    let lookBack_med = 3d;  
    let lookBack = 1d;  
    let aadFunc = (tableName: string) {  
    table(tableName)  
        | where TimeGenerated >= startofday(ago(lookBack_long))  
        | extend  
        DeviceDetail = todynamic(DeviceDetail),  
        Status = todynamic(DeviceDetail),  
        LocationDetails = todynamic(LocationDetails)  
        | extend locationString = strcat(tostring(LocationDetails.countryOrRegion), "/", tostring(LocationDetails.state), "/", tostring(LocationDetails.city), ";")  
        | project TimeGenerated, AppDisplayName, UserPrincipalName, locationString, countryName=split(locationString,"/").[0]  
        // Create time series  
        | make-series dLocationCount = dcount(locationString)  
        on TimeGenerated  
        in range(startofday(ago(lookBack_long)), now(), 1d)  
        by UserPrincipalName, AppDisplayName, tostring(countryName)  
        // Compute best fit line for each entry  
        | extend (RSquare, Slope, Variance, RVariance, Interception, LineFit)=series_fit_line(dLocationCount)  
        // Chart the 3 most interesting lines  
        // A 0-value slope corresponds to an account being completely stable over time for a given Azure Active Directory application  
        | where Slope > 0.3  
        | top 50 by Slope desc  
    | join kind = leftsemi (  
    table(tableName)  
        | where TimeGenerated >= startofday(ago(lookBack_med))  
        | extend  
        DeviceDetail = todynamic(DeviceDetail),  
        Status = todynamic(DeviceDetail),  
        LocationDetails = todynamic(LocationDetails)  
        | extend locationString = strcat(tostring(LocationDetails.countryOrRegion), "/", tostring(LocationDetails.state), "/", tostring(LocationDetails.city), ";")  
        | project TimeGenerated, AppDisplayName, UserPrincipalName, locationString, countryName=split(locationString,"/").[0]  
        | make-series dLocationCount = dcount(locationString)  
        on TimeGenerated  
        in range(startofday(ago(lookBack_med)), now(), 1d)  
        by UserPrincipalName, AppDisplayName, tostring(countryName)  
        | extend (RSquare, Slope, Variance, RVariance, Interception, LineFit)=series_fit_line(dLocationCount)  
        | where Slope > 0.3  
        | top 50 by Slope desc  
        )  
    on UserPrincipalName, AppDisplayName  
    | join kind = leftsemi (  
    table(tableName)  
        | where TimeGenerated >= startofday(ago(lookBack))  
        | extend  
        DeviceDetail = todynamic(DeviceDetail),  
        Status = todynamic(DeviceDetail),  
        LocationDetails = todynamic(LocationDetails)  
        | extend locationString = strcat(tostring(LocationDetails.countryOrRegion), "/", tostring(LocationDetails.state), "/", tostring(LocationDetails.city), ";")  
        | project TimeGenerated, AppDisplayName, UserPrincipalName, locationString, countryName=split(locationString,"/").[0]  
        | make-series dLocationCount = dcount(locationString)  
        on TimeGenerated  
        in range(startofday(ago(lookBack)), now(), 1d)  
        by UserPrincipalName, AppDisplayName, tostring(countryName)  
        | extend (RSquare, Slope, Variance, RVariance, Interception, LineFit)=series_fit_line(dLocationCount)  
        | where Slope > 5  
        | top 50 by Slope desc  
        // Higher threshold requirement on last day anomaly  
        )  
    on UserPrincipalName, AppDisplayName  
    | extend timestamp = TimeGenerated, AccountCustomEntity = UserPrincipalName  
    };  
    let aadSignin = aadFunc("SigninLogs");  
    let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");  
    union isfuzzy=true aadSignin, aadNonInt  
    

    Posting in case it's useful - please Accept the answer if it helps

    0 comments No comments

0 additional answers

Sort by: Most helpful