topic Re: Join two indexes in one search in Splunk Search

Join two indexes in one search

man03359 — Mon, 29 Jan 2024 13:08:55 GMT

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

Re: Join two indexes in one search

gcusello — Mon, 29 Jan 2024 13:26:28 GMT

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

Re: Join two indexes in one search

ITWhisperer — Mon, 29 Jan 2024 13:31:54 GMT

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.

Re: Join two indexes in one search

JohnEGones — Mon, 29 Jan 2024 15:56:17 GMT

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