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!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...