topic Re: Using lookups to categorize field values into certain categories, how do I prepare for a field value that is not in the lookup? in 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 — Fri, 20 Mar 2015 03:02:13 GMT

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

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

musskopf — Fri, 20 Mar 2015 05:35:55 GMT

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.

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

chimell — Fri, 20 Mar 2015 13:16:31 GMT

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

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

HattrickNZ — Sun, 22 Mar 2015 19:55:16 GMT

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

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

musskopf — Mon, 23 Mar 2015 05:09:39 GMT

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.

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

HattrickNZ — Mon, 23 Mar 2015 22:12:46 GMT

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

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

musskopf — Mon, 23 Mar 2015 22:15:20 GMT

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

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

HattrickNZ — Mon, 23 Mar 2015 22:22:18 GMT

not sure I follow? Do I use lookups here?

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

HattrickNZ — Mon, 23 Mar 2015 22:25:54 GMT

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.

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

musskopf — Mon, 23 Mar 2015 22:34:02 GMT

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"

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

HattrickNZ — Mon, 23 Mar 2015 23:16:41 GMT

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

value2replacelookup_RHS_2takenOut_CatchThis     values(status)
Aaaaa   200
Bbbbb   304
Ccccc   303

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

musskopf — Mon, 28 Sep 2020 19:19:59 GMT

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

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

HattrickNZ — Mon, 23 Mar 2015 23:42:20 GMT

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

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

musskopf — Mon, 23 Mar 2015 23:46:55 GMT

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

Cheers