topic Re: Will the below search work. in Splunk Search

Will the below search work.

tvon1990 — Tue, 29 Sep 2020 20:04:19 GMT

|inputlookup lookup |map [ search index=index ESP_APPLICATION=$ESP_Application$
|eval Actual_Start_Time='[search index=index ESP_JOB=$Start_Job$ JOB_STATUS=EXEC|stats count as mycount first(_time) as _time |eval _time=if(mycount=0,0,_time)|return $_time]'
|eval Actual_End_Time='[search index=index ESP_JOB=$End_Job$ JOB_STATUS=COMPLETE|stats count as mycount first(_time) as _time |eval _time=if(mycount=0,0,_time)|return $_time]'
]

Re: Will the below search work.

tvon1990 — Tue, 29 Sep 2020 20:04:22 GMT

I need to pass the variables Start_Job and End_Job to the search inside the eval command and return the results.

Re: Will the below search work.

richgalloway — Sun, 17 Jun 2018 22:33:15 GMT

Have you tried this query? What you mean by "work"? What results do you expect?

I can tell you the map command will fail because it's expecting either the name of a saved search or the "search" keyword.

Re: Will the below search work.

tvon1990 — Tue, 29 Sep 2020 20:05:33 GMT

Hi This works if I hard code the values of the $Start_Job$ and $End_Job$ in the query. And if I replace it with variables the results for the column alone is not populating. Is there a way to send the values to the search inside the eval command.

Re: Will the below search work.

richgalloway — Thu, 21 Jun 2018 16:25:17 GMT

Where are you getting the variables? They should be coming from input fields in your dashboard or passed as part of a drilldown.

Re: Will the below search work.

tvon1990 — Fri, 22 Jun 2018 01:25:10 GMT

I'm passing the variables via the map command.

Re: Will the below search work.

richgalloway — Mon, 25 Jun 2018 12:36:47 GMT

But where are the variables coming from? Are they in your lookup file or somewhere else?

Re: Will the below search work.

DalJeanis — Tue, 29 Sep 2020 20:11:36 GMT

Okay, you've started at the wrong end of the question. When you post a naked search, and ask if it will work, all we will be able tot tell you is what you could find out by test-running the search: whether it is syntactically correct.

The question you really want to ask us is whether it will solve your use case... and you haven't told us what that is.

I can tell you that the search you posted looks very complicated and not well thought out. map should be avoided when possible, and it's almost always possible.

Also, your subsearches are at least two deep, so I'd say, "No, that's not the best way to do whatever you are trying to do."

Please update your question to describe what you are trying to do. NOT the method that you tried to do it, but what information you have, and what information you are trying to get.

Given the assumptions that you have an input lookup called mylookup.csv with fields ESP_Application, Start_Job and End_Job, this might be a workable approach...

index=foo ESP_JOB=* (JOB_STATUS="EXEC" OR JOB_STATUS="COMPLETE")

| rename COMMENT as "Roll up all the records of each type to get the first one"
| stats count as mycount first(_time) as _time by index ESP_JOB JOB_STATUS ESP_APPLICATION

| rename COMMENT as "If this is a start record, read the lookup to see if it is the right start job for the application"
| eval Start_Job=case(JOB_STATUS="EXEC",ESP_JOB) 
| lookup myLookup.csv Start_Job ESP_Application as ESP_APPLICATION  OUTPUT Start_Job as foundStart

| rename COMMENT as "If this is an end record, read the lookup to see if it is the right end job for the application"
| eval end_Job=case(JOB_STATUS="COMPLETE",ESP_JOB)
| lookup myLookup.csv End_Job ESP_Application as ESP_APPLICATION  OUTPUT End_Job as foundEnd 

| rename COMMENT as "Kill all records that are not wanted, then drop the flags"
| where isnotnull(foundStart) OR isnotnull(foundEnd)
| fields - foundStart  foundEnd

