topic Re: How do I get an automatic lookup to populate a table, even if there are null values in the event logs for the matching field? in Splunk Search

How do I get an automatic lookup to populate a table, even if there are null values in the event logs for the matching field?

cchimento — Mon, 07 Dec 2015 19:05:20 GMT

Hello - This is my first time asking a question here. I receive a lot of answers by reading others' questions (thank you) so hopefully you can understand that I have done my fare share of searching before typing this all out. So here we go....

Assume that I have a fully functioning automated lookup using a CSV.

3 fields; location, number, group [which is used for call counts for each location/number]

The event logs populate the number field and the lookup populates the location & group fields.

What happens is that, if there are no events that have the number as listed in the table - it will not populate the location/group and call count (event count) for that entry. So for example.. Group A I have a complete list, as it is in the lookup because all numbers show up in the event logs. However, for Group B I don't have a full list because some of the numbers needed to populate the lookup are not present in the logs. So splunk will not list those locations in the table as there is essentially nothing to look up.

What I want is to have the complete list (as from the csv) listed in the table regardless if the lookup finds it or not and then if there are no matches for numbers, populate a 0 (zero) value for the table.

Location - Number - Group - Calls
A ------------1------------- A --------- 4
B------------2 --------------A-----------5
A------------3---------------B-----------3
B ---------- 4 ---------------B---------- 0 <----- I want zero value because the number was not found in the logs.

I think that I might want to do a reverse look up? But something tells me there may be a very simple way to do this.

Thanks for your time, I look forward you your help!

-Chris

Re: How do I get an automatic lookup to populate a table, even if there are null values in the event logs for the matching field?

lguinn2 — Mon, 07 Dec 2015 19:42:08 GMT

The community should probably see your search to offer suggestions, but I am going to give it a guess here:

If you are searching like this, with an automatic lookup:

yoursearchhere | stats count as Calls by Location Number Group

You could do this:

yoursearchhere | stats count as Calls by Location Number Group
| append [ inputlookup yourLookupNamehere | eval Calls = 0 ]
| stats sum(Calls) as Calls by Location Number Group

The append will "fill in" the cells with a zero value for Calls, and then the final stats combines it with the original results.

Re: How do I get an automatic lookup to populate a table, even if there are null values in the event logs for the matching field?

cchimento — Mon, 07 Dec 2015 19:45:48 GMT

I think the moderator edited my post title/Question which I don't feel is accurate to my problem. Please refer to the actual body of my post. Thanks.

Re: How do I get an automatic lookup to populate a table, even if there are null values in the event logs for the matching field?

lguinn2 — Mon, 07 Dec 2015 22:19:53 GMT

Okay, but does my answer work? I think it should. Also, it would still be helpful to see your search (even sanitized).

Re: How do I get an automatic lookup to populate a table, even if there are null values in the event logs for the matching field?

hartfoml — Tue, 08 Dec 2015 13:00:56 GMT

If any of the fields in missing a value then you can fill the value you want useing the fillnull command like this

your search | fillnull value=0 Calls Location Number Group | Stats count by ...

this will fill any log that does not have a value for the field to "0"

if you want to fill only the Calls field with the "0" then leave out the other fields or creat another |fillnull value= for the other fields

Hope this helps

Re: How do I get an automatic lookup to populate a table, even if there are null values in the event logs for the matching field?

cchimento — Tue, 08 Dec 2015 15:33:48 GMT

Thank you - This worked for me however I have another issue based on these new results.

Before I was using a pivot table to manipulate this data. Very early in my development on these particular logs so I am playing with things. Using your method Iguinn, I see numbers populate with a 0 value for the number of calls. But because my CSV has all three "groups" it pulls zeros for every entry in the CSV that didn't pull a result... despite having specified a specific 'group' in my search. Currently, the way I see around this is to create 3 separate look ups for each group as opposed to one although I don't want to. Base on your method, how can I only display results from specific groups? Can I selectively pull info from a lookup?

My search:
index=ast sourcetype=poc_agi_logs number=* group="FAA" | stats count as Calls by location number group | append [ inputlookup faadid.csv | eval Calls = 0 ] | stats sum(Calls) as Calls by location number group

My CSV (snipit)

location,number,group
UK,3785,FAA
SPAIN,5301,FAA
FRANCE,5081,FAA
ITALY,0025,FAA
UK,3786,FAB
SPAIN,5302,FAB
FRANCE,5082,FAB
ITALY,0026,FAB
UK,0615,EP
SPAIN,8165,EP
FRANCE,4984,EP
ITALY,0098,EP

Results for searching group FAA will return zeros for the locations and numbers in FAB and EP as well. I don't want that. Does that make sense?

Re: How do I get an automatic lookup to populate a table, even if there are null values in the event logs for the matching field?

cchimento — Tue, 08 Dec 2015 15:34:09 GMT

Thank you - but this did not work for me.

Re: How do I get an automatic lookup to populate a table, even if there are null values in the event logs for the matching field?

lguinn2 — Wed, 09 Dec 2015 00:18:44 GMT

Let me try again with a variation of my earlier answer.

index=ast sourcetype=poc_agi_logs number=* group="FAA" 
| stats count as Calls by location number group 
| append [ inputlookup faadid.csv | where group="FAA" | eval Calls = 0 ] 
| stats sum(Calls) as Calls by location number group

Notice the where command that follows the inputlookup - this will select only the FAA group and set it to zero. I think this will work. Thanks for the clarifying comment!

Re: How do I get an automatic lookup to populate a table, even if there are null values in the event logs for the matching field?

cchimento — Wed, 09 Dec 2015 20:46:07 GMT

Ya know I did try that and failed, but I didn't put the | before "where". That must have been it. I will have to try this later on or tomorrow. Thank you again and I will update after I try.

Re: How do I get an automatic lookup to populate a table, even if there are null values in the event logs for the matching field?

cchimento — Fri, 08 Jan 2016 01:04:29 GMT

Hi Iguinn - happy new year. Finally able to work this and it indeed worked for me. Thank you so much for your help.