topic Re: Trouble figuring out how to do a lookup in Splunk Search

Trouble figuring out how to do a lookup

agoebel — Tue, 30 Sep 2014 14:02:41 GMT

I have a bunch of log files which as part of the first 4 lines or so sends a handshake in the form of

 20140930093723895211 R 0019 ,prepare ENSAll)

Where ENSAll is a profile name. There is only ever one profile per source (each source file is actually per socket). What I need is to be able to attache this profile name to every event for a given source.

I started with a basic join but as we get up to 100k entries per file and the prepare is the first one, this search can take forever. Also, if the prepare is out of the time window it may not be registered.

I am at a complete loss how to speed up this query, it feels like it should be able to be indexed somehow since it is once per file, though not the first line necessarily.

Edit

I should clarify, the files look more like this:

...snip...
20140930084819093639 R 0010 (CM,000026
20140930084819561640 R 0016 ,prepare ENSall)
20140930084819561640 R 0010 (CM,000026
20140930084819561640 W 0019 (AK,000019,welcome)
20140930084819811240 W 0027 (AK,000027,starting:resume)
20140930084819811240 R 0014 ,start resume) 
...snip...

And I need the profile name for each event following the first prepare.

Re: Trouble figuring out how to do a lookup

somesoni2 — Tue, 30 Sep 2014 14:39:29 GMT

You can extract this value as a field and then setup an automatic lookup. See these

http://docs.splunk.com/Documentation/Splunk/6.1.3/Data/Configureindex-timefieldextraction
http://docs.splunk.com/Documentation/Splunk/6.1.3/Knowledge/Usefieldlookupstoaddinformationtoyourevents

Re: Trouble figuring out how to do a lookup

agoebel — Tue, 30 Sep 2014 14:57:16 GMT

If I'm understanding this correctly I do a regex field extraction at index time, search for it in a report to populate a lookup file, and then do an automatic lookup against that? I did not think reports ran fast enough (limit of per minute) to be useful. As soon as that first prepare is seen I need to have it in the rest of the log plus the lookup table would get quite big....good first step though

Re: Trouble figuring out how to do a lookup

martin_mueller — Tue, 30 Sep 2014 22:19:50 GMT

You said there's exactly one value per source?

If so, you can distribute this value using eventstats like this:

base search | eventstats first(profile_name) as profile_name by source | ...

That's assuming the profile name is extracted already. If not, use ... | rex ",prepare (?\w+)" | ... or whatever regular expression works for your data.

Re: Trouble figuring out how to do a lookup

agoebel — Wed, 01 Oct 2014 20:09:26 GMT

That seemed to do it and was what I am looking for, thanks. I'm assuming it looks no further back than the time range on the base search which may be a slight problem but that's for another time.

Re: Trouble figuring out how to do a lookup

martin_mueller — Wed, 01 Oct 2014 20:17:39 GMT

It doesn't look further, no.

However, you can make it do that. Say you want data from the past seven days, but want to copy over profile values that happen on the eighth day as well. Set your earliest to -8d@d, run your search with eventstats, and then filter like this: ... | addinfo | where _time >= relative_time(info_min_time, "+d")

The addinfo adds the search's time range to the events, and the where throws out the first day. Do that after the eventstats and you will get profile values that happened on that day despite not actually using the events in the end.