Hi, I'm having a bit of an issue with the Geographically Improbable Access panel in the Access Anomalies dashboard of the InfoSec app. Basically, if I add a "search user=username" into the search powering it, I get a hit but without it I don't so for a given time period, I'm getting two results for the specified user if I search explicitly for them which look to be genuine but I don't see them on the general search. This is the search (I've mark my additional username search in bold):
| tstats summariesonly=true allow_old_summaries=true values(Authentication.app) as app from datamodel=Authentication.Authentication where Authentication.action=success by Authentication.user, Authentication.src _time span=1s
| rename "Authentication.*" as "*"
| eventstats dc(src) as src_count by user
| search user=username
| search src_count>1
| sort 0 + _time
| iplocation src
| where isnotnull(lat) AND isnotnull(lon)
| streamstats window=2 earliest(lat) as prev_lat, earliest(lon) as prev_lon, earliest(_time) as prev_time, earliest(src) as prev_src, earliest(City) as prev_city, earliest(Country) as prev_country, earliest(app) as prev_app by user
| where (src != prev_src)
| eval lat1_r=((lat * 3.14159265358) / 180), lat2_r=((prev_lat * 3.14159265358) / 180), delta=(((prev_lon - lon) * 3.14159265358) / 180), distance=(3959 * acos(((sin(lat1_r) * sin(lat2_r)) + ((cos(lat1_r) * cos(lat2_r)) * cos(delta))))), distance=round(distance,2)
| fields - lat1_r, lat2_r, long1_r, long2_r, delta
| eval time_diff=if((('_time' - prev_time) == 0),1,('_time' - prev_time)), speed=round(((distance * 3600) / time_diff),2)
| where (speed > 500)
| eval prev_time=strftime(prev_time,"%Y-%m-%d %H:%M:%S")
| table user, src, _time, City, Country, app, prev_src, prev_time, prev_city, prev_country, prev_app, distance, speed
Anyone got any ideas what's going on? @igifrin_splunk Thanks
Hi @igifrin_splunk - I added in the "global=false" argument but it did not fix the whole problem. I'd been experimenting with adding a "sort 0 user _time" before streamstats which was looking pretty good but it looks like tstats does not return the results ordered by _time consistently. The "global=false" argument negates the need to sort by user as they perform the same function (not sure which would be more computationally efficient) but the sort by _time is still needed to pick up all the hits I was getting before. Thanks
Hi @moogmusic, thanks for sharing the details. Sounds like sorting working incorrectly on data coming from tstats by _time can be a big problem.
Could you share more details or DM me with any obfuscated data sample that can be used to reproduce the issue with sorting by _time? I assume that all the original events' timestamps are good and something else is going on.
Hi @igifrin_splunk, sorry I wasn't clear in my last message. What I was saying was that tstats is not always return the events in time order so just adding in "global=false" wasn't enough on our system, it still need to be sorted by _time. The sort function is working as expected though.
Happy to have a chat and prep some anonymised data to go through it in more detail though. Thanks
Hi @moogmusic, this seems to be an issue with streamstats portion of the search (good catch by the way; thanks!).
Adding global=false to streamstats should show your missing user.
The search will look like this:
| tstats summariesonly=true allow_old_summaries=true values(Authentication.app) as app from datamodel=Authentication.Authentication where Authentication.action=success by Authentication.user, Authentication.src _time span=1s
| rename "Authentication.*" as "*"
| eventstats dc(src) as src_count by user
| search src_count>1
| sort 0 + _time
| iplocation src
| where isnotnull(lat) AND isnotnull(lon)
| streamstats window=2 global=false earliest(lat) as prev_lat, earliest(lon) as prev_lon, earliest(_time) as prev_time, earliest(src) as prev_src, earliest(City) as prev_city, earliest(Country) as prev_country, earliest(app) as prev_app by user
| where (src != prev_src)
| eval lat1_r=((lat * 3.14159265358) / 180), lat2_r=((prev_lat * 3.14159265358) / 180), delta=(((prev_lon - lon) * 3.14159265358) / 180), distance=(3959 * acos(((sin(lat1_r) * sin(lat2_r)) + ((cos(lat1_r) * cos(lat2_r)) * cos(delta))))), distance=round(distance,2)
| fields - lat1_r, lat2_r, long1_r, long2_r, delta
| eval time_diff=if(((_time - prev_time) == 0),1,(_time - prev_time)), speed=round(((distance * 3600) / time_diff),2)
| where (speed > 500)
| eval prev_time=strftime(prev_time,"%Y-%m-%d %H:%M:%S")
| table user, src, _time, City, Country, app, prev_src, prev_time, prev_city, prev_country, prev_app, distance, speed
This fix will go into the next InfoSec app update.