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
Get Updates on the Splunk Community!

App Platform's 2025 Year in Review: A Year of Innovation, Growth, and Community

As we step into 2026, it’s the perfect moment to reflect on what an extraordinary year 2025 was for the Splunk ...

Operationalizing Entity Risk Score with Enterprise Security 8.3+

Overview Enterprise Security 8.3 introduces a powerful new feature called “Entity Risk Scoring” (ERS) for ...

Unlock Database Monitoring with Splunk Observability Cloud

  In today’s fast-paced digital landscape, even minor database slowdowns can disrupt user experiences and ...