Hi,
I have created a query which gives me date, and start and end time of a job in the below format.
Date Start End
04/04/2018 04:04:04 05:05:05
04/03/2018 04:03:48 05:15:05
04/02/2018 04:04:02 05:01:05
03/30/2018 04:08:04 05:05:25
03/29/2018 04:10:14 05:25:65
I want to get the latest run of this set. example: on 04/05/2018, I should get 04/04 data. But for 04/02, I should be seeing 03/30 since that was the latest run at that point of time. This is because of Monday and other days of the job run.
This is the output I am looking for :
on 04/05:
Date Start End
04/04/2018 04:04:04 05:05:05
on 04/02:
Date Start End
03/30/2018 04:08:04 05:05:25
I know that I need to check if the day is Monday, then I need to look for last business day which would be Friday, if its not Monday, then look for previous Business day which would be the day prior. But I am not able to put this logic in Splunk.
Here is what I have tried, but I am not sure how to get my condition checked.
..|eval epochtime=strptime(ProcessDate,"%Y/%m/%d") | eval dayofweekfull=strftime(epochtime,"%A") | eval checking=if(dayofweekfull="Monday","I am not sure how I can check this here as this is text and not a field that I can initialize","same here")
Please let me know if I am too confused for a easy problem
@dileepsri9, do you want to do this in Search or Dashboard?
Refer to one of my previous answer on similar lines (with run anywhere example), which was for dashboard: https://answers.splunk.com/answers/620104/how-to-set-my-default-time-to-previous-business-da-1.html
@niketnilay,I am looking for this exactly, but this one looks very complex for me as I am a starter. Could you please explain me a bit more so that I can incorporate this for my requirement. On what variables should I be using and how should these need to be changed
Hi,
I have replicated your sample above with a csv so you can ignore the first line.
In any case, instead of using string values indicating the day of the week, I am simply sort by epoch time (ascendant) and then using streamstats to display the previous value (window=1, current=false). Let me know if that helps or I didn't fully understand your request:
| inputcsv mycsv.csv
| eval epochtime=strptime(Date,"%m/%d/%Y")
| eval dayofweekfull=strftime(epochtime,"%A")
| sort +epochtime
| streamstats window=1 current=f values(End) as LatestRun_End values(Start) as LatestRun_Start
| sort -epochtime
| table Date, Start, End, LatestRun_Start, LatestRun_End
Output:
Thanks,
J
Hi J, as your output says, I don't need the full output. I just need the latest run details. So if I am running my query I just need to see 04/04 details as on 04/04/2018. my output should look something like this.
Date Start End
04/04/2018 04:04:04 05:05:05
Then you can simply limit the latest sort to only return the top 1 and adjust the table command to display the fields you want, as in:
| sort limit=1 -epochtime
| table YOUR LIST OF FIELDS HERE
Other than that, is the logic in my previous comment what you were expecting to see?
But if the job is not started yet on Tuesday morning, it will be returning Friday's run where it should be waiting for Monday's run.
Hey try this run anywhere search,
| makeresults | eval Date="04/04/2018",Start="04:04:04",End="05:05:05"|append [| makeresults | eval Date="04/03/2018",Start="04:03:48",End="05:15:05"]|append [| makeresults | eval Date="04/02/2018",Start="04:04:02",End="05:01:05"]|append [| makeresults | eval Date="03/30/2018",Start="04:08:04",End="05:05:25"]|append [| makeresults | eval Date="03/29/2018",Start="04:10:14",End="05:25:65"]|eval condition="04/02/2018"| eval condition1=strftime(strptime(condition,"%m/%d/%Y"),"%A")| eval condition2=if(condition1="Monday",relative_time(strptime(condition,"%m/%d/%Y"),"-3d@d"),relative_time(strptime(condition,"%m/%d/%Y"),"-1d@d")) | eval condition2=strftime(condition2,"%m/%d/%Y") | where Date=condition2 | table Date End Start
In your environment, you should try
<your query with Columns Date Start End>|eval condition="04/02/2018"| eval condition1=strftime(strptime(condition,"%m/%d/%Y"),"%A")| eval condition2=if(condition1="Monday",relative_time(strptime(condition,"%m/%d/%Y"),"-3d@d"),relative_time(strptime(condition,"%m/%d/%Y"),"-1d@d")) | eval condition2=strftime(condition2,"%m/%d/%Y") | where Date=condition2 | table Date End Start
Put your date in the conditon
field and you will see the desired results. You can also make a token
to pass date
to condition field
.
let me know if this helps!
@mayurr98, sorry maybe my statement was misunderstood, I want to check the current date and if it is Monday, then give Friday's value, if Tuesday give Monday's value, if Wednesday give Tuesday's value and so on, not that looking at the data from table
Try something like this, you might want to modify |where clause condition as per your validation
|inputlookup time.csv| eval Date1=Date |eval epoch=strptime(Date,"%m/%d/%Y")|eval check_date="04/04/2018"|eval check_date_epoch=strptime(check_date,"%m/%d/%Y")|where epoch=check_date_epoch |stats last(Start) last(End) by Date
@Splunker12er, I am not looking for a hardcoded date, I am looking to check if today is Monday, retrieve Friday's run details, if its non Monday retrieve the previous day details.