KQL Search

Query Details

Interactive Web Login

Query

//Logging into a website/interactive login

SigninLogs
| where IsInteractive == 1
| extend City = LocationDetails.city
| extend State = LocationDetails.state
| extend Lat = parse_json(tostring(LocationDetails.geoCoordinates)).latitude
| extend Long = parse_json(tostring(LocationDetails.geoCoordinates)).longitude
| project UserPrincipalName, UserType, AppDisplayName, ResourceDisplayName, City, State, Location, Lat, Long

Explanation

This query is designed to filter and display information about interactive sign-ins to a website. Here's a breakdown of what it does:

  1. Data Source: It starts with the SigninLogs table, which contains records of sign-in activities.

  2. Filter: It filters the records to only include those where the sign-in was interactive, indicated by IsInteractive == 1.

  3. Extract Location Details:

    • It extracts the city and state from the LocationDetails field.
    • It also extracts the latitude and longitude by parsing the geoCoordinates from LocationDetails.

  4. Select Specific Fields: Finally, it selects and displays a set of fields for each filtered record:

    • UserPrincipalName: The user's principal name (often their email or username).
    • UserType: The type of user (e.g., member, guest).
    • AppDisplayName: The name of the application the user signed into.
    • ResourceDisplayName: The name of the resource accessed.
    • City and State: The city and state from which the sign-in occurred.
    • Location: The original location details.
    • Lat and Long: The latitude and longitude coordinates of the sign-in location.

In summary, this query retrieves and displays information about interactive sign-ins, including user details and geographical location data.

Details

Rod Trent profile picture

Rod Trent

Released: September 13, 2024

Tables

SigninLogs

Keywords

SigninLogsUserPrincipalNameUserTypeAppDisplayNameResourceDisplayNameCityStateLocationLatLong

Operators

SigninLogswhereextendparse_jsontostringproject

Actions

GitHub