Splunk Search

Unable to join results with all the required stats calculations

lmaclean
Path Finder

What I am trying to do is write a report on bandwidth from firewall logs based upon different sites and work out the total bandwidth, unique users, unique sessions at each site but also the avg bandwidth per user at the sites. Without adding in the average user per site I can get the results I want with the following query:

index=... sourcetype=... 
| fields bandwidth_total, site_region, site_name, site_detail, user, sessionid
| stats sum(bandwidth_total) as Bandwidth, dc(user) as User, dc(sessionid) as Sessions by site_region, site_name, site_detail
| eval Bandwidth = tostring(Bandwidth/1024,"commas")." (GB)"
| eval site_name = site_name." (".site_detail." - ".site_region.")"
| eval User = tostring(User,"commas")
| eval Sessions = tostring(Sessions,"commas")
| sort -Bandwidth
| table site_name, User, Sessions, Bandwidth
| rename site_name as "Site (Full Name/Region)", Bandwidth as "Total Bandwidth", User as "Number of Users at Site", Sessions as "Number of Unique Sessions"

But when I try to add in avg(bandwidth_total) as Average and by user, site_name to the stats command, with Average added to the end of one of the evals, I get results with duplicate Sites because each line is only for one user. I tried playing around with transaction, join but couldn't get them to work, I even tried a second stats command but found that the second stats command works off the first stats results not the original.

Tags (2)
0 Karma
1 Solution

Runals
Motivator

From your initial problem statement I think you want to get the average bandwidth by User and not user. I was initially thinking along the lines of using eventstats but think you could probably get by with just an eval statement (bolded). Incidentally I moved your formatted Bandwidth eval down below your sorting statement as my guess is you want to sort it numerically but by then it is a string.

index=... sourcetype=...
| fields bandwidth_total, site_region, site_name, site_detail, user, sessionid
| stats sum(bandwidth_total) as Bandwidth, dc(user) as User, dc(sessionid) as Sessions by site_region, site_name, site_detail
| eval avgUserBandwidth = round(Bandwidth/User)
| eval site_name = site_name." (".site_detail." - ".site_region.")"
| eval User = tostring(User,"commas")
| eval Sessions = tostring(Sessions,"commas")
| sort -Bandwidth
| table site_name, User, Sessions, Bandwidth, avgUserBandwidth
| eval Bandwidth = tostring(Bandwidth/1024,"commas")." (GB)"
| rename site_name as "Site (Full Name/Region)", Bandwidth as "Total Bandwidth", User as "Number of Users at Site", Sessions as "Number of Unique Sessions"

View solution in original post

0 Karma

Runals
Motivator

From your initial problem statement I think you want to get the average bandwidth by User and not user. I was initially thinking along the lines of using eventstats but think you could probably get by with just an eval statement (bolded). Incidentally I moved your formatted Bandwidth eval down below your sorting statement as my guess is you want to sort it numerically but by then it is a string.

index=... sourcetype=...
| fields bandwidth_total, site_region, site_name, site_detail, user, sessionid
| stats sum(bandwidth_total) as Bandwidth, dc(user) as User, dc(sessionid) as Sessions by site_region, site_name, site_detail
| eval avgUserBandwidth = round(Bandwidth/User)
| eval site_name = site_name." (".site_detail." - ".site_region.")"
| eval User = tostring(User,"commas")
| eval Sessions = tostring(Sessions,"commas")
| sort -Bandwidth
| table site_name, User, Sessions, Bandwidth, avgUserBandwidth
| eval Bandwidth = tostring(Bandwidth/1024,"commas")." (GB)"
| rename site_name as "Site (Full Name/Region)", Bandwidth as "Total Bandwidth", User as "Number of Users at Site", Sessions as "Number of Unique Sessions"

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 ...