Splunk Search

Exclude certain values in a field

New Member

I have two separate searches, both of which involve serial numbers that make a batch. I'm trying to compute the time taken for batch 1 (search 1=96 serial numbers) and batch 2 (search 2=100 serial numbers).

The issue is that I want to only include the serial numbers that are common to both searches, and from exporting raw data from both these searches on excel, it turns out that there are only 92 common serial numbers between the two searches.

(The search I'm doing for the batches of serial numbers is simply something like "NTEST$$$$B" and "NTEST$$$$A" where $$$$=any combination of numbers, and serial number is a parsable field on Splunk. For instance, for every NTEST1234A there exists an NTEST1234B. And this relationship is true only for 92 of the serial numbers - I verified this on Excel).

Ideally, this is what I'd like to do in raw terms..

"NTEST****A" NOT (....list of extra serial numbers that I want to exclude)| stats max(_time) as maxtime min(_time) as mintime | eval difference=maxtime-mintime

"NTEST****B" NOT (....list of extra serial numbers that I want to exclude)| stats max(_time) as maxtime min(_time) as mintime | eval difference=maxtime-mintime

I assume that the later part of my query will give me the time difference between the earliest event and the latest event for that search. The problem lies with the first part where I know I cannot exclude serial numbers by just typing NOT NTEST####A NTEST&&&&B and so on.

I'm OK with executing them as two separate queries, but I want to make sure I'm only using those 92 common devices. Thanks for any help with this!

Tags (2)
0 Karma

Revered Legend

Try this (assuming this SerialNumber is a field that is extracted in with values like "NTEST$$$$B" and "NTEST$$$$A". If not than first step will be extract those)

"NTEST*A" OR "NTESET*B" | table _time SerialNumber | rex field=SerialNumber "(?<SerialNumberOnly>.*)(?<Type>\w)" | eventstats values(Type) as Types by SerialNumberOnly | where mvcount(Types)=2 | fields - SerialNumberOnly Types | stats min(_time) as min max(_time) as max by Type
0 Karma

New Member

Hi @somesoni2, thanks for your prompt response! I have a couple questions.
(1) rex field=SerialNumber " "(? < SerialNumberOnly> . * ) (? < Type >\w)"" ---> what is SerialNumberOnly and Type? for instance, if my serial number is NTEST1234A, would I use "(? < N T E S T 1 2 3 4> . * ) (? < A > \ w)" ?
And if I have multiple such devices, how will I account for that?

To give you an idea of my data set, here's a smaller sample:

I basically only want to keep the first three pairs in both types and exclude serial numbers NTEST9999A, NTEST8888B, NTEST1111A, and NTEST2222B (....in the real data set there are about 8 such unique values)

(2) Also, should "Type" be a field? Coz it's not a parsable field on the logs I have.

Thanks so much for clarifying!

0 Karma

Revered Legend

I am assuming that you have your raw data like this

_time, SerialNumber, ...other fields if any

So basically you have fields called _time and SerialNumber. If you have different name for this field, use that.. From SerialNumber field I am extracting fields SerialNumber which will be NTEST$$$$ and Type which will be A OR B.

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...