topic How to combine two searches/data sets into one table? in Splunk Search

How to combine two searches/data sets into one table?

Apples — Tue, 13 Jun 2023 13:50:51 GMT

I have two searches/data sets that I would like to combine into a table, and am not entirely sure on what the correct process of completing the task is. I would like to use the Mandiant indicators/information and another search to look for activity that occurred and getting the data from both into one table with a total count of detected activity. If anyone could provide assistance or a recommendation with this matter it would be much appreciated.

First Search (Fields Needed: src_ip, dest, City, Country

index=pan_logs OR index=estreamer dest="*"
| iplocation src_ip | stats count by src_ip dest City Country

Second Search (Fields Needed: src_ip, category, mscore, type, malware, threat_actor

First Variation

| inputlookup mandiant_master_lookup | search type=ipv4 | eval src_ip=_key | table category mscore type malware threat_actor

Second Variation

| lookup mandiant_master_lookup _key as src_ip output category mscore type malware threat_actor

Attempted Join that didn't work

|index=pan_logs OR index=estreamer dest="*"
| iplocation src_ip | stats count by src_ip dest City Country
| join type=outer indicator [inputlookup mandiant_master_lookup | eval src_ip=_key | table src_ip category mscore type malware threat_actor]

Search that was Close, but needed additional iplocation data and action from device:

index=pan_logs OR index=estreamer dest="*"
| lookup mandiant_master_lookup _key as src_ip output category mscore type malware threat_actor
| fillnull value=""
| search type=ipv4 | makemv delim=";" category
| stats count by src_ip dest category mscore severity type malware threat_actor

Re: How to combine two searches/data sets into one table?

yuanliu — Wed, 14 Jun 2023 04:55:47 GMT

I'm confused. In your first search, iplocation is shown as a command whose argument is an IP address. (But the output is not being used in that stats command.) Then you mentioned that the last search is working (as look should be used), except you need iplocation data. What is preventing you from adding that data using iplocation? Like

index=pan_logs OR index=estreamer dest="*" | lookup mandiant_master_lookup _key as src_ip output category mscore type malware threat_actor | fillnull value="" | search type=ipv4 | makemv delim=";" category | stats count by src_ip dest category mscore severity type malware threat_actor | iplocation src_ip

Re: How to combine two searches/data sets into one table?

Apples — Thu, 15 Jun 2023 13:32:06 GMT

I thought that the iplocation command added the City and Country fields when being ran, which is why I added those fields to the stats count. You are right that I can just add it to the end which slipped my mind at the time. I would have also liked to get the device action from the initial search, but this is good enough. Thank you for your assistance.

Re: How to combine two searches/data sets into one table?

yuanliu — Thu, 15 Jun 2023 16:01:01 GMT

If the initial search has a field named device_action, you can pass it just like other fields. If every event of interest has device_action (fully populated), include it in groupby

index=pan_logs OR index=estreamer dest="*" | lookup mandiant_master_lookup _key as src_ip output category mscore type malware threat_actor | fillnull value="" | search type=ipv4 | makemv delim=";" category | stats count by src_ip dest category mscore severity type malware threat_actor device_action | iplocation src_ip

If device_action is not fully populated, you can use values(), or populate missing values with another fillnull.

index=pan_logs OR index=estreamer dest="*" | lookup mandiant_master_lookup _key as src_ip output category mscore type malware threat_actor | fillnull value="" | search type=ipv4 | makemv delim=";" category | stats count values(device_action) as device_action by src_ip dest category mscore severity type malware threat_actor | iplocation src_ip

index=pan_logs OR index=estreamer dest="*" | fillnull device_action value="N/A" | lookup mandiant_master_lookup _key as src_ip output category mscore type malware threat_actor | fillnull value="" | search type=ipv4 | makemv delim=";" category | stats count by src_ip dest category mscore severity type malware threat_actor device_action | iplocation src_ip

Re: How to combine two searches/data sets into one table?

Apples — Thu, 15 Jun 2023 18:08:28 GMT

Thank you for the detailed responses, the answer provided is everything I needed. I think I had misunderstood the lookup/join, as I thought that it would only carry over the src_ip field from the first search that was matched in both searches.