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

[bkumarm]

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.

[woodcock · Accepted solution]

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$"

[bkumarm]

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

[bkumarm]

This is the final query that we used

|inputlookup temp.csv | stats count BY host | eval tablename=host.".csv" | map maxsearches=10000 search="|inputlookup temp.csv | search host=$host$ | outputlookup $tablename$"

[DalJeanis]

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.)

[bkumarm]

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.

[bkumarm]

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

|inputlookup temp.csv | stats count BY host | eval tablename=host.".csv" | map maxsearches=10000 search="|inputlookup temp.csv | search host=$host$ | outputlookup $tablename$"

[gcusello]

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