topic How to join two tables for more data in Splunk Search

How to join two tables for more data

JandrevdM — Thu, 03 Oct 2024 09:21:14 GMT

Good day,

I have done a join on two indexes before to add more information to one event. example get department for a user from network events. But now I want to add two indexes to give me more data.
Example index one will display:
host 1 10.0.0.2
host 2 10.0.0.3
And index two will display:
host 3 10.0.0.4
host 1 10.0.0.2
What I want is:
host 1 10.0.0.2
host 2 10.0.0.3
host 3 10.0.0.4

index=db_azure_activity sourcetype=azure:monitor:activity change_type="virtual machine" | rename "identity.authorization.evidence.roleAssignmentScope" as subscription | dedup object | where command!="MICROSOFT.COMPUTE/VIRTUALMACHINES/DELETE" | table change_type object resource_group subscription command _time | sort object asc index=* sourcetype=o365:management:activity | rename "PropertyBag{}.AssessmentStatusPerInitiative{}.ResourceName" as ResourceName | rename "PropertyBag{}.AssessmentStatusPerInitiative{}.CloudProvider" as CloudProvider | rename "PropertyBag{}.AssessmentStatusPerInitiative{}.ResourceType" as ResourceTypes | rename "PropertyBag{}.AssessmentStatusPerInitiative{}.EventType" as EventType | where ResourceTypes="Microsoft.Compute/virtualMachines" OR ResourceTypes="microsoft.compute/virtualmachines" | eval object=mvdedup(split(ResourceName," ")) | eval Provider=mvdedup(split(CloudProvider," ")) | eval Type=mvdedup(split(ResourceTypes," ")) | dedup object | where EventType!="Microsoft.Security/assessments/Delete" | table object, Provider, Type * | sort object asc

Re: How to join two tables for more data

gcusello — Thu, 03 Oct 2024 09:27:38 GMT

Hi @JandrevdM ,

Splunk has the join command but I don't hint it because it's very slow and requires many resources.

if you have less than 50,000 results in the second search, you could use this solution joining events using stats command:

index=db_azure_activity sourcetype=azure:monitor:activity change_type="virtual machine" | rename "identity.authorization.evidence.roleAssignmentScope" AS subscription | dedup object | where command!="MICROSOFT.COMPUTE/VIRTUALMACHINES/DELETE" | table change_type object resource_group subscription command _time | sort object asc | append [ search index=* sourcetype=o365:management:activity | rename "PropertyBag{}.AssessmentStatusPerInitiative{}.ResourceName" as ResourceName "PropertyBag{}.AssessmentStatusPerInitiative{}.CloudProvider" as CloudProvider "PropertyBag{}.AssessmentStatusPerInitiative{}.ResourceType" as ResourceTypes "PropertyBag{}.AssessmentStatusPerInitiative{}.EventType" as EventType | where ResourceTypes="Microsoft.Compute/virtualMachines" OR ResourceTypes="microsoft.compute/virtualmachines" | eval object=mvdedup(split(ResourceName," ")), Provider=mvdedup(split(CloudProvider," ")), Type=mvdedup(split(ResourceTypes," ")) | dedup object | where EventType!="Microsoft.Security/assessments/Delete" | table object, Provider, Type * | sort object asc ] | stats values(*) AS * BY object

eventually limiting the fields to display related to your requirements.

Ciao.

Giuseppe