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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...