topic Re: Last Value in Lookup as Variable? in Splunk Search

Last Value in Lookup as Variable?

snoobzilla — Mon, 07 Apr 2014 22:08:13 GMT

How do I get the last KER out of my lookup and get it into search below as LASTKER?

I have a lookup table of error signatures. I have assigned a KER0000### e.g KER0000123 as a primary key to use when referencing the signature... I want to auto increment new signatures. If I run this

search error
| stats count by fields used for signature
| eval LASTKER="KER0000100"
| where count >10
| streamstats count(KER) AS INCREMNT
| eval myint=ltrim(LASTKER, "KER")
| eval myint=INCREMNT+myint
| eval myint="0000000".myint
| rex field=myint "(?\d{7})$"
| eval KER="KER".myint

Above gives expected results based on KER0000100 (e.g. next one is KER0000101, then KER0000102...)

Thanks in advance.

Re: Last Value in Lookup as Variable?

martin_mueller — Tue, 08 Apr 2014 14:10:26 GMT

Here's what you can do:

| stats count | eval [inputlookup cim_http_status_lookup | sort - status | head 1 | return status] | eval new_status = status + 1

I'm using a lookup of HTTP status codes as an example, available in the Splunk Common Information Model: http://apps.splunk.com/app/1621/
That gives me my "previous values", I sort them by some criterion and pick one row, one field to return, yielding a string of status="511" that gets passed to the eval and added to my dummy event generated by | stats count.
After that I can do any math I like, such as incrementing.

Re: Last Value in Lookup as Variable?

snoobzilla — Tue, 08 Apr 2014 14:19:45 GMT

Thanks. I was storing value as text KER0000123 so we could use it in our knowledgebase and ticketing system as a keyword. Will try storing as a number too and see if that works.

Re: Last Value in Lookup as Variable?

somesoni2 — Tue, 08 Apr 2014 15:28:30 GMT

Try this

Updated:

search error | stats count by fields used for signature | where count >10 | eval joinfield=1
| join joinfield [|inputlookup error_signature.csv | rex field=KER "KER(?<counter>.*)" 
| eval counter=tonumber(counter) | stats max(counter) as LASTKER | eval joinfield=1]
| fields - joinfield | streamstats count(KER) AS INCREMNT  
| eval myint=INCREMNT+LASTKER| eval myint="0000000".myint 
| rex field=myint "(?<myint>\d{7})$" | eval KER="KER".myint

Re: Last Value in Lookup as Variable?

martin_mueller — Tue, 08 Apr 2014 15:32:59 GMT

My numbers are just an example - you can use your combo of ltrim(...) if you like. Just replace the fixed LASTKER="KER00000100" with a subsearch yielding that field.

Re: Last Value in Lookup as Variable?

snoobzilla — Tue, 08 Apr 2014 18:40:58 GMT

I tried that and unfortunately I am only getting 1 row of KER back even when I have multiple new signatures.

Re: Last Value in Lookup as Variable?

somesoni2 — Tue, 08 Apr 2014 19:36:46 GMT

My bad, I overlooked that completely. Try the updated answer.

Re: Last Value in Lookup as Variable?

snoobzilla — Tue, 08 Apr 2014 20:00:56 GMT

Thank you, that did the trick!!!!!!!!

Re: Last Value in Lookup as Variable?

snoobzilla — Tue, 08 Apr 2014 20:19:19 GMT

One typo... missing \ before d. Last line should read
| rex field=myint "(?\d{7})$" | eval KER="KER".myint