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

[HattrickNZ]

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

[chimell]

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

[HattrickNZ]

not sure I follow? Do I use lookups here?

[musskopf]

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.

[HattrickNZ]

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

[musskopf]

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.

[HattrickNZ]

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

[musskopf]

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

[HattrickNZ]

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.

[musskopf]

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"

[HattrickNZ]

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 

[musskopf]

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

[HattrickNZ]

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 

[musskopf]

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

Cheers