How to edit my search to join two indexes and two sourcetypes together and include data from one sourcetype into another?

[ebaums5467]

Hello Splunkers!

I'm scratching my head trying to find out how to join two different indexes and two different sourcetypes together and would like to extract the user_name field data from sourcetype B into sourcetype A's table. This way I can know what user corresponds to a given IP address (this is all historical data by the way).

The first syntax has Firewall data coming into Splunk and gives you a table with 7 columns sorted by count:

index=INDEX-A policy_name="Policy-A" sourcetype=SOURCE TYPE A action=DROP threat_severity=CRITICAL (source_address=192.168.1* OR source_address=192.168.2* OR source_address=192.168.3* OR source_address=192.168.4*) | stats count, values(source_zone_name) as Source_Zone, values(destination_address) as Destination_Address, values(destination_port) as Destination_Port, values(attack_name) as Attack_Name, values(threat_severity) as Threat_Severity by source_address | sort -count

The second syntax has VPN data coming into Splunk and returns user name data for a corresponding IP address:

index=INDEX-B sourcetype=SOURCE TYPE B source_address="192.168.1.50" Tunneling | return user_name

Essentially, I would like to see a new column called user_name with the user name data all in one search even though they are two different indexes and sourcetypes.

Many thanks and Happy Splunking!!

[DalJeanis]

Follow the link provided by @martin_mueller for one part of your solution.

I believe that your second search, however, is missing the _time element. Unless those users have static IP addresses, you may need to identify how to limit the match to the right time frame.

[martin_mueller · Accepted solution]

See this excellent writeup: https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...

TL;DR: Search for A OR B, and do a | stats count, values(), etc. by join_field using fields from A and B.

[ebaums5467]

Martin- your answer helped me tweak my search! The final working Splunk Search is below:

(index=INDEX-A threat_severity=HIGH OR threat_severity=CRITICAL) OR (sourcetype="SOURCETYPE-B") (source_address=192.168.1* OR source_address=192.168.2* OR source_address=192.168.3* OR source_address=192.168.4*) | stats count values(user_name) as User_Name, values(source_zone_name) as Source_Zone, values(destination_address) as Destination_Address, values(destination_port) as Destination_Port, values(attack_name) as Attack_Name, values(threat_severity) as Threat_Severity by source_address | search Source_Zone=* User_Name=* | sort -count