Splunk Search

How to delete row values if condition is not met in a table?

Edwin1471
Path Finder

Hi all,

I need to write a query that checks whether  (Daily AH <= Daily Po <= Daily Risk <= Daily File <= Daily Instrum)  condition is met for each row. If the condition is not met get rid of the row value that did not meet the condition and all the values after it.  

Edwin1471_1-1662223322928.png

 

 

Labels (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
sourcetype=e143_csv, index="L134521_e143", PROCESS_ID = "FileDelivery" FREQ="DAY"
| rex field=PROCESS_NAME "(?P<Process>.*)for"
| eval Process=if(Process="Daily AHS Instrument File ","Daily AH",Process)
| eval Process=if(Process="Daily Position File ","Daily Po",Process)
| eval Process=if(Process="Daily Credit Risk Info File ","Daily Risk",Process)
| eval Process=if(Process="Daily CB File ","Daily File",Process)
| eval Process=if(Process="Daily Instrument File ","Daily Instrum",Process)
| table Process, START_TIME, FINISH_TIME, RELATED_GRS_UNIT_ID
| sort -START_TIME, -FINISH_TIME, -Process, -RELATED_GRS_UNIT_ID
| dedup RELATED_GRS_UNIT_ID, Process sortby -FINISH_TIME
| xyseries Process, RELATED_GRS_UNIT_ID, FINISH_TIME
| transpose 0 header_field=Process
| eval "Daily Po" = if('Daily AH' <= 'Daily Po', 'Daily Po', null())
| eval "Daily Risk" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk', 'Daily Risk', null())
| eval "Daily File" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File', 'Daily File', null())
| eval "Daily File" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File', 'Daily File', null())
| eval "Daily Instrum" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File' AND 'Daily File' <= 'Daily Instrum', 'Daily Instrum', null())

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust
| eval "Daily Po" = if('Daily AH' <= 'Daily Po', 'Daily Po', null())
| eval "Daily Risk" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk', 'Daily Risk', null())
| eval "Daily File" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File', 'Daily File', null())
| eval "Daily File" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File', 'Daily File', null())
| eval "Daily Instrum" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File' AND 'Daily File' <= 'Daily Instrum', 'Daily Instrum', null())
0 Karma

Edwin1471
Path Finder

Didn't work,  same result as before

 

 

My spl query below:

sourcetype=e143_csv, index="L134521_e143", PROCESS_ID = "FileDelivery" FREQ="DAY"
| rex field=PROCESS_NAME "(?P<Process>.*)for"
| eval Process=if(Process="Daily AHS Instrument File ","Daily AH",Process)
| eval Process=if(Process="Daily Position File ","Daily Po",Process)
| eval Process=if(Process="Daily Credit Risk Info File ","Daily Risk",Process)
| eval Process=if(Process="Daily CB File ","Daily File",Process)
| eval Process=if(Process="Daily Instrument File ","Daily Instrum",Process)
| eval "Daily Po" = if('Daily AH' <= 'Daily Po', 'Daily Po', null())
| eval "Daily Risk" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk', 'Daily Risk', null())
| eval "Daily File" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File', 'Daily File', null())
| eval "Daily File" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File', 'Daily File', null())
| eval "Daily Instrum" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File' AND 'Daily File' <= 'Daily Instrum', 'Daily Instrum', null())
| table Process, START_TIME, FINISH_TIME, RELATED_GRS_UNIT_ID
| sort -START_TIME, -FINISH_TIME, -Process, -RELATED_GRS_UNIT_ID
| dedup RELATED_GRS_UNIT_ID, Process sortby -FINISH_TIME
| xyseries Process, RELATED_GRS_UNIT_ID, FINISH_TIME
| transpose 0 header_field=Process

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
sourcetype=e143_csv, index="L134521_e143", PROCESS_ID = "FileDelivery" FREQ="DAY"
| rex field=PROCESS_NAME "(?P<Process>.*)for"
| eval Process=if(Process="Daily AHS Instrument File ","Daily AH",Process)
| eval Process=if(Process="Daily Position File ","Daily Po",Process)
| eval Process=if(Process="Daily Credit Risk Info File ","Daily Risk",Process)
| eval Process=if(Process="Daily CB File ","Daily File",Process)
| eval Process=if(Process="Daily Instrument File ","Daily Instrum",Process)
| table Process, START_TIME, FINISH_TIME, RELATED_GRS_UNIT_ID
| sort -START_TIME, -FINISH_TIME, -Process, -RELATED_GRS_UNIT_ID
| dedup RELATED_GRS_UNIT_ID, Process sortby -FINISH_TIME
| xyseries Process, RELATED_GRS_UNIT_ID, FINISH_TIME
| transpose 0 header_field=Process
| eval "Daily Po" = if('Daily AH' <= 'Daily Po', 'Daily Po', null())
| eval "Daily Risk" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk', 'Daily Risk', null())
| eval "Daily File" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File', 'Daily File', null())
| eval "Daily File" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File', 'Daily File', null())
| eval "Daily Instrum" = if('Daily AH' <= 'Daily Po' AND 'Daily Po' <= 'Daily Risk' AND 'Daily Risk' <= 'Daily File' AND 'Daily File' <= 'Daily Instrum', 'Daily Instrum', null())

gcusello
SplunkTrust
SplunkTrust

Hi @Edwin1471,

if all the values are all in the same event, you could crete some eval statements to check all the conditions:

<your_search>
| eval
   Status_AH_Po=if(Daily_AH<=Daily_Po,"Ok","Nok"),
   Status_Po_Risk=if(Daily_Po<=Daily_Risk,"Ok","Nok"),
   Status_Risk_File=if(Daily_Risk<=Daily_File,"Ok","Nok"),
   Status_File_Instrum=if(Daily_File<=Daily_Instrum,"Ok","Nok")
| table Daily_AH Status_AH_Po Daily_Po Status_Po_Risk Daily_Risk Status_Risk_File Daily_File Status_File_Instrum Daily_Instrum

Ciao.

Giuseppe

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...