topic Re: Combine two tables without the use of join and without aggregating values in Splunk Search

Combine two tables without the use of join and without aggregating values

moonie — Mon, 16 Aug 2021 12:08:23 GMT

Hello,

I'm working on a really complex search where I need to combine results from different lookup tables. One lookup table is really big with multiple million entries, while the other one is quite small with only a thousand entries.
Both tables have one common field, let's call it "office". The big tables has entries for task which are applied to a certain office. The other table has more information about the office.
Some example data for the task lookup:

office	city	country	importance
xxx	madrid	spain	very important
yyy	paris	france	important

Office table looks similar to this:

office	group	name
xxx	this	aaa
yyy	that	bbb

I want to add the group and name fields to the first task table, without loosing any entries from the task table, so I can continue working with it. I've tried a lot of different approaches but none of them work. I got the best results with this search, but it's still not the outcome I want:

| inputlookup task_lookup | eval importance_very_important=if(match(importance, "very important"), 1, 0), importance_important=if(match(importance, "important"), 1, 0), importance_less_important=if(match(importance, "less important"), 1, 0) | eval source="task" | append [| inputlookup office_lookup | eval source="office"] | stats values(source) as source, values(country) as country, values(city) as city, sum(importance_*) as *, values(group) as group, values(name) as name by office | where mvcount(source)=2

This search gives me the right combination of fields BUT it also combines the different cities and countries, which I don't want, since I need them seperated so I can filter them. I get the following outcome (e.g.):

office

country

city

name

group

very_important

important

less_important

xxx

madrid

paris

spain

france

italy

aaa

this

yyy

rome

paris

france

spain

bbb

that

So all in all I need a result table that doesn't combine any values so I can work with them seperately. I'm at a point where I have no clue how to accomplish this, so any help would be highly appreciated! 🙂
Additional info: I don't want to use join since the first lookup has so many entries, I don't thinks that's going to work. I also can't just use mvexpand, since it doesn't properly expand the counts for the different task counts with their importance.

Re: Combine two tables without the use of join and without aggregating values

richgalloway — Mon, 16 Aug 2021 12:59:35 GMT

While the inputlookup command can be powerful, many seem to forget the usefulness and simplicity of lookup.

Re: Combine two tables without the use of join and without aggregating values

moonie — Mon, 16 Aug 2021 12:56:57 GMT

This works perfectly, thank you so much!!

I didn't even think of lookup, but I'll def keep it in mind now for further searches. What a great solution. 🙂

Re: Combine two tables without the use of join and without aggregating values

ITWhisperer — Mon, 16 Aug 2021 13:00:15 GMT

Alternatively, if your second "table" was not a lookup but a search, you could use eventstats to "join" the "tables"