Splunk Search

Join two indexes in one search

man03359
Communicator

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
Labels (3)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

JohnEGones
Communicator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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

Get Updates on the Splunk Community!

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...