topic Transpose and aggregate in Splunk Search

Transpose and aggregate

lvbrunello — Tue, 29 Sep 2020 20:35:00 GMT

Hello,

i need help to obtain the below results. From:

num         has_breached        sla_name
100         false               P1 RES M
100         false               P1 CON M
200         true                 P2 RES M
200         false               P2 CON M
500         true                 P2 CON M
500         true                 P2 CON M
300         true                 P5 RES M
300         false               P5 CON M
999         true                 P1 RES M
999         false               P3 RES M
999         false               P3 CON M

All pairs *RES M values in sla_name translated (aggregated) together in sla_response column and
all paris *CON M values in sla_name translated (aggregated) together in sla_contract column

To:

num         sla_response        sla_contract
100         false              false
200         true                false
500         true                true
300         true                false

Note that object with num 999 has 3 rows, in case of objects with 3 or more rows, is it possible filter out the related rows?

Thanks,

Re: Transpose and aggregate

somesoni2 — Tue, 24 Jul 2018 14:49:13 GMT

Give this a try

your current search giving field num, has_breached sla_name
| eval sla_name=if(match(sla_name,"RES M"),"sla_response","sla_contract")
| chart values(has_breached) over num by sla_name

Re: Transpose and aggregate

kmorris_splunk — Tue, 24 Jul 2018 15:03:42 GMT

Here is a search using the multisearch command allowing you to only look at numbers with less than 3 entries.

| multisearch 
    [ search index=YOUR_INDEX sourcetype=YOUR_SOURCETYPE sla_name=*RES* 
    | eval sla_response=has_breached] 
    [ search index=YOUR_INDEX sourcetype=YOUR_SOURCETYPE sla_name=*CON* 
    | eval sla_contract=has_breached] 
| stats values(sla_response) as sla_response values(sla_contract) as sla_contract count by num 
| where count < 3 
| table num sla_response sla_contract

Re: Transpose and aggregate

kmorris_splunk — Tue, 24 Jul 2018 15:35:51 GMT

I just realized I misinterpreted how you wanted to handle the case where there were more than 2 entries (999). In this case, did you want to take just the entries where sla_name were associated:

999 true P1 RES M
999 false P3 RES M
999 false P3 CON M

For example, did you just want the 2 that start with P3?

Re: Transpose and aggregate

woodcock — Tue, 24 Jul 2018 17:18:42 GMT

Try this:

| makeresults 
| eval raw="100:false:P1 RES M:::100:false:P1 CON M:::200:true:P2 RES M:::200:false:P2 CON M:::500:true:P2 CON M:::500:true:P2 CON M:::300:true:P5 RES M:::300:false:P5 CON M:::999:true:P1 RES M:::999:false:P3 RES M:::999:false:P3 CON M"
| makemv delim=":::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<num>[^:]+):(?<has_breached>[^:]+):(?<hsla_name>[^:]+)$"
| table num has_breached hsla_name

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| stats count count(eval(has_breached=="true" AND like(hsla_name, "%RES%"))) AS sla_response count(eval(has_breached=="true" AND like(hsla_name, "%CON%"))) AS sla_contract BY num
| where count < 3
| foreach sla_* [ eval <<FIELD>>=if(<<FIELD>>==0, "false", "true") ]

Re: Transpose and aggregate

lvbrunello — Wed, 25 Jul 2018 10:24:07 GMT

I don't want take in consideration all rows with id 999.
Then remove all the rows with a particular id where rows count > 2.