I am having lookup file with list of Jobs to be monitored. I want to create a table with the jobs name from lookup file (static) and starting time and ending time of those jobs.
I will run this report every hour, whenever i am running it I want my table to get updated with the start time and end time values.
Say example,
10.00AM Run
Job Name - Start Time- End time
Job1 -9:50
Job2
Job3
job4 - 9.10 -9.50
Job5
11:00AM Run
Job Name - Start Time- End time
Job1 -9:50 - 10:12
Job2 - 10:05 -10:20
Job3 - 10:15 - 10:55
job4 - 9:10 -9:50
Job5 - 10:50
12:00PM Run
Job Name - Start Time- End time
Job1 -9:50 - 10:12
Job2 - 10:05 -10:20
Job3 - 10:15 - 10:55
job4 - 9:10 -9:50
Job5 - 10:50 - 11:50
This is how i want to generate the table.
Note- The logs for these jobs will be having the jobs name and job status like Starting, Running, Success OR Failure. I am using the lookup file because i only want to monitor the jobs listed in the lookup file.
Thanks in Advance
start by creating the first lookup using search for example:
index = index sourcetype = sourcetype | table _time "Job Name" "Start Time" "End time" | outputlookup jobs.csv
now schedule this search every hour to update your lookup:
| inputlookup jobs.csv | append [ search index = index sourcetype = sourcetype | table _time "Job Name" "Start Time" "End time" | outputlookup jobs.csv ] | filter duplicates | outputlookup jobs.csv
filter duplicates if needed
another answers on this subject here:
https://answers.splunk.com/answers/236903/how-to-update-a-lookup-table-using-a-scheduled-sea.html
https://answers.splunk.com/answers/514910/store-everyday-data-into-spreadsheet-append-data-o.html#an...
hope it helps
Since you want to keep updating the lookup table, consider using KV Store which is primarily designed for this use-case. Each Row for a job will have a primary key and you would be able to use that update the fields like StartTime/EndTime. See these for more information.
http://docs.splunk.com/Documentation/Splunk/latest/Knowledge/ConfigureKVstorelookups
http://dev.splunk.com/view/webframework-developapps/SP-CAAAEZH
https://answers.splunk.com/answers/499996/how-to-update-a-kv-store-field.html
start by creating the first lookup using search for example:
index = index sourcetype = sourcetype | table _time "Job Name" "Start Time" "End time" | outputlookup jobs.csv
now schedule this search every hour to update your lookup:
| inputlookup jobs.csv | append [ search index = index sourcetype = sourcetype | table _time "Job Name" "Start Time" "End time" | outputlookup jobs.csv ] | filter duplicates | outputlookup jobs.csv
filter duplicates if needed
another answers on this subject here:
https://answers.splunk.com/answers/236903/how-to-update-a-lookup-table-using-a-scheduled-sea.html
https://answers.splunk.com/answers/514910/store-everyday-data-into-spreadsheet-append-data-o.html#an...
hope it helps