topic Re: How to edit my search to create a 3 column table with proper grouping of values? in Splunk Search

How to edit my search to create a 3 column table with proper grouping of values?

HattrickNZ — Sun, 12 Apr 2015 23:47:08 GMT

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

Re: How to edit my search to create a 3 column table with proper grouping of values?

ramdaspr — Sun, 12 Apr 2015 23:52:24 GMT

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

Re: How to edit my search to create a 3 column table with proper grouping of values?

HattrickNZ — Mon, 13 Apr 2015 01:27:03 GMT

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

but what I really want is:

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)

Re: How to edit my search to create a 3 column table with proper grouping of values?

ramdaspr — Mon, 13 Apr 2015 01:54:21 GMT

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

Re: How to edit my search to create a 3 column table with proper grouping of values?

HattrickNZ — Mon, 13 Apr 2015 03:13:45 GMT

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.

Re: How to edit my search to create a 3 column table with proper grouping of values?

HattrickNZ — Mon, 13 Apr 2015 03:23:46 GMT

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

Re: How to edit my search to create a 3 column table with proper grouping of values?

HattrickNZ — Mon, 13 Apr 2015 03:35:57 GMT

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

Re: How to edit my search to create a 3 column table with proper grouping of values?

Runals — Tue, 14 Apr 2015 11:33:44 GMT

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.