| rename COMMENT as "Format the proper time and count for each record"
| eval Actual_Start_Time=case(JOB_STATUS="EXEC",_time)
| eval startCount = case(JOB_STATUS="EXEC",mycount)
| eval Actual_End_Time=case(JOB_STATUS="COMPLETE",_time)
| eval endCount = case(JOB_STATUS="COMPLETE",mycount)

| stats 
     first(Actual_Start_Time) as Actual_Start_Time 
     values(Start_Job) as Start_Job
     first(Actual_End_Time) as Actual_End_Time 
     values(End_Job) as End_Job
     by ESP_APPLICATION

At this point, if the start or end jobs were not found for an application, those columns will be blank.

Re: Will the below search work.

tvon1990 — Tue, 29 Sep 2020 20:11:50 GMT

hi DalJeanis,

I have a requirement of sending a job status report daily to the clients.
Here in the lookup I have the fields Application Start_Time, ENd_Time, Start_Job and End_Job(I have 21 entries).
And I'm indexing the scheduler logs which will have the start and end events of the job. I'm trying to dynamically populate the start_time and End_time using the eval command. If there is any other approach kindly let me know.

Re: Will the below search work.

DalJeanis — Tue, 03 Jul 2018 14:54:13 GMT

@tvon1990 - what exactly does the Start_Time and End_Time in the lookup table mean, and what are you using them for? If it's a lookup, then presumably it is a relatively fixed set of data, so we assume it is the "regular start time" and "expected end time" of the application. Based on that assumption, we've updated the lookup and stats in the code below, to retain that information.

index=foo ESP_JOB=* (JOB_STATUS="EXEC" OR JOB_STATUS="COMPLETE")
 | rename COMMENT as "Roll up all the records of each type to get the first one in the selected time range"
 | stats count as mycount first(_time) as _time by index ESP_JOB JOB_STATUS ESP_APPLICATION

 | rename COMMENT as "If this is a start record, read the lookup to see if it is the right start job for the application, also get expected start and end times"
 | eval Start_Job=case(JOB_STATUS="EXEC",ESP_JOB) 
 | lookup myLookup.csv Start_Job ESP_Application as ESP_APPLICATION  OUTPUT Start_Job as foundStart Start_Time as Scheduled_Start_Time  End_Time as Scheduled_End_Time

 | rename COMMENT as "If this is an end record, read the lookup to see if it is the right end job for the application"
 | eval end_Job=case(JOB_STATUS="COMPLETE",ESP_JOB)
 | lookup myLookup.csv End_Job ESP_Application as ESP_APPLICATION  OUTPUT End_Job as foundEnd 

 | rename COMMENT as "Kill all records that are not wanted, then drop the flags"
 | where isnotnull(foundStart) OR isnotnull(foundEnd)
 | fields - foundStart  foundEnd

 | rename COMMENT as "Format the proper time and count for each record"
 | eval Actual_Start_Time=case(JOB_STATUS="EXEC",_time)
 | eval startCount = case(JOB_STATUS="EXEC",mycount)
 | eval Actual_End_Time=case(JOB_STATUS="COMPLETE",_time)
 | eval endCount = case(JOB_STATUS="COMPLETE",mycount)
 | stats 
      first(Scheduled_Start_Time) as Scheduled_Start_Time 
      first(Actual_Start_Time) as Actual_Start_Time 
      values(Start_Job) as Start_Job
      first(Scheduled_End_Time) as Scheduled_End_Time 
      first(Actual_End_Time) as Actual_End_Time 
      values(End_Job) as End_Job
      by ESP_APPLICATION

Re: Will the below search work.

woodcock — Mon, 16 Jul 2018 02:11:29 GMT

It would REALLY help if you told us what you are trying to accomplish. In any case, something like this would be better:

index=index [|inputlookup YourLookup| table ESP_Application | rename ESP_Application AS ESP_APPLICATION ]
| stats min(_time) AS Actual_Start_Time max(_time) AS Actual_End_Time BY ESP_APPLICATION
| lookup YourLookup ESP_Application AS ESP_APPLICATION OUTPUT Start_Job End_Job