Splunk Search

transpose and xyseries

mbasharat
Contributor

Hi,

I have a situation where I need to split my stats table. I have tried to use transpose and xyseries but not getting it. HAs someone had the same situation? Pls guide. Thanks!!!

What I have:

1.png

What I need:

2.png

Labels (4)
Tags (1)
0 Karma
1 Solution

mbasharat
Contributor

I actually did below. It is kind of cheating 🙂 but it worked.

 

| makeresults
| eval _raw="ip,scantype,severity,count
1.2.3.4,ip,none,1
1.2.3.4,agent,low,4
1.2.3.4,agent,medium,10
1.2.3.4,agent,high,100
1.2.3.4,agent,critical,50"
| multikv forceheader=1
| table ip scantype severity count
| eval ip_scan=ip."-".scantype
| chart values(count) over ip_scan by severity
| addtotals
| fillnull value="0" none, low, medium, high, critical,Total
| makemv delim="-" ip_scan
| eval ip=mvindex(ip_scan,0)
| eval scantype=mvindex(ip_scan,1)
| table ip, scantype, none, low, medium, high, critical, Total

View solution in original post

0 Karma

bowesmana
Champion

This shows your example data setup then using the chart statement to do what you want

| makeresults
| eval _raw="scantype,severity,count
ip,none,1
agent,low,4
agent,medium,10
agent,high,100
agent,critical,50"
| multikv forceheader=1
| table scantype severity count
| eval COMMENT="-----OK - NOW DO THIS ------"
| chart values(count) over scantype by  severity
| addtotals

Hope this helps

 

0 Karma

mbasharat
Contributor

Hi @ bowesmana,


I actually forgot to include on more column for ip  in the screenshots. Apology. Please see updated screenshots in the original question. Your provided query will need to be like below to get the screenshot 1 and I need that to be like in screenshot 2 please.

| makeresults
| eval _raw="ip,scantype,severity,count
1.2.3.4,ip,none,1
1.2.3.4,agent,low,4
1.2.3.4,agent,medium,10
1.2.3.4,agent,high,100
1.2.3.4,agent,critical,50"
| multikv forceheader=1
| table ip scantype severity count

 

0 Karma

mbasharat
Contributor

I actually did below. It is kind of cheating 🙂 but it worked.

 

| makeresults
| eval _raw="ip,scantype,severity,count
1.2.3.4,ip,none,1
1.2.3.4,agent,low,4
1.2.3.4,agent,medium,10
1.2.3.4,agent,high,100
1.2.3.4,agent,critical,50"
| multikv forceheader=1
| table ip scantype severity count
| eval ip_scan=ip."-".scantype
| chart values(count) over ip_scan by severity
| addtotals
| fillnull value="0" none, low, medium, high, critical,Total
| makemv delim="-" ip_scan
| eval ip=mvindex(ip_scan,0)
| eval scantype=mvindex(ip_scan,1)
| table ip, scantype, none, low, medium, high, critical, Total

View solution in original post

0 Karma

bowesmana
Champion

@mbasharat 

You can actually do it with stats, which will give you the multi-split-by clause rather than having to make the join/split field approach. See this - see how the sum(eval... statements work.

| makeresults
| eval _raw="ip,scantype,severity,count
1.2.3.4,ip,none,1
1.2.3.4,agent,low,4
1.2.3.4,agent,medium,10
1.2.3.4,agent,high,100
1.2.3.4,agent,critical,50"
| multikv forceheader=1
| stats sum(eval(if(severity="none", count, 0))) as none sum(eval(if(severity="low", count, 0))) as low sum(eval(if(severity="medium", count, 0))) as medium sum(eval(if(severity="high", count, 0))) as high sum(eval(if(severity="critical", count, 0))) as critical by ip scantype
| addtotals

 As you can see, there is always more than one way to solve the problem.

mbasharat
Contributor

Excellent, this is great!!!

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!