Last Value in Lookup as Variable?

[snoobzilla]

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.

[somesoni2 · Accepted solution]

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

[snoobzilla]

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

[snoobzilla]

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

[somesoni2]

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

[snoobzilla]

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

[martin_mueller]

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.

[martin_mueller]

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.

[snoobzilla]

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.