I have this search which works great. It makes a list for me of load times for each user, and then a total of all time (basically adding up all user times and giving me a total). This search works amazing for 24 hours time.
sourcetype=EDR user=*
| dedup LoadTime, user
| stats count by LoadTime,user,_time | fields - count
| transaction maxspan=24h
| convert dur2sec(LoadTime) AS LoadTimeSec
| eventstats sum(LoadTimeSec) as "TotalSec"
| convert timeformat="%H:%M:%S" ctime(TotalSec) AS Total_Time
| fields LoadTime, user, Total_Time
| fields - _raw, _time
However switching to say, 7 days doesn't work so good. For 7 days I would like to have a list of 7 events, and each event would contain the list of the load times for each user, the users, and the total load time.
How can I tell splunk to give me 7 days of this data separated like this? It's instead giving me a jumbled list. Would "span" work on this? So far I haven't gotten it to work.
I am having a hard time following this. I would probably simplify it like this, for a start
sourcetype=EDR user=*
| dedup LoadTime, user, _time
| bucket _time span=1d
| stats sum(LoadTime) as UserLoadTime by _time user
| eventstats sum(UserLoadTime) as TotalLoadTime by _time
| fieldformat UserLoadTime = tostring(UserLoadTime,"duration")
| fieldformat TotalLoadTime = tostring(TotalLoadTime,"duration")
| eval Day = strftime(_time,"%x")
| rename user as User
| table Day TotalLoadTime User UserLoadTime
I am unsure of the need for the dedup, unless you have duplicate data in your index.
If you want a slightly different format, try this (it's a little weird, but it should work):
sourcetype=EDR user=*
| dedup LoadTime, user, _time
| bucket _time span=1d
| stats sum(LoadTime) as UserLoadTime by _time user
| eventstats sum(UserLoadTime) as TotalLoadTime by _time
| fieldformat TotalLoadTime = tostring(TotalLoadTime,"duration")
| eval Day = strftime(_time,"%x")
| eval UserPlusTime = user + ": " + tostring(UserLoadTime,"duration")
| stats last(TotalLoadTime) as "Total Seconds" values(UserPlusTime) as "User: LoadTime" by Day
I am having a hard time following this. I would probably simplify it like this, for a start
sourcetype=EDR user=*
| dedup LoadTime, user, _time
| bucket _time span=1d
| stats sum(LoadTime) as UserLoadTime by _time user
| eventstats sum(UserLoadTime) as TotalLoadTime by _time
| fieldformat UserLoadTime = tostring(UserLoadTime,"duration")
| fieldformat TotalLoadTime = tostring(TotalLoadTime,"duration")
| eval Day = strftime(_time,"%x")
| rename user as User
| table Day TotalLoadTime User UserLoadTime
I am unsure of the need for the dedup, unless you have duplicate data in your index.
If you want a slightly different format, try this (it's a little weird, but it should work):
sourcetype=EDR user=*
| dedup LoadTime, user, _time
| bucket _time span=1d
| stats sum(LoadTime) as UserLoadTime by _time user
| eventstats sum(UserLoadTime) as TotalLoadTime by _time
| fieldformat TotalLoadTime = tostring(TotalLoadTime,"duration")
| eval Day = strftime(_time,"%x")
| eval UserPlusTime = user + ": " + tostring(UserLoadTime,"duration")
| stats last(TotalLoadTime) as "Total Seconds" values(UserPlusTime) as "User: LoadTime" by Day
Also I like this, but I'm not sure it's as nice as Transaction. With transaction, it's taking everything I mentioned and putting it as 1 result for 243 hours. For 7 days I'd like to get 7 results, but I get 70 right now. I am going to see if I can work transaction in there.
btw thank you. I read about the bucket command for a while but wasn't sure if that would help. There's always many options
This is working. I added something to first convert time to seconds then convert it back to normal time for a sum. I would love to only have TotalLoadTime displayed once for each day (sum of userloadtime for 1 day and put in totalloadtime once). I'm still looking into this but this certainly is more on the track I was trying to go.