Splunk Search

How to combine two lookups?

POR160893
Builder

On Splunk, I have the following 2 searches:

1)

`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

2)

| inputlookup laso_lab_networks.csv
| join type=inner LAB_ID
[| inputlookup laso_lab_firewalls.csv
| eval Firewall = split(Firewall, ",")
| mvexpand Firewall]
| dedup Firewall
| eval Firewall = mvindex(split(Firewall, "."), 0)
| fields LAB_ID, Lab_Mgr, Firewall

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

Labels (5)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

 

 

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...