Why are the eval fields blank after join?

[intelli2019]

Hi,
I have the query below which involves 2 joins. I know joins are not the best way but I'm a Splunk noob and there is a bit of time pressure 🙂

The top section before the "=======" works fine. However the bottom section is misbehaving. It's meant to calculate the "ESLA_Total" time which is "ESLA File End" minus "ESLA_Start" but have found that "ESLA Total" is returning blank.

I think it's something to do with the "ESLA File End" field being in a join subsearch but can't figure it out!

Any ideas?

index=esla | bucket _time span=1d as Day | stats earliest(_time) as ESLA_Start latest(_time) as ESLA_End by Day
| eval ESLA_Start=strftime(ESLA_Start,"%H:%M:%S") 
| eval ESLA_End=strftime(ESLA_End,"%H:%M:%S") 
| eval Day=strftime(Day,"%d/%m/%Y")
| join Day [search index=mule-new State=START Service="Early SLA" | bucket _time span=1d as Day | stats values(FileTime) as "ESLA File Start" by Day | eval Day=strftime(Day,"%d/%m/%Y")]
| join Day [search index=mule-new State=END Service="Early SLA" | bucket _time span=1d as Day | stats values(FileTime) as "ESLA File End" by Day | eval Day=strftime(Day,"%d/%m/%Y")]

===================================================================================

| eval st = strptime(ESLA_Start,"%H:%M:%S") | eval et = strptime("ESLA File End","%H:%M:%S") | eval diff = et - st | eval "ESLA_Total" = tostring(diff, "duration")
| fields Day ESLA_Start ESLA_End "ESLA File Start" "ESLA File End" "ESLA_Total"

[woodcock]

How is this different than this question? https://answers.splunk.com/answers/768911/eval-total-duration-in-minutes-across-2-indexes.html#answe...

You have multiple answers over there, both of which eliminate join, which is the heart of all of your problems.

[intelli2019]

Hi Woodcock.
I actually asked this same question of someone on Fiverr and he came on here and asked you guys! 🙂
That's why we have nearly identical questions from different users.
Any how thanks for you input I think this one is solved now.

[intelli2019]

Hi somesoni2. Your first query is a lot faster than my joins so I would like to use it but I have the issue mentioned below...

The ESLA_End field is picking up the "ESLA File End" value as it is the latest event time for that Day.

How can I get the ESLA_End time instead? It will fall after the ESLA_Start but before the "ESLA File End"

| stats earliest(_time) as ESLA_Start latest(_time) as ESLA_End values(fileStart) as "ESLA File Start" values(fileEnd) as "ESLA File End" by Day

If you get time to look at this bit I would really appreciate it!

[somesoni2]

It's because of the field names you have. If a field name contains anything other than alphanumeric characters and underscore, they need to be enclosed with single quotes in expression section of eval and where (and withing double quotes everywhere else)., so try this for your section after =======

| eval st = strptime(ESLA_Start,"%H:%M:%S") | eval et = strptime('ESLA File End',"%H:%M:%S") | eval diff = et - st | eval "ESLA_Total" = tostring(diff, "duration")
 | fields Day ESLA_Start ESLA_End "ESLA File Start" "ESLA File End" "ESLA_Total"

Removing join

Try something like this (by merging queries in same base search).

index=esla OR (index=mule-new  Service="Early SLA" (State=START OR State=END) )
| eval Day=strftime(_time,"%d/%m/%Y")
| eval ESLA_time=if(index="esla",_time,null())
| eval fileStart=if(index="mule-new" AND State="START", FileTime, null())
| eval fileEnd=if(index="mule-new" AND State="END", FileTime, null())
| stats earliest(_time) as ESLA_Start latest(_time) as ESLA_End values(fileStart) as "ESLA File Start"  values(fileEnd) as "ESLA File End" by Day
| eval ESLA_Start=strftime(ESLA_Start,"%H:%M:%S") 
| eval ESLA_End=strftime(ESLA_End,"%H:%M:%S") 
| eval st = strptime(ESLA_Start,"%H:%M:%S") 
| eval et = strptime('ESLA File End',"%H:%M:%S") | eval diff = et - st | eval "ESLA_Total" = tostring(diff, "duration")
| fields Day ESLA_Start ESLA_End "ESLA File Start" "ESLA File End" "ESLA_Total"

OR by replacing join with append stats like this

index=esla |  eval Day=strftime(_time,"%d/%m/%Y") | stats earliest(_time) as ESLA_Start latest(_time) as ESLA_End by Day
 | eval ESLA_Start=strftime(ESLA_Start,"%H:%M:%S") 
 | eval ESLA_End=strftime(ESLA_End,"%H:%M:%S")  
 | append [search index=mule-new State=START Service="Early SLA" |  eval Day=strftime(_time,"%d/%m/%Y") | stats values(FileTime) as "ESLA File Start" by Day ]
 | append [search index=mule-new State=END Service="Early SLA" |  eval Day=strftime(_time,"%d/%m/%Y") | stats values(FileTime) as "ESLA File End" by Day ]
| stats values(*) as * by Day
 | eval st = strptime(ESLA_Start,"%H:%M:%S") | eval et = strptime("ESLA File End","%H:%M:%S") | eval diff = et - st | eval "ESLA_Total" = tostring(diff, "duration")
 | fields Day ESLA_Start ESLA_End "ESLA File Start" "ESLA File End" "ESLA_Total"

[intelli2019]

I got rid of the nulls myself using the line below.

| fields Day ESLA_Start ESLA_End "ESLA File Start" "ESLA File End" "ESLA_Total" | where 'ESLA File End' !=""

Many thanks again!

[intelli2019]

Actually there is a flaw in the stats line below. The ESLA_End field is picking up the "ESLA File End" value as it is the latest event time for that Day.

How can I get the ESLA_End time instead? It will fall after the ESLA_Start but before the "ESAL File End"

 | stats earliest(_time) as ESLA_Start latest(_time) as ESLA_End values(fileStart) as "ESLA File Start"  values(fileEnd) as "ESLA File End" by Day

Cheers

[nathc100]

Sorry this is me Intelli2019. I also have the Splunk Answers account nathc100

[nathc100]

Wow! It works just by replacing the double quotes with singles in the eval, who knew 🙂

Your 2nd and third answers removing the joins work too. Which leads me to believe you must know what you're talking about!

Last minor things before awarding well deserved points. How do I get rid of rows/days where "ESLA File End" is null?

Also would you recommend the first query you gave or the second append stats one to replace the joins?

So many thanks!