Skip to main content
Sumo Logic

Lab 4 - Azure Top 10 Number of Failed Login Attempts by User

Create a query that will allow you to see the Top 10 High Number of Password Resets to see if potential malicious activity is happening.

Often times you want to detect users who fail to login to their account.  The user could have forgotten their password or someone could be attempting to hack into your network.  In this lab you will be filtering the incoming logs to observe the Top 10 login failures. You will learn how to aggregate data over time.  For this you will use the timeslice and top functions.

Create the query - Top 10 Failed Logins
  1. Create a new search query and set the timeframe for a 24 hour period.   

  2. Set your _sourceCategory to the Active Directory source.
    _sourceCategory="Labs/Azure/AD"

  1. For this query we are going to need to extract the following JSON fields - operationName, properties.userPrincipalName which you want to call user, identity and resultType and callerIPAddress which you want to call src_ip.   After you get the operationName field, you should filter it for "Sign-in activity".  You query now look like this:
    _sourceCategory="Labs/Azure/AD"
    | json field=_raw "operationName"
    | json field=_raw "properties.userPrincipalName" as user
    | where operationName = "Sign-in activity"
    | json field=_raw "resultType"
    | json field=_raw "callerIpAddress" as src_ip
    | lookup latitude, longitude, country_code, country_name, region, city, postal_code from geo://location on ip = src_ip

  2.  We want to test and see if resultType=0 and if so we want to put "Success" in a field called result.  If not resultType=0, then we will display "Failure".  We then only want to see the result of "Failure".  Your new code that you are adding should look like this: 

    //If the resultType = 0 then call the value "Success" ... if not 0 then call it "Failure"
    | if(resultType = 0,"Success","Failure") as result
    //Below should be -- where resultType =1 ... But we don't have logs for that yet so, populating with other than successful logs
    | where result = "Failure"

  3.  Now let's add a timeslice to break things up in 1 min slices and then count by timeslice, user, src_ip.operation_name, resultType, result, city,  and country_name. Let's add a filter to only allow where count > 10.

    | timeslice 1h
    | count by _timeslice,user,src_ip,operationName,resultType,result,city,country_name
    | where _count > 10

     

  4. Finally let's do a Top 10 of Identity based on timeslice, src_ip, operationName, resultType, city, country_name, and count and sort by count descending.  The entire query should should look like this:
    _sourceCategory="Labs/Azure/AD"
    | json field=_raw "operationName"
    | json field=_raw "properties.userPrincipalName" as user
    | where operationName = "Sign-in activity"
    | json field=_raw "resultType"
    | json field=_raw "callerIpAddress" as src_ip
    | lookup latitude, longitude, country_code, country_name, region, city, postal_code from geo://location on ip = src_ip
    //If the resultType = 0 then call the value "Success" ... if not 0 then call it "Failure"
    | if(resultType = 0,"Success","Failure") as result
    //Below should be -- where resultType =1 ... But we don't have logs for that yet so, populating with other than successful logs
    | where result = "Failure"
    | timeslice 1h
    | count by _timeslice,user,src_ip,operationName,resultType,result,city,country_name
    | where _count > 10
    | top 10 user by _timeslice,src_ip,operationName,resultType,result,city,country_name,_count
    | sort by _count desc

Add to the dashboard
  1. Now let's add it to the dashboard you created in the previous exercises, by clicking on the Add to Dashboard button from the Aggregates tab.  Your dashboard should look like this:
    clipboard_eb9fc7e293bce420fdd038670c00c53a7.png
    We will edit and format the dashboard after we add all of the panels.