Splunk Search

Ignore records in a lookup

nolesrb
Engager

I have a lookup table (attached sample) and in my search I want to return records "ACCT" is not in "ACCTNBR4" in the lookup.

My current search looks something like this:
sourcetype="abc" "SAMPLE acctGuid=, 13DigitAcctNbr=, 4DigitAcctNbr=* " | rex field=_raw ", 4DigitAcctNbr=(?[0-9]{4})" | lookup TestAccounts ACCTNBR4 AS ACCT output ACCTNBR4 | stats count by ACCTNBR4

I want to exclude what is being returned.

GUID,ACCTNBR4,INSERT_DATE,NOTES,USERNAME,FNAME,LNAME

123,1234,8/24/2012 9:01:56 AM,,abc,Mad,Dog

456,1111,3/19/2013 11:29:59 AM,,def@test.net,,

Tags (1)
0 Karma

the_wolverine
Champion

You can assign a value of NULL to the column in question and then specify it in your search:

sourcetype="abc" "SAMPLE acctGuid=, 13DigitAcctNbr=, 4DigitAcctNbr=* " | rex field=_raw ", 4DigitAcctNbr=(?[0-9]{4})" | lookup TestAccounts ACCTNBR4 AS ACCT output ACCTNBR4 | fillnull ACCTNBR4 value=NULL | search ACCTNBR4=NULL | stats count by GUID,ACCTNBR4

0 Karma

nolesrb
Engager

I am trying that, but it is still giving me the same counts.

0 Karma

nolesrb
Engager

I only want to show records if ACCT does not have a value in the ACCTNBR4 column of thelookup table.

So if 2 events looked like this:
SAMPLE acctGuid=123-abc, 13DigitAcctNbr=1234567890123, 4DigitAcctNbr=1234
SAMPLE acctGuid=def-567, 13DigitAcctNbr=0001117890123, 4DigitAcctNbr=7945

I would only want to return 7945, 1 from this record:
SAMPLE acctGuid=def-567, 13DigitAcctNbr=0001117890123, 4DigitAcctNbr=7945

I would not return the other event because 4DigitAcctNbr (field acct) 123 is in my lookup file:
SAMPLE acctGuid=def-567, 13DigitAcctNbr=0001117890123, 4DigitAcctNbr=7945

Thanks!
Ryan

0 Karma

sowings
Splunk Employee
Splunk Employee

What do you mean by "exclude what is being returned"? You want to filter to a subset of account numbers?

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 ...