Splunk Enterprise

Lookup based table in Splunk?

snipedown21
Path Finder

I have a search that searches indexes for all time, and retrieves values(1 field) and stores it in a lookup. I figured that if I set the earliest time and latest time for the search from a config file and somehow update the config file on a day to day basis, I can make the search faster.
The problem here is that I need the lookup to populate values for "All time" only for the first time it runs. There on, it must run for the time specified in a file, let's say timeSettings.conf. I want to know if this is possible at all.

timeSettings.conf
earliestTime = .....
latestTime = .....

Thank you.
Cheers.

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi snipedown21,
why you don't store the earliest value in another field?
in other words
in your lookup there are two fields: index, earliest
the first run you put in your lookup:

index,earliest
start,0

.

your_search [| your_lookup.csv | stats latest(earliest) AS earliest) ] latest=now
| table index _time
| outputlookup your_lookup.csv

Bye.
Giuseppe

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi snipedown21,
why you don't store the earliest value in another field?
in other words
in your lookup there are two fields: index, earliest
the first run you put in your lookup:

index,earliest
start,0

.

your_search [| your_lookup.csv | stats latest(earliest) AS earliest) ] latest=now
| table index _time
| outputlookup your_lookup.csv

Bye.
Giuseppe

0 Karma

snipedown21
Path Finder

Hi Giuseppe.
I think I wasn't clear enough in the question. My apologies.
This is more like what I need.
I have a search which writes(appends) to a lookup. Let's just say it is all the roles in a school database.
The first time I run the search, I want it to run from 0 to "now".
The next time onwards, it should run from the date that was "now" , to the next day.
This data must be picked up from a file(dateMaintainer.csv) which holds earliest and latest values.
This file must get updated via a script or something.
The value of earliest must change to latest after the first run and the value of latest to the day it runs on.

e.g.
dateMaintainer.csv
earliest=0
latest=10/10/2017

after first run

dateMaintainer.csv
earliest=10/10/2017
latest=11/10/2017

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi snipedown21,
let me understand:

  • you have in a csv some rows with two informations: school_role, date (if there isn't this second field you can use the now time),
  • this csv is updated daily,
  • your daily take this csv and index it in Splunk,
  • then you want to add to a lookup all the rows where date is after the previous ingestion date; is it correct?

If this is you requirement, you have to save in your lookup in every row the new school_roles and the date that you have in your csv or the date of the ingestion (_time).

In this way, when you run again your search (new day), you don't need to restart your search from the beginning, but instead you can start from the latest date of your lookup, infact using [| your_lookup.csv | stats latest(earliest) AS earliest) ] you pass to your search the earliest parameter that is 0 for the first run.
So when you run again the search to populate your lookup you'll have only the newest values and you'll have an updated lookup.
Modify my search in this way:

 your_search [| your_lookup.csv | stats latest(earliest) AS earliest) ] latest=now
| rename _time AS earliest
 | table index earliest
 | outputlookup your_lookup.csv append=true

If your csv file is updated by script, you could think to use this script to directly index results without using csv.

Bye.
Giuseppe

snipedown21
Path Finder

Hi. I really don't get how this would work. If you could explain how I can use this with the following code, It would be great.
Thank you.

Blockquote

index=.... | table role | dedup role | sort role

Blockquote

This is my search for now.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi snipedown21,
The problem is to populate your lookup without re-run your search from the beginning, so the update of your lookup is done in two steps:

reading the csv file to add: this is done in the way you're already using; in this way you have in a dedicated index all the rows of your csv file, in other words you have in each event: _time, role.

Now you have to update your lookup adding the new roles that you have indexed: to perform this job you have to run a search on the index choosing the correct time period (fields earliest and latest).
Latest is fixed as "now", so it don't have any problem.
The problem is to find the earliest value to use in the search that must be the latest value of your lookup, in other words, the last time when your lookup was updated.
To perform this, you can use the suggested search to populate the lookup:
your_search [| your_lookup.csv | stats latest(earliest) AS earliest) ] latest=now
| rename _time AS earliest
| table index earliest
| outputlookup your_lookup.csv append=true
As you can see: the output of the subsearch is the earliest field to use in the main search.

In this way you add to your lookup only the newest roles (the ones after last update).
So you have an updated lookup to use for your scopes (that I don't know).

I hope I was clear enough, otherwise ask me which part of the procedure aren't clear.

Bye.
Giuseppe

snipedown21
Path Finder

Hi Giuseppe.
I tried the above suggestion and had to configure the lookup file with initial values to start off.
It works just fine.
As always, your answer is on point.
Thank you.
-Snipedown21

0 Karma

gcusello
SplunkTrust
SplunkTrust

Good!
if you're satisfied of this answer, please accept or upvote it.
Bye.
Giuseppe

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...