How can I create an "if" command where one date is greater than the other?

[jackreeves]

Hi,

I am trying to use an eval if function with a stats command. I am having an issue creating an IF command where one date is greater than the other. I have checked the raw data & there are instances of where there is a VOICE_Date that has a timestamp that is earlier than the LATEST_WP_DATE.

What I essentially want is to return the first VOICE_Date after the LATEST_WP_Date, ignoring everything before & after.

EXAMPLE of Data:

ordernum       LATEST_WP_Date             VOICE_Date
12345               2018-02-13 11:00:00.0    2018-02-06 14:33:00.0
                                                                     2018-02-07 13:05:00.0
                                                                     2018-02-07 15:15:00.0
                                                                     2018-02-09 08:20:00.0
                                                                     2018-02-09 12:32:00.0
                                                                     2018-02-12 10:12:00.0
                                                                     2018-02-12 10:19:00.0
                                                                     2018-02-12 13:26:00.0
                                                                     2018-02-13 11:35:00.0

In this case I would want a new column called LATEST_VOICE displaying the VOICE_Date that is after the Latest_WP_Date which is 2018-02-13 11:35:00.

Syntax below:

| stats latest(eval(if(SRP_ENTRY_TYPE="w" OR SRP_TEXT LIKE "%AWP0%" OR SRP_TEXT LIKE "%@@WP%",SRP_DATE,NULL))) as LATEST_WP_Date values(eval(if(SRP_TEXT LIKE "%@@VOICE%",SRP_DATE,NULL))) as VOICE_Date earliest(eval(if("VOICE_Date">"LATEST_WP_Date",VOICE_Date,NULL))) as LATEST_VOICE by ordernum

[somesoni2]

For easier readability, I suggest to move eval out of stats and be done before, like this

index=progress_text sourcetype=all_sword_live
| fillnull value=Open SER_CLOSED 
| search (SER_IN_PROGRESS=1 AND SER_CLOSED=Open AND SER_CONSOL_PRI=PRI1) 
| eval ORIGINAL_WP_Date=if(SRP_ENTRY_TYPE="w" OR SRP_TEXT LIKE "%AWP0%" OR SRP_TEXT LIKE "%@@WP%",SRP_DATE,null())
| eval LATEST_WP_Date=if(SRP_ENTRY_TYPE="w" OR SRP_TEXT LIKE "%AWP0%" OR SRP_TEXT LIKE "%@@WP%",SRP_DATE,null())
| eval VOICE_Date=if(SRP_TEXT LIKE "%@@VOICE%",SRP_DATE,null())
| eval LATEST_VOICE=if('VOICE_Date'>'LATEST_WP_Date',VOICE_Date,null())
| stats values(SER_ENTDATE) AS ENTRY_DATE earliest(ORIGINAL_WP_Date) as ORIGINAL_WP_Date latest(LATEST_WP_Date) as LATEST_WP_Date list(VOICE_Date) as VOICE_Date earliest(LATEST_VOICE) as LATEST_VOICE by ordernum customer

Check eval in line 4 to line 7 where you want it to default to null or literal string "NULL".

