Splunk Search

Merging search results into lookup file

Kwip
Contributor

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

0 Karma
1 Solution

adonio
Ultra Champion

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

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

adonio
Ultra Champion

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...