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

[neilsmith2]

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.

[Arun_N_007 · Accepted solution]

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

[neilsmith2]

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?

[aweitzman]

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

[neilsmith2]

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

[Arun_N_007]

Please accept the Answer 🙂

[Arun_N_007]

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)

[Arun_N_007]

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

[neilsmith2]

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

[Arun_N_007]

Please accept the answer

[Arun_N_007]

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.