topic Re: Using lookups against multiple sources (with differing field names for the same value) in Splunk Search

Using lookups against multiple sources (with differing field names for the same value)

neilsmith2 — Mon, 28 Sep 2020 19:55:44 GMT

Hi, I'm looking for an explanation of the best/most efficient way to perform a lookup against multiple sources/field names. I have a lookup table with a bunch of IP addresses that I want to find evidence of in logs.
e.g.
lookup : IPaddresses
sourcetype: source1 fieldname=src_ip
sourcetype: source2 fieldname=source_address

I've tried various methiods but I can't seem to get what I'm looking for!
TIA.

Re: Using lookups against multiple sources (with differing field names for the same value)

Arun_N_007 — Wed, 20 May 2015 08:35:54 GMT

Hi Neil,

Could you please give Sample data in lookup and sourcetypes in table format???

While using lookup commands you can use aliasing like

Table A -> Lookup

|lookup File.csv field as field1

where field1 will be the filed of Table A.

Re: Using lookups against multiple sources (with differing field names for the same value)

neilsmith2 — Mon, 28 Sep 2020 19:55:46 GMT

Hi Arun, thanks for the reply. Sure please see below (I have obfuscated the actual IP addresses/hostnames etc but the format remains the same). So I am trying to match values in the Address field in lookup.csv to the srcip field in source1 and/or the source_address field in source2

Lookup.csv
Address Time Attack
1.1.1.1 15/05/2015 09:07 test1
1.1.1.2 14/05/2015 00:43 test2
1.1.1.3 13/05/2015 09:07 RA SCAN Unusually fast Terminal Server Traffic Inbound
1.1.1.4 14/05/2015 13:44 Sipvicious Scan
1.1.1.5 16/05/2015 18:47 source disclosure vulnerability
1.1.1.6 03/05/2015 09:21 Sipvicious Scan

Source1 sample event
May 20 09:20:24 hostname auditd: date="2015-05-20 09:20:24 +0000",fac=f_kernel_ipfilter,area=a_general_area,type=t_nettraffic,pri=p_major,hostname=hostname.nm,event="session end",application=All-TCP-UDP-clustername-cluster,netsessid=abb23555c51bf,srcip=1.1.1.3,srcport=61759,srczone=vlan1674,protocol=6,dstip=10.10.10.10,dstport=3424,dstzone=vlan1,bytes_written_to_client=69500,bytes_written_to_server=6372,rule_name=permit-any-any,cache_hit=0,start_time="2015-05-20 09:19:59 +0000"

Source2 sample event
May 20 09:25:48 172.1.1.1 1 2015-05-20T10:25:48.243 hostname RT_FLOW - RT_FLOW_SESSION_CLOSE [junos@osversion reason="TCP FIN" source-address="1.1.1.5" source-port="50764" destination-address="10.1.1.1" destination-port="55551" service-name="None" nat-source-address="10.1.1.0" nat-source-port="50764" nat-destination-address="101.1.0" nat-destination-port="55551" src-nat-rule-name="None" dst-nat-rule-name="None" protocol-id="6" policy-name="policyname" source-zone-name="zonename" destination-zone-name="zonename2" session-id-32="43321" packets-from-client="19" bytes-from-client="10098" packets-from-server="17" bytes-from-server="3188" elapsed-time="3" application="UNKNOWN" nested-application="UNKNOWN" username="N/A" roles="N/A" packet-incoming-interface="reth1.0"]

Re: Using lookups against multiple sources (with differing field names for the same value)

Arun_N_007 — Wed, 20 May 2015 10:05:27 GMT

Hi Neil,

While Joining with Source1 it should be like,

sourcetype=source1|lookup Lookup.csv Address as srcip

For Source2

sourcetype=source2|lookup Lookup.csv Address as source-address

Re: Using lookups against multiple sources (with differing field names for the same value)

Arun_N_007 — Wed, 20 May 2015 10:07:07 GMT

If you have given name to lookup in Splunk you can use its name. in lookup command instead of using .csv

Re: Using lookups against multiple sources (with differing field names for the same value)

neilsmith2 — Wed, 20 May 2015 10:13:55 GMT

Thanks Arun, I can get the lookup working indiviually, i.e. one search per source, what I was really looking for is one search that will search and match against both sources, is this possible?

Re: Using lookups against multiple sources (with differing field names for the same value)

Arun_N_007 — Mon, 28 Sep 2020 19:59:36 GMT

Ok why dont you rename srcip and source-address to other variable like

sourcetype=source1 sourcetype=source2|eval srcIpAddress=if(isnotnull(srcip),srcip,if(isnotnull(source-address),source-address,NULL))|lookup Lookup.csv Address as srcIpAddress

Or you can join it twice like below

But make sure that you will take backup of Time,Attack fields before 2nd join (Because they will be replace in the second join)

sourcetype=source1 sourcetype=source2|lookup Lookup.csv Address as srcip|rename Time as temp_Time,Attack as temp_Attack|lookup Lookup.csv Address as source-address|eval Attack=if(isnotnull(temp_Attack),temp_Attack,Attack)|eval Attack=if(isnotnull(temp_Time),temp_Time,Time)

Re: Using lookups against multiple sources (with differing field names for the same value)

aweitzman — Mon, 28 Sep 2020 19:55:54 GMT

A better way to do what @Arun_N_007 suggests is to use the coalesce function, which will allow you to collapse two differently-named fields into one depending on their existence in one sourcetype or another:

sourcetype=source1 OR sourcetype=source2
| eval Address=coalesce(srcip,source-address)
| lookup Lookup.csv Address ...

Re: Using lookups against multiple sources (with differing field names for the same value)

neilsmith2 — Mon, 28 Sep 2020 19:59:48 GMT

Many thanks both; those answers work nicely. Currently I'm having a play with:

sourcetype="source1" OR sourcetype="source2" | eval srcIPAddress=coalesce(src_ip,source_address)| eval DestPort=coalesce(dest_port,destination_port) | lookup Lookup Address as srcIPAddress OUTPUT Address as IPMatch, Attack as Attack_name | stats count by IPMatch, Attack_name, DestPort

Improvements to this most welcome (I have no programming and very little scripting experience!).

Re: Using lookups against multiple sources (with differing field names for the same value)

Arun_N_007 — Wed, 20 May 2015 15:12:32 GMT

Please accept the answer

Re: Using lookups against multiple sources (with differing field names for the same value)

Arun_N_007 — Thu, 21 May 2015 04:29:06 GMT

Please accept the Answer 🙂