Splunk Search

modify a query based on condition

dileepsri9
Engager

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

Tags (3)
0 Karma

niketn
Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

dileepsri9
Engager

@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

0 Karma

javiergn
SplunkTrust
SplunkTrust

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:

alt text

Thanks,
J

0 Karma

dileepsri9
Engager

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
0 Karma

javiergn
SplunkTrust
SplunkTrust

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?

0 Karma

dileepsri9
Engager

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.

0 Karma

mayurr98
Super Champion

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!

0 Karma

dileepsri9
Engager

@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

0 Karma

splunker12er
Motivator

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

alt text

0 Karma

dileepsri9
Engager

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

0 Karma
Get Updates on the Splunk Community!

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...

Announcing General Availability of Splunk Incident Intelligence!

Digital transformation is real! Across industries, companies big and small are going through rapid digital ...