How to exclude writing existing result to Splunk lookup table.

[Vicky84]

I want to keep updating new records to Splunk lookup table and not writing records again for existing users, even if they come in search results.

Lookup table structure : apiCallerID, ticketId

My base search query is having mvexpand, mvindex & rex commands and then table so when I do "NOT" it does not allow me to any other way or corrections.

index=test sourcetype=stats earliest=-5m@m latest=-0m@m| eval temp=ltrim(Response,"[") | eval temp1=rtrim(temp, "]") | eval temp2=split(temp1,"}")| mvexpand temp2| eval temp3=ltrim(temp2,",")  | eval testData=mvindex(temp3,-1) | rex field= testData "userID:\s(?<apiCallerID>\d+)" | table apiCallerID 

NOT [|inputlookup LookuptableGeneratorForDSIDByTestID.csv| fields apiCallerID]

Basically if the user is not there I want to add a ticket id along with user in lookup table and in future system will use this to raise any new tickets and prevent duplicate tickets for the existing user.

[to4kawa]

If your log is JSON format, try spath and make table. it's easy.

[Vicky84]

@to4kawa with or without path, how do you use "NOT" to exclude records from lookuptable. Only after massaging the result data with rex ors path, I will get the userID and post that NOT is not working or I am missing something. please correct

[to4kawa]

index=yours something NOT [ | inputlookup yourlookup | table your_field]

|inputlookup your_lookup where NOT your_field="value"

Which do you want?

[Vicky84]

@to4kawa It is first one, as NOT is put after doing spath or rex. Need to exclude apiCallerID that are already in lookup table

index=test sourcetype=stats earliest=-5m@m latest=-0m@m| kv
| spath input=Response {} output=response
| stats count by response
| spath input=response
| fields - count response
 NOT
[|inputlookup LookuptableGeneratorForDSIDByTestID.csv| fields apiCallerID]

[to4kawa · Accepted solution]

...

| fields - count response
| search NOT
[|inputlookup LookuptableGeneratorForDSIDByTestID.csv| fields apiCallerID]

[Vicky84]

Oh I was missing "|search" thanks !