How do i create the 3 column table below in splunk (i.e. Label 1-3 would fall into Group1....etc):
I can get a 2 column table using (column 2 and 3 in the above table):
... | stats max(c84162281) as "Average Seizure Traffic per Line (Trunk Group)" by LABEL
but now I want to add the 1st column which has the values I want in the field TG_Category
... | stats max(c84162281) as "Average Seizure Traffic per Line (Trunk Group)" by LABEL,TB_Category
Just add TG_Category to the grouping and it will provide the result by label and category.
I like doing that as well
... | stats max(c84162281) as max by Group Label | sort Group Label | stats list(Label) as Label list(max) as max by Group
This is just about the only time I use list(). Note you have to do your sorting before the second stats command otherwise it will skew the associations between, in this case, Label and max.
... | stats max(c84162281) as "Average Seizure Traffic per Line (Trunk Group)" by LABEL,TB_Category
Just add TG_Category to the grouping and it will provide the result by label and category.
tks i use this search ...| stats max(c84162281) as "Average Seizure Traffic per Line (Trunk Group)" by TG_Category, LABEL | sort - TG_Category
and this is good.
but it gives me something like this
And ideally it would be great if I could sort by max
Label in each Group, something like this but obviously for each group(here I am only showing group1)
regarding the sorting, you can add multiple columns to the sort command as well, so in this case it would be sort - TG_ Category +max
Reg. the formatting, if you mean you want to show nothing if the group name is same as above, it might be possible using streamstats to create a column which shows it like that
original query| streamstats current=f window=1 first(TG_Category) as lstcat | eval cul=if(lstcat=TG_Category,"",TG_Category) | table TG,Category,Label,max
tks very much but I want group1 with all its labels and then group2 ...etc
that does group1 then group2 then group1 again the group3...group2..grroup1 if you know what I mean.
sorry a bit sleepy but got that to work, ignore my last comment
...| sort +TG_Category -"Average Seizure Traffic per Line (Trunk Group)" | streamstats current=f window=1 first(TG_Category) as lstcat | eval cul=if(lstcat=TG_Category,"",TG_Category) | table cul, LABEL, "Average Seizure Traffic per Line (Trunk Group)"
now I need to work out how to add a timestamp cloumn that cooresponds with when that max occured.. tks again
to achive the timestamp I will have to workout away to handle duplicates i.e. if 2 maxes that are the same occur at the hours 9 and 10. One way around this is to do it at a lower granularity e.g. 15minutes that way they will not be any duplicates, assuming 24hour clock.
search using an hour timestamp will have issue with duplicates:
...| stats max(c84162281) as "Average Seizure Traffic per Line (Trunk Group)" by TG_Category, LABEL, date_hour | sort +TG_Category -"Average Seizure Traffic per Line (Trunk Group)" | streamstats current=f window=1 first(TG_Category) as lstcat | eval cul=if(lstcat=TG_Category,"",TG_Category) | table cul, LABEL, "Average Seizure Traffic per Line (Trunk Group)" date_hour