SOLVED
Home

Azure Log search - Login search question

%3CLINGO-SUB%20id%3D%22lingo-sub-352548%22%20slang%3D%22en-US%22%3EAzure%20Log%20search%20-%20Login%20search%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352548%22%20slang%3D%22en-US%22%3E%3CP%3EWith%20having%20the%20ability%20to%20search%20sing-in%20logs%20I%20am%20trying%20to%20figure%20out%20the%20correct%26nbsp%3Bquery%20for%20the%20following.%20I%20want%20to%20search%20for%20when%20a%26nbsp%3Bsingle%26nbsp%3Baccount%20signs%20in%20from%20multiple%20IPs.%20My%20end%20goal%20here%20is%20to%20create%20a%20search%20query%20that%20will%20run%20say%20every%2015%20mins%20and%20alert%20when%20a%20user%20account%20has%20been%20logged%20in%20from%20multiple%20IP%20addresses%20over%20a%20specified%20time%20frame.%20My%20goal%20is%20to%20use%20this%20to%20help%20pick%20up%20on%20compromised%20accounts%20quicker.%3C%2FP%3E%3CP%3EI%20know%20how%20to%20setup%20the%20alerting%20part%20with%20a%20query%2C%20im%20just%20stuck%26nbsp%3Bat%20how%20to%20write%26nbsp%3Bthe%20part%26nbsp%3Bthat%20only%20picks%20up%20when%20multiple%20IPs%20are%20used....or%20if%20its%20even%20possible.%20I%20don't%20care%20if%20there%20are%20multiple%20sign-ins%20from%20the%20same%20IP%20in%20this%20scenario%2C%20just%20when%20different%20IPs%20are%20used%20for%20the%20same%20user%20account.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUser%20%22bob%22%20signs%20in%20from%20IP%20x.x.x.1%20and%20two%20mins%20later%20x.x.x.2%20and%20then%205%20mins%20later%20x.x.x.3.%20I%20want%20to%20know%20about%20this%20and%20trying%20to%20figure%20out%20a%20way%20how%20using%20a%20query%20of%20the%20sign-in%20logs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-352548%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353516%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Log%20search%20-%20Login%20search%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353516%22%20slang%3D%22en-US%22%3EPerfect%20that%20worked.%20Thank%20you%20very%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353248%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Log%20search%20-%20Login%20search%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353248%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EThat%20is%20possible%20as%20well%20but%20you%20need%20to%20verify%20if%20the%20alert%20will%20work.%20Alerts%20require%20specific%20things%20like%20AggregatedValue%20for%20example.%3C%2FP%3E%0A%3CP%3EThe%20example%20query%20will%20be%3A%3C%2FP%3E%0A%3CPRE%3EAzureActivity%20%0A%7C%20extend%20httpdata%20%3D%20parse_json(HTTPRequest)%20%0A%7C%20summarize%20IpList%20%3D%20makeset(tostring(httpdata.clientIpAddress))%20%20by%20ResourceId%0A%7C%20extend%20AggregatedValue%20%3D%20array_length(IpList)%20%7C%20sort%20by%20AggregatedValue%20desc%20%3C%2FPRE%3E%0A%3CP%3EIn%20your%20case%3A%3C%2FP%3E%0A%3CPRE%3ESigninLogs%0A%7C%20where%20ResultType%20%3D%3D%20%220%22%0A%7C%20summarize%20IpList%20%3D%20makeset(IPAddress)%20%20by%20UserPrincipalName%0A%7C%20extend%20AggregatedValue%20%3D%20array_length(IpList)%20%0A%7C%20where%20AggregatedValue%20%26gt%3B%205%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353075%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Log%20search%20-%20Login%20search%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353075%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20help%2C%20that%20got%20me%20the%20count%20and%20will%20work.%3CBR%20%2F%3E%3CBR%20%2F%3EOne%20last%20question.%20Using%20the%20method%20you%20gave%20me%20I%20get%20the%20total%20count%20of%20different%20IPs%20the%20sign-ins%20are%20coming%20from%20for%20a%20user%2C%20is%20there%20a%20way%20to%20also%20display%20the%20actual%20IPAddresses%20and%20not%20just%20the%20user%20and%20count%3F%3CBR%20%2F%3E%3CBR%20%2F%3EHere%20is%20what%20I%20got%20that%20gives%20me%20the%20count%2C%20can%20something%20be%20added%20that%20shows%20the%20IPs%20related%20to%20the%20aggregatedvalue%3F%3CBR%20%2F%3E%3CBR%20%2F%3ESigninLogs%3CBR%20%2F%3E%7C%20where%20ResultType%20%3D%3D%20%220%22%3CBR%20%2F%3E%7C%20summarize%20AggregatedValue%20%3D%20dcount(IPAddress)%20by%20UserPrincipalName%3CBR%20%2F%3E%7C%20where%20AggregatedValue%20%26gt%3B%205%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-352872%22%20slang%3D%22en-US%22%3ERe%3A%20Azure%20Log%20search%20-%20Login%20search%20question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-352872%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EI%20do%20not%20have%20Azure%20AD%20logs%20in%20my%20env%20but%20I%20can%20simulate%20the%20same%20thing%20via%20Azure%20Activity%20logs.%20The%20most%20basic%20query%20is%20this%3A%3C%2FP%3E%0A%3CPRE%3EAzureActivity%20%0A%7C%20extend%20httpdata%20%3D%20parse_json(HTTPRequest)%20%0A%7C%20summarize%20AggregatedValue%20%3D%20dcount(tostring(httpdata.clientIpAddress))%20%20by%20ResourceId%3C%2FPRE%3E%0A%3CP%3EDon't%20mind%20the%20usage%20of%20parse_json%20that%20is%20specific%20for%20that%20log.%20The%20most%20important%20to%20notice%20is%20that%20I%20use%20dcount()%20and%26nbsp%3B%20count%20it%20by%20Resource%20Id.%20In%20your%20case%20inside%20dcount%20will%20be%20the%20column%20of%20the%20IP%20address%20and%20ResourceId%20for%20you%20will%20be%20the%20user%20name.%20You%20will%20not%20get%20a%20list%20of%20which%20are%20IPs%20but%20you%20will%20get%20their%20count.%20This%20is%20the%20most%20basic%20query%20that%20should%20work%20for%20the%20most%20basic%20number%20of%20results%20alert.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMark%20this%20reply%20as%20answer%20if%20it%20has%20helped%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
fishermc
New Contributor

