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
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())
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
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())
Added to query but still doesn't produce any results
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
Adding mvexpand results in the ORIGINAL_WP_Date & LATEST_WP_Date to display as blank, also LATEST_VOICE still remains blank.
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?
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
Sorry - accidentally deleted your comment, could you repost.
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.
Thanks.
Unfortunately I still get a blank column for LATEST_VOICE