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