How to join two tables for more data

[JandrevdM]

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

[gcusello · Accepted solution]

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