topic Re: modify a query based on condition in Splunk Search

modify a query based on condition

dileepsri9 — Wed, 04 Apr 2018 06:41:55 GMT

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

Re: modify a query based on condition

splunker12er — Wed, 04 Apr 2018 07:15:54 GMT

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

Re: modify a query based on condition

mayurr98 — Wed, 04 Apr 2018 08:07:58 GMT

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!

Re: modify a query based on condition

dileepsri9 — Wed, 04 Apr 2018 08:31:56 GMT

@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

Re: modify a query based on condition

javiergn — Wed, 04 Apr 2018 08:57:19 GMT

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

Re: modify a query based on condition

dileepsri9 — Wed, 04 Apr 2018 11:36:03 GMT

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

Re: modify a query based on condition

niketn — Wed, 04 Apr 2018 13:01:45 GMT

@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

Re: modify a query based on condition

javiergn — Wed, 04 Apr 2018 13:21:53 GMT

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?

Re: modify a query based on condition

dileepsri9 — Wed, 04 Apr 2018 15:18:19 GMT

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.

Re: modify a query based on condition

dileepsri9 — Wed, 04 Apr 2018 15:22:13 GMT

@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

Re: modify a query based on condition

dileepsri9 — Thu, 05 Apr 2018 07:32:03 GMT

@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.