How to combine two lookups?

POR160893 — Tue, 23 May 2023 14:54:55 GMT

On Splunk, I have the following 2 searches:

`ABC_logs(traffic)` user != "unknown" src_ip IN (*) dest_ip IN (*) | stats values(src_ip) values(src_zone) values(dest_ip) values(dest_port) values(app) values(transport) values(session_end_reason) values(user) by host rule action | rename values(*) as * | dedup src_ip, dest_ip | rename src_ip as device_ip_address | fields device_ip_address, dest_ip, user, host [search index=DEF sourcetype = asset_registry_2 | dedup device_ip_address | fields device_ip_address, host ]

AND

I need to write a single query that would LINK both these queries such that the field "Firewall" from search 1 is joined to the field "host" from search 2.

I appreciate any help you can offer

Re: How to combine two lookups?

yuanliu — Tue, 23 May 2023 16:34:30 GMT

Both searches are very confusing, and seem overly complicated. I took the liberty to simplify. (With the susbsearch in the main search, the first search should also be more efficient.) But really if a good use of the lookup laso_lab_firewalls.csv is to match host name, I wonder if it is much more efficient to actually produce the lookup with host as one of keys. Lookup is a binary tree, whereas inputlookup forces you to use other, less efficient commands.

Anyway, the main idea is to stitch the two together, then do values operation on non-overlapping fields.

`ABC_logs(traffic)` user != "unknown" [search index=DEF sourcetype = asset_registry_2 | dedup device_ip_address | fields device_ip_address, host | rename device_as src_ip] | stats values(src_ip) values(src_zone) values(dest_ip) values(dest_port) values(app) values(transport) values(session_end_reason) values(user) by host rule action | rename values(*) as * | rename src_ip as device_ip_address | fields device_ip_address, dest_ip, user, host | append [| inputlookup laso_lab_firewalls.csv | eval Firewall = split(Firewall, ",") | mvexpand Firewall | dedup Firewall | eval host = mvindex(split(Firewall, "."), 0) | fields LAB_ID, Lab_Mgr, host] | stats values(device_ip_address) as device_ip_address values(dest_ip) as dest_ip values(user) as user values(LAB_ID) as LAB_ID values(Lab_Mgr) as Lab_Mgr by host

topic How to combine two lookups? in Splunk Search

How to combine two lookups?

Re: How to combine two lookups?