transpose and xyseries

[mbasharat]

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:

What I need:

[bowesmana]

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

[mbasharat]

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

[mbasharat · Accepted solution]

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

[bowesmana]

@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]

Excellent, this is great!!!