Splunk Search

Transpose and aggregate

lvbrunello
New Member

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,

L

Tags (2)
0 Karma
1 Solution

kmorris_splunk
Splunk Employee
Splunk Employee

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

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") ]
0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

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

kmorris_splunk
Splunk Employee
Splunk Employee

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?

0 Karma

lvbrunello
New Member

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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