Splunk Search
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

Builder

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

0 Karma
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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 "value2replacelookupRHS2takenOut_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