Splunk Dev

Search multi-valued field with specific values in sequence

vjajula
Engager

Hi,

I have another request similar to my previous post but with a variation

Here is the multi-valued field ColY. ColY has only two values ON or OFF. I need to find all rows which changed values from ON to OFF or vice-versa in any order. Below is the example

ColXColY
A123456ON
ON
ON
A123457ON
OFF
ON
OFF
A123458ON
ON
OFF
ON
ON
ON
OFF
A123459OFF
OFF
OFF
A123460ON
ON
ON
OFF
OFF
OFF

 

Required output

ColXColYtotalChanges
A123457ON
OFF
ON
OFF
3
A123458ON
ON
OFF
ON
ON
ON
OFF
3
A123460ON
ON
ON
OFF
OFF
OFF
1
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@vjajula 

Can you please try this?

YOUR_SEARCH | mvexpand ColY
| autoregress ColY as p_ColY p=1 | autoregress ColX as p_ColX p=1
| eval cnt = if(p_ColY!=ColY and ColX=p_ColX,1,0)
| stats list(ColY) as ColY sum(cnt) as cnt by ColX
| where cnt > 0

 

My Sample Search :

| makeresults | eval _raw="ColX    ColY
A123456 ON,ON,ON
A123457 ON,OFF,ON,OFF
A123458 ON,ON,OFF,ON,ON,ON,OFF
A123459 OFF,OFF,OFF
A123460 ON,ON,ON,OFF,OFF,OFF" | multikv forceheader=1
| eval ColY=split(ColY,",")
| mvexpand ColY
| autoregress ColY as p_ColY p=1 | autoregress ColX as p_ColX p=1
| eval cnt = if(p_ColY!=ColY and ColX=p_ColX,1,0)
| stats list(ColY) as ColY sum(cnt) as cnt by ColX
| where cnt > 0


 Output.

Screenshot 2021-10-13 at 10.13.42 AM.png

 

Thanks
KV
▄︻̷̿┻̿═━一   😉

If any of my reply helps you to solve the problem Or gain knowledge, an upvote would be appreciated. 

0 Karma
Get Updates on the Splunk Community!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...