topic Add a Date field to a ref table that doesn't have a date field that gets updated once a month so that it will can be used as a subquery in Splunk Search

Add a Date field to a ref table that doesn't have a date field that gets updated once a month so that it will can be used as a subquery

pparkerntx99 — Thu, 04 Sep 2014 14:36:44 GMT

Howdy from Dallas Texas,
I have an employee info table that gets indexed in splunk once a month and has no date field.
This table is used extensively as Subsearch to define specific subsets of employees.
However my problem is that since the table only has a timestamp of when it is loaded each month I have to use custom date for the subsearch from the date range (i.e., earliest=-45d) to include the employee file in my main search.

I have already tried to do a field extraction of the time to add to my index but it did not seem to work.
I'm sure that there is an easy solution but I'm not very experienced with Splunk so Your suggestions/recommendations would be greatly appreciated.
Thanks

Re: Add a Date field to a ref table that doesn't have a date field that gets updated once a month so that it will can be used as a subquery

musskopf — Fri, 05 Sep 2014 00:31:06 GMT

So, Splunk is timebased... I do have similar situations here but I don't see as a problem to use "earliest=-45d" in the subsearch. I normally include a bigger period, lets say that covers 2 or 3 imports, and use a "dedup" to make user I get the last record.

The other alternative is to export the employee data as a lookup table. You could use it in a lookup format or using "inputlookup" command. In both cases, there is no "date"... like that:

index=main <your search> [ inputlookup employees.csv name="John" | return id=employee_id ]

Let me know if that helps.

Re: Add a Date field to a ref table that doesn't have a date field that gets updated once a month so that it will can be used as a subquery

lguinn2 — Sat, 06 Sep 2014 23:53:10 GMT

Splunk is really designed to index "events." Events are a record of something interesting that happened at a particular time. For the employee info data, I recommend that you use a lookup. Lookups are fast, and you don't need a sub-search, which will make your searches less complicated. You also don't need to mess with date ranges if you use lookups.

You will need to upload your employee info data to Splunk as a CSV file. You can update the file at will. (It's just a CSV in a particular directory on the Splunk server.)

Here is the best place to learn more, it is a tutorial on lookups: Use Field Lookups