Merging search results into lookup file

[Kwip]

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

[somesoni2]

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

[adonio · Accepted solution]

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