behavioral rule based on ip historic

[karimoss]

Hello, 

I am trying to implement a behavioral rule, that checks if an ip was used in the last 7 days or not.

this is who my search looks like : 

index=<index> operationName="Sign-in activity" 
| stats count by ipAddress
| eval is_historical=if(ipAddress IN [ search index=<index>operationName="Sign-in activity" earliest=-7d@d 
| dedup ipAddress 
| table ipAddress], "true", "false" )

i got a wrong results and it seems that only the first search was executed, and the eval was failed.

Any Help please ?

Regards

[bowesmana]

The eval statement is wrong, technically it would be

| eval is_historical=if(in(ipAddress, 
[ 
  search index=<index> operationName="Sign-in activity" earliest=-7d@d 
  | stats values(ipAddress) as ipAddress
  | eval ipAddress="\"".mvjoin(ipAddress, "\",\"")."\""
  | return $ipAddress
]
), "true", "false" )

but this is probably the wrong way to go about this, because you are always doing 2 searches, when you only need one. 

You should do a single search, for example like this

index=<index> operationName="Sign-in activity" earliest=-7d@d
| bin _time span=1d
``` Count by day/ip ```
| stats count by _time ipAddress 
``` Count unique days and most recent day by IP ```
| stats dc(_time) as countDays max(_time) as latestDay by ipAddress
``` Now calculate historical indicator ```
| eval is_historical=if(countDays>1 AND latestDay>=relative_time(now(), "@d"), "true", "false" )

[karimoss]

Hello @bowesmana ,

Thank you for your help and your suggested idea^^

I added to the first search  "|search NOT body.properties.deviceDetail.displayName=*" to focus only on authentication with unknown device on azure active directory.

But i got a lot of false positive, do you have any idea how optimize the search to get more relevent results or if you have any other suggestion of rules to detect unknown ip based on authentification history and correlate with other elements maybe.

Thanks in advance for your help

[yuanliu]

I get the feeling that optimization is the least of your problem here.

---

I am trying to implement a behavioral rule, that checks if an ip was used in the last 7 days or not.

... [search index=<index>operationName="Sign-in activity" earliest=-7d@d | ...]

It is just unclear what "used in the last 7 days" really mean because your mock code only constraints earliest.  The default latest is now().  So, that mock code (if not for the code error that @bowesmana pointed out) would have been exactly the same as if the main search starts at earliest=-7d@d latest=now.  In other words, you would have picked up everything from the beginning of the start of 7th day back to now().  There would have been no "false".

@bowesmana interpreted your intention as thus: starting 7th day back, determine whether an IP address that appears in the current day had also appeared in the earlier days.  Is this the correct interpretation?

If that is the requirement, the following should make the distinction.

index=<index> operationName="Sign-in activity" NOT body.properties.deviceDetail.displayName=* earliest=-7d@d ```latest=now```
| eval history = if(_time < relative_time(now(), "@d"), "today", "past7")
| stats values(history) as is_historical count by ipAddress
| where is_historical == "today" ``` shorthand for "today" IN is_historical ```
| eval is_historical = if(is_historical == "past7", "true", "false")