Unable to join results with all the required stats calculations

[lmaclean]

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.

[Runals · Accepted solution]

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"