Splunk Search

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

ebaums5467
Engager

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

alt text

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

alt text

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

0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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.

View solution in original post

DalJeanis
SplunkTrust
SplunkTrust

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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
Engager

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

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...