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

[Edwin1471]

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.  

[ITWhisperer]

| 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())

[Edwin1471]

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

[ITWhisperer · Accepted solution]

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]

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