Splunk Search

Post-tstats manipulation

wilcompl1334
Explorer

I'm working to transition a traditional search to utilize accelerated datamodels my environment has available. My original search is below:

(index=firewall sourcetype="vpn-resource" (event_id="authentication") status=success) OR
(index=webapp outcome.result=SUCCESS eventType=user.authentication.sso) OR
(index=mfa sourcetype=mfa:authentication result=SUCCESS)
| fields type user src_ip targetAppDisplayName ip integration username index
| eval user=coalesce(user, username)
| eval "Resource Accessed"=coalesce(type, integration, targetAppDisplayName)
| eval src_ip=coalesce(ip, src_ip)
| iplocation allfields=true src_ip
| search City!=""
| eval cur_t=_time
| streamstats current=t window=5 first(lat) as prev_lat first(lon) as prev_lon first(cur_t) as prev_t first(src_ip) as IP2 first(City) as City2 first(Country) as Country2 first("Resource Accessed") as prev_r first(index) as prev_i by user
| rename src_ip as IP1 City as City1 Country as Country1 "Resource Accessed" as first_r index as first_i
| eval time_diff=cur_t - prev_t
| distance outputField=distance inputFieldLat1=lat inputFieldLat2=prev_lat inputFieldLon1=lon inputFieldLon2=prev_lon

My version utilizing tstats follows, however in the tstats command I appear to run into issues with the distance command, as both lat/lon and prev_lat/prev_lon are working from a per event versus a per user basis. 

| tstats summariesonly=true prestats=t count fillnull_value=NULL from datamodel=firewall_dm where (log.event_id=gateway-auth) AND log.action=success by sourcetype _time log.action log.src_ip log.user
| tstats summariesonly=true append=t prestats=t count fillnull_value=NULL from datamodel=Authentication where (sourcetype=mfa:authentication OR sourcetype=webappIM2:log) AND Authentication.action=success by sourcetype _time Authentication.action Authentication.src Authentication.user Authentication.app
| rename log.* as *, Authentication.* as *
| eval src_ip=coalesce(src, src_ip)
| eval "Resource Accessed"=if(match(sourcetype, "vpn-resource"), "VPN", app)
| iplocation allfields=true src_ip
| search City!=""
| eval cur_t=_time
| streamstats current=t window=5 first(lat) as prev_lat first(lon) as prev_lon first(cur_t) as prev_t first(src_ip) as IP2 first(City) as City2 first(Country) as Country2 first("Resource Accessed") as prev_r first(index) as prev_i by user
| rename src_ip as IP1 City as City1 Country as Country1 "Resource Accessed" as first_r index as first_i
| eval time_diff=cur_t - prev_t
| distance outputField=distance inputFieldLat1=lat inputFieldLat2=prev_lat inputFieldLon1=lon inputFieldLon2=prev_lon

Based on the input data from both base searches being the same (lines , I would expect the outcome to match, however I believe my use of prestats to be somewhat limiting my ability to manipulate the data downstream?

Labels (1)
0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Get Updates on the Splunk Community!

Tech Talk Recap | Mastering Threat Hunting

Mastering Threat HuntingDive into the world of threat hunting, exploring the key differences between ...

Observability for AI Applications: Troubleshooting Latency

If you’re working with proprietary company data, you’re probably going to have a locally hosted LLM or many ...

Splunk AI Assistant for SPL vs. ChatGPT: Which One is Better?

In the age of AI, every tool promises to make our lives easier. From summarizing content to writing code, ...