Join two indexes in one search

[man03359]

I am noob with Splunk.

I am trying to join two indexes in one search -

index="idx-enterprise-tools" sourcetype="spectrum:alarm:json"
| eval Host=substr(host,1,9)

Second Index -

index=idx-sec-cloud sourcetype=rubrik:json  NOT (summary="*on demand backup*" OR custom_details.clusterName="ART1RBRK100P" OR custom_details.clusterName="ONT1RBRK100P" OR custom_details.clusterName="GRO1RBRK100P")
(custom_details.eventName="Snapshot.BackupFailed" NOT (custom_details.errorId="Oracle.RmanStatusDetailsEmpty" OR custom_details.errorId="Vmware.VmwareCBTCorruption")) 
OR (custom_details.eventName="Mssql.LogBackupFailed") 
OR (custom_details.eventName="Snapshot.BackupFromLocationFailed" NOT (custom_details.errorId="Fileset.FailedDataThresholdNas" OR custom_details.errorId="Fileset.FailedFileThresholdNas" OR custom_details.errorId="Fileset.FailedToFindFilesNas")) 
OR (custom_details.eventName="Vmware.VcenterRefreshFailed")
OR (custom_details.eventName="Hawkeye.IndexOperationOnLocationFailed")
OR (custom_details.eventName="Hawkeye.IndexRetryFailed")
OR (custom_details.eventName="Storage.SystemStorageThreshold")
OR (custom_details.eventName="ClusterOperation.DiskLost")
OR (custom_details.eventName="ClusterOperation.DiskUnhealthy")
OR (custom_details.eventName="Hardware.DimmError")
OR (custom_details.eventName="Hardware.PowerSupplyNeedsReplacement")
OR (custom_details.location="*/MSSQLSERVER")
| rename custom_details.eventName as EventName custom_details.errorId as ErrorCode custom_details.clusterName as ClusterName custom_details.location as LocationName
| eventstats count(eval(custom_details.location="*/MSSQLSERVER")) as MsSqlServer by summary

I am trying like this but I do not see any events where as both the indexes are giving events for same time frame-

index="idx-enterprise-tools" sourcetype="spectrum:alarm:json"
| eval Host=substr(host,1,9)
| join host
    [ search index=idx-sec-cloud sourcetype=rubrik:json  NOT (summary="*on demand backup*" OR custom_details.clusterName="ART1RBRK100P" OR custom_details.clusterName="ONT1RBRK100P" OR custom_details.clusterName="GRO1RBRK100P")
(custom_details.eventName="Snapshot.BackupFailed" NOT (custom_details.errorId="Oracle.RmanStatusDetailsEmpty" OR custom_details.errorId="Vmware.VmwareCBTCorruption")) 
OR (custom_details.eventName="Mssql.LogBackupFailed") 
OR (custom_details.eventName="Snapshot.BackupFromLocationFailed" NOT (custom_details.errorId="Fileset.FailedDataThresholdNas" OR custom_details.errorId="Fileset.FailedFileThresholdNas" OR custom_details.errorId="Fileset.FailedToFindFilesNas")) 
OR (custom_details.eventName="Vmware.VcenterRefreshFailed")
OR (custom_details.eventName="Hawkeye.IndexOperationOnLocationFailed")
OR (custom_details.eventName="Hawkeye.IndexRetryFailed")
OR (custom_details.eventName="Storage.SystemStorageThreshold")
OR (custom_details.eventName="ClusterOperation.DiskLost")
OR (custom_details.eventName="ClusterOperation.DiskUnhealthy")
OR (custom_details.eventName="Hardware.DimmError")
OR (custom_details.eventName="Hardware.PowerSupplyNeedsReplacement")
OR (custom_details.location="*/MSSQLSERVER")
]
| rename custom_details.eventName as EventName custom_details.errorId as ErrorCode custom_details.clusterName as ClusterName custom_details.location as LocationName
| eventstats count(eval(custom_details.location="*/MSSQLSERVER")) as MsSqlServer by summary

[JohnEGones]

Hi man03359,

I'd say nothing wrong with being a noob, we all are at some point, continuously.

To your question, while perhaps not quite directly responsive: you might want to create tags (and review the exisitng ones) for certain types of data that you know lies in different indexes and sourcetypes. This can be a way of creating a searchable correlation that is properly time-indexed; and then you can pivot to the specific index/sourcetype.

Some explanation on tagging/eventypes here: 

what is the basic difference between tags and even... - Splunk Community
About tags and aliases - Splunk Documentation
About event types - Splunk Documentation

[ITWhisperer]

Joins (with the join command) are generally best avoided as they are slow and have limitations. However, if you want to continue down this route, you should also note that field names are case sensitive, so if you were expecting Host from one set of events to be "joined" with host in the other set of events, they would have to share exactly the same field name. Without you sharing some sample events, it is not easy to determine whether this is your issue.

[gcusello · Accepted solution]

Hi @man03359,

probably your search doesn't run because you renamed a field and used the previous field name.

Anyway, in general, avoid to use join because it's avery slow search, try using stats:

(index="idx-enterprise-tools" sourcetype="spectrum:alarm:json") OR 
(index=idx-sec-cloud sourcetype=rubrik:json  NOT (summary="*on demand backup*" OR custom_details.clusterName="ART1RBRK100P" OR custom_details.clusterName="ONT1RBRK100P" OR custom_details.clusterName="GRO1RBRK100P")
(custom_details.eventName="Snapshot.BackupFailed" NOT (custom_details.errorId="Oracle.RmanStatusDetailsEmpty" OR custom_details.errorId="Vmware.VmwareCBTCorruption")) 
OR (custom_details.eventName="Mssql.LogBackupFailed") 
OR (custom_details.eventName="Snapshot.BackupFromLocationFailed" NOT (custom_details.errorId="Fileset.FailedDataThresholdNas" OR custom_details.errorId="Fileset.FailedFileThresholdNas" OR custom_details.errorId="Fileset.FailedToFindFilesNas")) 
OR (custom_details.eventName="Vmware.VcenterRefreshFailed")
OR (custom_details.eventName="Hawkeye.IndexOperationOnLocationFailed")
OR (custom_details.eventName="Hawkeye.IndexRetryFailed")
OR (custom_details.eventName="Storage.SystemStorageThreshold")
OR (custom_details.eventName="ClusterOperation.DiskLost")
OR (custom_details.eventName="ClusterOperation.DiskUnhealthy")
OR (custom_details.eventName="Hardware.DimmError")
OR (custom_details.eventName="Hardware.PowerSupplyNeedsReplacement")
OR (custom_details.location="*/MSSQLSERVER"))
| stats 
   count(eval(custom_details.location="*/MSSQLSERVER")) as MsSqlServer
   BY host

Ciao.

Giuseppe