Splunk Search

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

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 policyname="Policy-A" sourcetype=SOURCE TYPE A action=DROP threatseverity=CRITICAL (sourceaddress=192.168.1* OR sourceaddress=192.168.2* OR sourceaddress=192.168.3* OR sourceaddress=192.168.4*) | stats count, values(sourcezonename) as SourceZone, values(destinationaddress) as DestinationAddress, values(destinationport) as DestinationPort, values(attackname) as AttackName, values(threatseverity) as ThreatSeverity by sourceaddress | 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 sourceaddress="192.168.1.50" Tunneling | return username

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

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

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

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

Engager

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

(index=INDEX-A threatseverity=HIGH OR threatseverity=CRITICAL) OR (sourcetype="SOURCETYPE-B") (sourceaddress=192.168.1* OR sourceaddress=192.168.2* OR sourceaddress=192.168.3* OR sourceaddress=192.168.4) | stats count values(username) as UserName, values(sourcezonename) as SourceZone, values(destinationaddress) as DestinationAddress, values(destinationport) as DestinationPort, values(attackname) as AttackName, values(threatseverity) as ThreatSeverity by sourceaddress | search Source_Zone= User_Name=* | sort -count