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
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Network to App: Observability Unlocked [May & June Series]

In today’s digital landscape, your environment is no longer confined to the data center. It spans complex ...

SPL2 Deep Dives, AppDynamics Integrations, SAML Made Simple and Much More on Splunk ...

Splunk Lantern is Splunk’s customer success center that provides practical guidance from Splunk experts on key ...

[Puzzles] Solve, Learn, Repeat: Matching cron expressions

This puzzle (first published here) is based on matching timestamps to cron expressions.All the timestamps ...