topic Re: how to break a lookup table into mulitple lookup tables ( rowwise or based on a field value) in Splunk Search

how to break a lookup table into mulitple lookup tables ( rowwise or based on a field value)

bkumarm — Fri, 10 Mar 2017 14:06:17 GMT

we have a lookup table which is like:
table:
host,userid,index,status
host1.dom.com,user1,idx1,Y
host1.dom.com,user2,idx2,Y
host2.dom.com,user1,idx1,Y
host2.dom.com,user2,idx2,Y
host3.dom.com,user1,idx1,Y
host3.dom.com,user2,idx1,Y

WE need to break this table into multiple tables based on the hostname as key.
we need to execute this as dynamic search or saved search without using hardcoded values.

and we need the table name to include the hostname. the expected output is :

host1_table:
host,userid,index,status
host1.dom.com,user1,idx1,Y
host1.dom.com,user2,idx2,Y

host2_table:
host,userid,index,status
host2.dom.com,user1,idx1,Y
host2.dom.com,user2,idx2,Y

host3_table:
host,userid,index,status
host3.dom.com,user1,idx1,Y
host3.dom.com,user2,idx1,Y

we need help in getting this done.

Re: how to break a lookup table into mulitple lookup tables ( rowwise or based on a field value)

gcusello — Fri, 10 Mar 2017 14:39:50 GMT

Hi bkumarm,
it's possible to perform lookup separation in multiple searches, running as many searches as hosts (three in the following example):

your_search host=host1.dom.com | table field1 field2 field3 ... | outputlookup host1_table
your_search host=host2.dom.com | table field1 field2 field3 ... | outputlookup host2_table
your_search host=host3.dom.com | table field1 field2 field3 ... | outputlookup host3_table

Bye.
Giuseppe

Re: how to break a lookup table into mulitple lookup tables ( rowwise or based on a field value)

DalJeanis — Tue, 29 Sep 2020 13:12:15 GMT

Something like this should work the first time, with either the inputcsv/outputcsv or inputlookup/outputlookup verbs.

| inputlookup mybigtable | dedup host | table host | rename host as myhost | eventstats count as nbrHosts 
| map search="| inputlookup mybigtable | search host=$myhost$ | outputlookup $myhost$_table" maxsearches=nbrHosts

I'd suggest, organizationally, that you want to keep the data in the big table as the system of record and then recreate the little tables every time the big one changes.

Test the code once, change the file by adding one record or deleting one, and test it again to make sure the output verb is having the desired effect.

It's going to need a little more tweaking to kill the ".com" off the end of the hostname. You also need to clarify, in the case of "host1.dom.com", whether you want the table name to be host1_table or host1.dom_table or host1_dom_table. if you choose the first, then you stand a chance of ending up with duplicate files overwriting each other; subdomains of two different organizations-- for example, mainhost.domain1.com and mainhost.domain2.com -- would land on the same mainhost_table output file.)

Re: how to break a lookup table into mulitple lookup tables ( rowwise or based on a field value)

woodcock — Fri, 10 Mar 2017 22:58:51 GMT

Like this:

|inputlookup MyCombinedLookup
| stats count BY host
| rex field=host "(?<tablename>[^\.]*)"
| eval tablename = tablename . "_table"
| map maxsearches=10000 search="|inputlookup MyCombinedLookup | search host=$host$ | outputlookup $tablename$"

Re: how to break a lookup table into mulitple lookup tables ( rowwise or based on a field value)

bkumarm — Mon, 13 Mar 2017 06:30:48 GMT

Thanks DalJeanis . actually I need the table names to be unique identifiers mapping to hostname and hostname.csv is the best choice.
as you had suggested the search query worked after a lil tweak.

Re: how to break a lookup table into mulitple lookup tables ( rowwise or based on a field value)

bkumarm — Mon, 13 Mar 2017 06:31:39 GMT

Thanks Woodcock. that was a straight answer to my question. It worked.

Re: how to break a lookup table into mulitple lookup tables ( rowwise or based on a field value)

bkumarm — Mon, 13 Mar 2017 06:59:46 GMT

A refined search query as below is what we used finally ..

Re: how to break a lookup table into mulitple lookup tables ( rowwise or based on a field value)

bkumarm — Mon, 13 Mar 2017 07:00:12 GMT

This is the final query that we used