Change for LATEST_VOICE eval was to enclose the field names in single quotes (right side of |eval fieldname= and |where requires field names to be in single quotes if it has special characters. Double quoted field name would be treated as literal string not fields). If Line 7 eval doesn't work, may be due to not able to compare string dates, then replace it with this

 | eval LATEST_VOICE=if(strptime('VOICE_Date',"%Y-%m-%d %H:%M:%S.%N")>strptime('LATEST_WP_Date',"%Y-%m-%d %H:%M:%S.%N",VOICE_Date,null())

[jackreeves]

Really appreciate your help.

Tried first scenario on Line 7 and it failed so i've attempted to use:
| eval LATEST_VOICE=if(strptime('VOICE_Date',"%Y-%m-%d %H:%M:%S.%N")>strptime('LATEST_WP_Date',"%Y-%m-%d %H:%M:%S.%N"),VOICE_Date,null())

Noticed that the above eval was missing a ")" before ,VOICE_Date. I've now added this in. However still getting a blank field for LATEST_VOICE

[somesoni2]

Try this

| eval LATEST_VOICE=if(strptime('VOICE_Date',"%Y-%m-%d %H:%M:%S.%N")>strptime('LATEST_WP_Date',"%Y-%m-%d %H:%M:%S.%N"),VOICE_Date,null())

[jackreeves]

Added to query but still doesn't produce any results

[somesoni2]

How about this

index=progress_text sourcetype=all_sword_live
 | fillnull value=Open SER_CLOSED 
 | search (SER_IN_PROGRESS=1 AND SER_CLOSED=Open AND SER_CONSOL_PRI=PRI1) 
 | eval ORIGINAL_WP_Date=if(SRP_ENTRY_TYPE="w" OR SRP_TEXT LIKE "%AWP0%" OR SRP_TEXT LIKE "%@@WP%",SRP_DATE,null())
 | eval LATEST_WP_Date=if(SRP_ENTRY_TYPE="w" OR SRP_TEXT LIKE "%AWP0%" OR SRP_TEXT LIKE "%@@WP%",SRP_DATE,null())
 | eval VOICE_Date=if(SRP_TEXT LIKE "%@@VOICE%",SRP_DATE,null())
 | mvexpand VOICE_Date
| eval LATEST_VOICE=if(strptime('VOICE_Date',"%Y-%m-%d %H:%M:%S.%N")>strptime('LATEST_WP_Date',"%Y-%m-%d %H:%M:%S.%N"),VOICE_Date,null())
 | stats values(SER_ENTDATE) AS ENTRY_DATE earliest(ORIGINAL_WP_Date) as ORIGINAL_WP_Date latest(LATEST_WP_Date) as LATEST_WP_Date list(VOICE_Date) as VOICE_Date earliest(LATEST_VOICE) as LATEST_VOICE by ordernum customer

[jackreeves]

Adding mvexpand results in the ORIGINAL_WP_Date & LATEST_WP_Date to display as blank, also LATEST_VOICE still remains blank.

[somesoni2]

Can you post your full search? The field ordernum doesn't seems to be available in each row, is it a typo or values for field LATEST_WP_DATE and VOICE_Date are multivalued?

[jackreeves]

VOICE_Date is multivalued

index=progress_text sourcetype=all_sword_live
| fillnull value=Open SER_CLOSED
| search (SER_IN_PROGRESS=1 AND SER_CLOSED=Open AND SER_CONSOL_PRI=PRI1)
| stats values(SER_ENTDATE) AS ENTRY_DATE earliest(eval(if(SRP_ENTRY_TYPE="w" OR SRP_TEXT LIKE "%AWP0%" OR SRP_TEXT LIKE "%@@WP%",SRP_DATE,NULL))) as ORIGINAL_WP_Date latest(eval(if(SRP_ENTRY_TYPE="w" OR SRP_TEXT LIKE "%AWP0%" OR SRP_TEXT LIKE "%@@WP%",SRP_DATE,NULL))) as LATEST_WP_Date list(eval(if(SRP_TEXT LIKE "%@@VOICE%",SRP_DATE,NULL))) as VOICE_Date earliest(eval(if("VOICE_Date">"LATEST_WP_Date",VOICE_Date,NULL))) as LATEST_VOICE by ordernum customer

[jackreeves]

Sorry - accidentally deleted your comment, could you repost.

[harsmarvania57]

Try something like this

| stats latest(eval(if(SRP_ENTRY_TYPE="w" OR SRP_TEXT LIKE "%AWP0%" OR SRP_TEXT LIKE "%@@WP%",SRP_DATE,NULL))) as LATEST_WP_Date values(eval(if(SRP_TEXT LIKE "%@@VOICE%",SRP_DATE,NULL))) as VOICE_Date by ordernum
| stats values(LATEST_WP_DATE) AS LATEST_WP_DATE values(VOICE_Date) AS VOICE_Date earliest(eval(if(VOICE_Date>LATEST_WP_Date,VOICE_Date,"NULL"))) as LATEST_VOICE by ordernum

EDIT: Updated query.

[jackreeves]

Thanks.

Unfortunately I still get a blank column for LATEST_VOICE