How to loop through results of a main inputlookup and combine with a child inputlookup?

[erwin_pader_1dc]

hi,

i have a main search-

|inputlookup wlaa_hosts.csv | eval Host=split(HostList,",") | stats count by Host

that results with-

Host           count
host1            1
host2            1
host3            1

i have another lookup that looks like-

MetricID                  AlertMsg
response_time             resp time > 10
error_count               error > 20

i want to loop through each of the host and attach it to every row in the child lookup, so that the result looks like-

Host     MetricID             AlertMsg
host1    response_time        resp time > 10
host1    error_count          error > 20
host2    response_time        resp time > 10
host2    error_count          error > 20

final step is to output each row to a KV Store lookup

i tried using the map command but it did not give me the desired result. appreciate very much for any help. thnx

[somesoni2 · Accepted solution]

Try like this

|inputlookup wlaa_hosts.csv | eval Host=split(HostList,",") | stats count by Host | join max=50 [| inputlookup secondlookup.csv | table MetricID AlertMsg ]

[sundareshr]

Try this

| inputlookup wlaa_hosts.csv | eval Host=split(HostList,",") | stats count by Host | inputlookup append=t secondfile.csv | fillnull Host value="settings" | eventstats values(if(eval(host="settings", MetricID, null()))) as MetricID values(if(eval(host="settings", AlertMsg, null()))) as AlertMsg by host | where Host!="settings" | eval z=mvzip(MetricID, AlertMsg, "#") | mvexpand z | rex field=z "(?<MetricID>[^#]+)#(?<AlertMsg>.+)" | fields - z