With having the ability to search sing-in logs I am trying to figure out the correct query for the following. I want to search for when a single account signs in from multiple IPs. My end goal here is to create a search query that will run say every 15 mins and alert when a user account has been logged in from multiple IP addresses over a specified time frame. My goal is to use this to help pick up on compromised accounts quicker.

I know how to setup the alerting part with a query, im just stuck at how to write the part that only picks up when multiple IPs are used....or if its even possible. I don't care if there are multiple sign-ins from the same IP in this scenario, just when different IPs are used for the same user account.

 

User "bob" signs in from IP x.x.x.1 and two mins later x.x.x.2 and then 5 mins later x.x.x.3. I want to know about this and trying to figure out a way how using a query of the sign-in logs.

 

Thanks

 

4 Replies
Solution

Hi,

I do not have Azure AD logs in my env but I can simulate the same thing via Azure Activity logs. The most basic query is this:

AzureActivity 
| extend httpdata = parse_json(HTTPRequest) 
| summarize AggregatedValue = dcount(tostring(httpdata.clientIpAddress))  by ResourceId

Don't mind the usage of parse_json that is specific for that log. The most important to notice is that I use dcount() and  count it by Resource Id. In your case inside dcount will be the column of the IP address and ResourceId for you will be the user name. You will not get a list of which are IPs but you will get their count. This is the most basic query that should work for the most basic number of results alert.

 

Mark this reply as answer if it has helped you.

Thanks for the help, that got me the count and will work.

One last question. Using the method you gave me I get the total count of different IPs the sign-ins are coming from for a user, is there a way to also display the actual IPAddresses and not just the user and count?

Here is what I got that gives me the count, can something be added that shows the IPs related to the aggregatedvalue?

SigninLogs
| where ResultType == "0"
| summarize AggregatedValue = dcount(IPAddress) by UserPrincipalName
| where AggregatedValue > 5

Hi,

That is possible as well but you need to verify if the alert will work. Alerts require specific things like AggregatedValue for example.

The example query will be:

AzureActivity 
| extend httpdata = parse_json(HTTPRequest) 
| summarize IpList = makeset(tostring(httpdata.clientIpAddress))  by ResourceId
| extend AggregatedValue = array_length(IpList) | sort by AggregatedValue desc 

In your case:

SigninLogs
| where ResultType == "0"
| summarize IpList = makeset(IPAddress)  by UserPrincipalName
| extend AggregatedValue = array_length(IpList) 
| where AggregatedValue > 5
Highlighted
Perfect that worked. Thank you very much!
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies