Splunk Search

Using lookups to categorize field values into certain categories, how do I prepare for a field value that is not in the lookup?

HattrickNZ
Motivator

I am thinking of using lookups for categorizing field values into certain categories, as below. Using lookups is probably the way I will go, as thus far this seems to be the best option?

But what I want to prepare for is the when a new fieldname is introduced lets say fieldName5 or anything other than fieldName1-4 and map it to Category Cat_Undefined. Is there a way I would be able to do this?

A simple example:

fieldName,Category
fieldName1,Cat1
fieldName2,Cat2
fieldName3,Cat3
fieldName4,Cat4
0 Karma

chimell
Motivator

Hi HattrickNZ
try this query

source=........  fieldName=fieldName1 OR  fieldName=fieldName2 OR  fieldName=fieldName3  OR  fieldName=fieldName4 | table fieldName Category | append[search source=...... fieldName!=fieldName1 AND fieldName!=fieldName2 AND  fieldName!=fieldName3  AND fieldName!=fieldName4 | fillnull value=Cat_Undefined category ]|table fieldName Category

tell me if it satisfy you

0 Karma

HattrickNZ
Motivator

not sure I follow? Do I use lookups here?

0 Karma

musskopf
Builder

Yes, once you create your lookup definition you should define the "Default matches" to "Cat_Undefined", that will be the default value. Alternatively, you could use the fillnull command after the lookup.

0 Karma

HattrickNZ
Motivator

you should define the "Default
matches" to "Cat_Undefined", that will
be the default value.

How do i do this exactly? Is this somehow done when I am uploading my csv file with the lookups? tks

0 Karma

musskopf
Builder

You should go to "Settings -> Lookups" and click over "Lookup Definitions". It sounds that you already uploaded the CSV file, so just need to create a new definition. Give it a name, select the uploaded file and click "Advanced Options". There you should define, for example, minimum: 1, maximum: 1 and default matches: "CAT Undefined". basically Splunk will use this value to populate everything without a match on your CSV file.

Remember now to use you Lookup Definition once you use the lookup command, and not the CSV file.

0 Karma

HattrickNZ
Motivator

I can't get the lookup to work...
but i have my search stats values(fieldName) by Category
and this gives me something like:
fieldName,Category
fieldName1,Cat1
fieldName2,Cat2
fieldName3,Cat3
fieldName4,Cat4

but it is not picking up the fieldName5 that I want to show as CAT Undefined after doing what you said with the min/max/default. Am I doing something Wrong?

So what I want to see is:
fieldName,Category
fieldName1,Cat1
fieldName2,Cat2
fieldName3,Cat3
fieldName4,Cat4
fieldName5,CAT Undefined

0 Karma

musskopf
Builder

Paste here the full search command you're using...

0 Karma

HattrickNZ
Motivator

index=_internal sourcetype="splunk_web_access" | stats values(status) by value2replacelookup_RHS_2takenOut_CatchThis

gives me:

value2replacelookup_RHS_2takenOut_CatchThis     values(status)
Aaaaa   200
Bbbbb   304
Ccccc   303 

I have remove these 2 lines from my lookup table that i uploaded to splunk

Ddddd   400
Eeeee   404 

So I want to somehow know that 400 and 404 are there.

0 Karma

musskopf
Builder

Sorry, I don't get where is the lookup command on your search... you need to try something like:

index=_internal sourcetype="splunk_web_access" | lookup MY_LOOKUP status | stats count by msg

Now the MY_LOOKUP is the definition name you created using a CSV file like:

status,msg
200,OK
404,NOT_FOUND

An alternative if that isn't working is just use fillnull, like that:

index=_internal sourcetype="splunk_web_access" | lookup MY_LOOKUP status | fillnull value="Not Resolved" msg | stats count by msg

This should populate all events without the msg field with "Not Resolved"

0 Karma

HattrickNZ
Motivator

index=_internal sourcetype="splunk_web_access" | lookup ABasicLookupFoSplunkOnMyMachine_2takenOutButCatchThis.csv value2lookup | fillnull value="Not Resolved" value2replacelookup | stats count by value2replacelookup
gives me:

value2replacelookup     count
Not Resolved    2658

index=_internal sourcetype="splunk_web_access" | lookup ABasicLookupFoSplunkOnMyMachine_2takenOutButCatchThis.csv value2lookup | fillnull value="Not Resolved" value2replacelookup | stats values(status) by value2replacelookup_RHS_2takenOut_CatchThis
gives me(but it is not showing the undefined values)

value2replacelookup_RHS_2takenOut_CatchThis     values(status)
Aaaaa   200
Bbbbb   304
Ccccc   303 
0 Karma

musskopf
Builder

You are confusing thing, let's try to clarify:

STATS
when using stats by, the stats will only group things by the selected field, if the field is present.

FILLNULL
The fillnull command creates value for fields that don't exist on the event, by default it populates with "0", but you can speficy any value.

Now, if you look your searches, the first thing I noticed is the lookup command. Looks like you're using the CSV file instead of the lookup definition, that's the reason why the "Default Value" from the lookup definition is not been used. Well, ignoring that and focusing on the fillnull only, at the second command, you're grouping by "value2replacelookup_RHS_2takenOut_CatchThis", but your fillnull is populating "value2replacelookup". Try update it like that:

index=_internal sourcetype="splunk_web_access" | lookup ABasicLookupFoSplunkOnMyMachine_2takenOutButCatchThis.csv value2lookup | fillnull value="Not Resolved" value2replacelookup_RHS_2takenOut_CatchThis | stats values(status) by value2replacelookup_RHS_2takenOut_CatchThis
0 Karma

HattrickNZ
Motivator

Thats it. That search works. Thanks very much. And apologies for the brutalness of my question!!
Yes that is using the csv file. And it gives me this.

value2replacelookup_RHS_2takenOut_CatchThis     values(status)
Aaaaa   200
Bbbbb   304
Ccccc   303
Not Resolved    400
                 404 

But this works the same:(this is using the lookup I defined with the same csv file, but i have to use fillnull here)
index=_internal sourcetype="splunk_web_access" | lookup ABasicLookupFoSplunkOnMyMachine_2takenOutButCatchThis_lookup value2lookup | fillnull value="Not Resolved" value2replacelookup_RHS_2takenOut_CatchThis | stats values(status) by value2replacelookup_RHS_2takenOut_CatchThis

If i don't use fillnullI thought the min/max/default = 1/1/"CAT Undefined" would work but it does not seem to as it gives me this
index=_internal sourcetype="splunk_web_access" | lookup ABasicLookupFoSplunkOnMyMachine_2takenOutButCatchThis_lookup value2lookup | stats values(status) by value2replacelookup_RHS_2takenOut_CatchThis

value2replacelookup_RHS_2takenOut_CatchThis     values(status)
Aaaaa   200
Bbbbb   304
Ccccc   303 
0 Karma

musskopf
Builder

Interesting, it might be a bug on the release, works over here... anyway, sticky with fillnull for now.

Cheers

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