Combine 2 lookup files and search query

[shayhibah]

Hi,

I want to create a "table" with different rows on every column.
For example:
Column A | Column B | Column C
a1 b1 c1
a2 c2
a3

I thought that every column can be written as a different lookup file and combine these 3 files into one table.
1. Is it possible? is there an easier way to do that?
2. In case I would like to run query and for every value in the lookup fields (a1,a2,a3) - look for this value in my logs and count each one of them - how can I do this?
For example:

Column A | Column B | Column C
a1=3 b1=5 c1=3
a2=2 c2=1
a3=0

Thanks

[DavidHourani]

Hi @shayhibah,

To answer your question
1- Yes of course you can have all of them in a lookup file and just keep null values where null values should be. your lookup file can look something like this :

ColumnA,ColumnB ,ColumnC
a1,b1,c1
a2,c2,
a3,,

As for question 2 you can achieve this using streamstats which will allow you to count for every column the number of occurrences of its values in a new field.
Details on streamstats here : https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/Streamstats

Let me know if that helps.

Cheers,
David

[shayhibah]

Hi @to4kawa and @DavidHourani

I tried your both answers and found out that:
1. When my lookup file contains the three column, the search commmand will look for events contain the whole row's field in the lookup file (which is not what I want - I want to count each field separately with no dependency to the other fields in the lookup rows).
2. Therefore, I decided to split them into three lookup files.
3. When I run the query on one loookup file, I get an error due to rex command:

sourcetype="my_log" | makemv delim="," my_field | rex field=my_field mode=sed "s/_/ /g" [|inputlookup my_lookup_1| rename Field AS my_field]

So I tried to use the query like this:
sourcetype="my_log" | makemv delim="," my_field | rex field=my_field mode=sed "s/_/ /g" | search [|inputlookup my_lookup_1| rename Field AS my_field]

and it works but - I get count values for fields exist in my logs and not all the fields that can be found on the lookup file.
How can I count lookups fields that appear in my logs and for lookup values fields that have no appearance in my logs to give it 0?

Thanks,
Shay

[DavidHourani]

Hi @shayhibah,

Did you try to run a fillnull command ? You can use it to replace all null values with 0.

Cheers,
David

[shayhibah]

My query looks like this:

sourcetype="my_log" | makemv delim="," my_field | rex field=my_field mode=sed "s/_/ /g" | search [|inputlookup my_lookup_1| rename Field AS my_field] | stats count by my_field

Where should I need to put it? I tried to put it before and after stats command but it doesnt work.
Any suggestions?

I think the problem is that after [|inputlookup my_lookup_1| rename Field AS my_field] part, it takes the fields from the query and not from the lookup.
Thats why I get only the field values that exist in my logs and not everything.

[DavidHourani]

What about using streamstats with something like this :

sourcetype="my_log" | makemv delim="," my_field | rex field=my_field mode=sed "s/_/ /g"   | search [|inputlookup my_lookup_1| rename Field AS my_field] | streamstats count by my_field

In that case you only count for that field for all other empty fields you don't count but you will still have the logs to use and work with.

[shayhibah]

Unfortunately it doesn't work.

[DavidHourani]

in that case get rid of | search [|inputlookup my_lookup_1| rename Field AS my_field] and add it in the end.

[shayhibah]

In this case it shows only the values exist in my logs and not in my lookup.

[DavidHourani]

but | search [|inputlookup my_lookup_1| rename Field]doesn't add the values values in your lookup.

This does : | append [|inputlookup my_lookup_1| rename Field]