Hi,
I am a newbie to splunk so apologies if I didn't follow any right etiquettes while creating this issue.
I am trying to create a table where I can show some statistical fields(avg,95perc,count) from current week for a given host and then show the trend from the beginning of the year for only count,95perc. Something like the table below:
Service | CurrentWeek:total | CurrentWeek: | CurrentWeek: | 2021-03-19:total | 2021-03-19: | 2021-03-12:total | 2021-03-12: |
HOST1 | | | | | | | |
HOST2 | | | | | | | |
The way I'm currently doing this by using a join for the current week and aggregating data for the year broken down by week on host field as shown below:
index=some_index earliest=@w latest=now sourcetype="some_src_type"
| stats count as total, avg(some_field) as avg_some_field, perc95(some_field) as some_field_95 by host_name
| join type=outer host_name
[search index=some_index earliest=@y latest=@w sourcetype="some_src_type"
| bin _time span=1w
| eval week=strftime(_time,"%Y-%m-%d")
| stats count as total_by_week, perc95(some_field) as some_field_95 by host_name, week
| chart sum(total_by_week) as total, mean(some_field_95) as some_field_95 by host_name, week useother=f
]
| rename host_name as "Host", total as "CurrentWeek:total", avg_some_field as "CurrentWeek:avg_some_field", some_field_95 as "CurrentWeek:some_field_95"
ASK:
If a host doesn't exist in current week, it doesn't show up in final table (because of the join) Is there a better way to solve this? Also, I would like following weeks to be in descending order in columns to show the most recent ones first. Currently it shows then in ascending order.
@woodcock @DalJeanis @niketn
