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

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

bowesmana
SplunkTrust
SplunkTrust

@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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...