- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I have two searches that I am trying to combine into one and keep running into roadblocks. Preferably, I would be able to accomplish the task without using transactions (second search). My goals are:
- See Jobs that are created, but never completed in the last 10 days
- Get the duration of current jobs in hours
My first search gives me the jobs completed/created, but it leaves out jobs created prior to the time window and is missing job duration.
.....search.... EventDescription="Job Created" OR EventDescription="Job Completed"
| stats values(EventDescription) AS Event, list(_time) as Time by Job_ID
| eval Time=strftime(Time, "%m-%d-%y %H:%M:%S")
My second search gives me duration in hours, but uses the expensive transaction command and doesn't tell me about jobs that got created but never completed during the window.
....search .... EventDescription="Job Created" OR EventDescription="Job Completed"
| transaction Job_ID startswith="Job Created" endswith="Job Completed" | where duration>0
| eval StartTime=strftime(_time, "%m-%d-%y %H:%M:%S"), EndTime=strftime(_time+duration, "%m-%d-%y %H:%M:%S")
| eval JobDuration = floor(duration/60/60)." Hours " | table Job_ID StartTime EndTime JobDuration
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Personally, I feel that transaction
deserves some love. I know it is an expensive command, but it is also a powerful one with some very useful features. If you decide to keep using transaction
, you can simply add the keepevicted=t
flag to the transaction
command to get the information you want.
It will add a closed_txn
field which denotes transactions that were:
- Opened and closed (both the startswith and endswith conditions were met). In this case,
closed_txn=1
- Only opened (only the startswith condition was met and therefore the transaction was evicted). In this case,
closed_txn=0
So to find jobs that started but didn't complete in your time window, add | search closed_txn=0
to your search after the transaction
command. If the search is running too slowly, use fields
to reduce your field extractions prior to running your transaction. The whole thing might look something like this:
....search .... EventDescription="Job Created" OR EventDescription="Job Completed"
| fields Job_ID
| transaction Job_ID startswith="Job Created" endswith="Job Completed" keepevicted=t | where duration>0
| eval Job_Status=if(closed_txn=0, "Incomplete", "Complete")
| eval StartTime=strftime(_time, "%m-%d-%y %H:%M:%S"), EndTime=strftime(_time+duration, "%m-%d-%y %H:%M:%S")
| eval JobDuration = floor(duration/60/60)." Hours "
| table Job_ID Job_Status StartTime EndTime JobDuration
If interested in this approach, have a look at keepevicted
in the docs here.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Personally, I feel that transaction
deserves some love. I know it is an expensive command, but it is also a powerful one with some very useful features. If you decide to keep using transaction
, you can simply add the keepevicted=t
flag to the transaction
command to get the information you want.
It will add a closed_txn
field which denotes transactions that were:
- Opened and closed (both the startswith and endswith conditions were met). In this case,
closed_txn=1
- Only opened (only the startswith condition was met and therefore the transaction was evicted). In this case,
closed_txn=0
So to find jobs that started but didn't complete in your time window, add | search closed_txn=0
to your search after the transaction
command. If the search is running too slowly, use fields
to reduce your field extractions prior to running your transaction. The whole thing might look something like this:
....search .... EventDescription="Job Created" OR EventDescription="Job Completed"
| fields Job_ID
| transaction Job_ID startswith="Job Created" endswith="Job Completed" keepevicted=t | where duration>0
| eval Job_Status=if(closed_txn=0, "Incomplete", "Complete")
| eval StartTime=strftime(_time, "%m-%d-%y %H:%M:%S"), EndTime=strftime(_time+duration, "%m-%d-%y %H:%M:%S")
| eval JobDuration = floor(duration/60/60)." Hours "
| table Job_ID Job_Status StartTime EndTime JobDuration
If interested in this approach, have a look at keepevicted
in the docs here.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Both your answer and @Runal worked. Because it was a smaller subset, I ended up sticking with transactions. The fancy evals from @Runal inspired me to improve my dashboard panel!. I had to remove duration>0 in order to see jobs that had started but not finished. I really like the idea of | eval Job_Status=if(closed_txn=0, "Incomplete", "Complete"), however it was showing complete for jobs that had started but not finished.
....search... + EventDescription="Job Created" OR EventDescription="Job Completed"
| transaction Job_ID startswith="*Job Created" endswith="Job Completed" keeporphans=true
| eval since=round((now()-_time)/3600, 1), StartTime=strftime(_time, "%m-%d-%y %H:%M:%S"), EndTime=strftime(_time+duration, "%m-%d-%y %H:%M:%S")
| eval JobDuration = round(duration/3600, 1)." Hours ", EndTime=if(isnull(EndTime),"Job Currently Running",EndTime), JobDuration=if(isnull(JobDuration),"Currently running time is: ". since ." Hours",JobDuration)
| sort -_time | table Job_ID StartTime EndTime JobDuration
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Don't get me wrong. I do love and use the transaction command; it is very powerful and versatile. When it comes to just getting the duration of a job and depending on the volume of your data and timeframe of your search though clever use of stats can get you to the same ends with a little more efficiency.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Absolutely, I agree that stats is more efficient. I'm just a fan of transaction and thought to spread the word 🙂
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I similarly try to avoid transaction command when possible. I'd try something like this as a base
EventDescription="Job Created" OR EventDescription="Job Completed" | stats count min(_time) as JobCreated max(_time) as JobCompleted range(_time) as Duration by Job_ID
I think range will supply the requisite duration but if not I'll add a secondary eval. At any rate you could spruce up your search like the following.
EventDescription="Job Created" OR EventDescription="Job Completed" | stats count min(_time) as JobCreated max(_time) as JobCompleted range(_time) as Duration by Job_ID | eval Completed = if(count=2, "Yes", "No") | eval backupDuration = JobCompleted - JobCreated | eval moFancyDurationEval = case(JobCompleted - JobCreated > 3600 AND Completed="Yes", round((JobCompleted - JobCreated) / 3600, 1) . " hrs", JobCompleted - JobCreated < 3600 AND Completed="Yes", round((JobCompleted - JobStarted) / 60, 1) . " min", 1=1, "Job not completed. Beat the hampsters for better performance")
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this...
....search ... | reverse | streamstats window=2 current=f range(_time) as dur latest(_time) as end earliest(_time) as start by jobid | search "Job Created" | table _time, jobid, start, end, dur