Splunk Search

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

HattrickNZ
Motivator

How do i create the 3 column table below in splunk (i.e. Label 1-3 would fall into Group1....etc):

Image and video hosting by TinyPic

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

Tags (3)
0 Karma
1 Solution

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

View solution in original post

0 Karma

Runals
Motivator

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.

0 Karma

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

0 Karma

HattrickNZ
Motivator

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
Image and video hosting by TinyPic

but what I really want is:
Image and video hosting by TinyPic

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)
Image and video hosting by TinyPic

0 Karma

ramdaspr
Contributor

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
0 Karma

HattrickNZ
Motivator

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.

0 Karma

HattrickNZ
Motivator

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

0 Karma

HattrickNZ
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...