Splunk Search

How can I run this query more efficiently without using so many join commands?

kltest
Explorer

Hello,

I'm running the following query to combine data from two different sources and to create a table for our AppAssure monitoring:

host="AppAssure1" source="WinEventLog:AppAssureMonitoring" EventCode=350| fields ServerName AgentStatus Version LatestSnapshot IsPaused LatestSnapshotStatus  RepositoryName| dedup ServerName | rename RepositoryName AS LocalRepositoryName | 

join type=outer ServerName [search host="AppAssure2" source="WinEventLog:AppAssureMonitoring" EventCode=150 |fields ServerName ReplicatedStatus ReplicatedTimeStamp | dedup ServerName] | 

join type=outer ServerName [search host="AppAssure1" source="WinEventLog:AppAssureMonitoring" EventCode=250 |fields ServerName ExportedTimeStamp ExportedStatus | dedup ServerName| rename ExportedTimeStamp AS LocalExportedTimeStamp|rename ExportedStatus AS LocalExportedStatus ] | 

join type=outer ServerName [search host="AppAssure2" source="WinEventLog:AppAssureMonitoring" EventCode=250 |fields ServerName ExportedTimeStamp ExportedStatus | dedup ServerName | rename ExportedTimeStamp AS ReplicaExportedTimeStamp| rename ExportedStatus AS ReplicaExportedStatus] 

I'm aware that it's horribly inefficient, but can't see a way to get the same result without using the join command as I also need to rename the fields as I go etc. I have to run this query on multiple tables on the same dashboard and as you can imagine it takes quite a while to load.

Can anyone clever point me in the right direction of where to go from here?

Thanks,

Andy

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

source="WinEventLog:AppAssureMonitoring" (host="AppAssure1" EventCode=350 OR EventCode=250) OR (host="AppAssure2"  EventCode=150 OR EventCode=250) 
| fields ServerName EventCode host AgentStatus Version LatestSnapshot IsPaused LatestSnapshotStatus  RepositoryName ReplicatedStatus ReplicatedTimeStamp ExportedTimeStamp ExportedStatus
| dedup ServerName host EventCode 
| eval LocalRepositoryName=if(host="AppAssure1" AND EventCode="350",RepositoryName,null())
| eval LocalExportedTimeStamp=if(host="AppAssure1" AND EventCode="250",ExportedTimeStamp,null())
| eval LocalExportedStatus=if(host="AppAssure1" AND EventCode="250",ExportedStatus,null())
| eval ReplicaExportedTimeStamp=if(host="AppAssure2" AND EventCode="250",ExportedTimeStamp,null())
| eval ReplicaExportedStatus=if(host="AppAssure2" AND EventCode="250",ExportedStatus,null())
| stats values(*) as * by ServerName | fields - EventCode host

View solution in original post

somesoni2
Revered Legend

Give this a try

source="WinEventLog:AppAssureMonitoring" (host="AppAssure1" EventCode=350 OR EventCode=250) OR (host="AppAssure2"  EventCode=150 OR EventCode=250) 
| fields ServerName EventCode host AgentStatus Version LatestSnapshot IsPaused LatestSnapshotStatus  RepositoryName ReplicatedStatus ReplicatedTimeStamp ExportedTimeStamp ExportedStatus
| dedup ServerName host EventCode 
| eval LocalRepositoryName=if(host="AppAssure1" AND EventCode="350",RepositoryName,null())
| eval LocalExportedTimeStamp=if(host="AppAssure1" AND EventCode="250",ExportedTimeStamp,null())
| eval LocalExportedStatus=if(host="AppAssure1" AND EventCode="250",ExportedStatus,null())
| eval ReplicaExportedTimeStamp=if(host="AppAssure2" AND EventCode="250",ExportedTimeStamp,null())
| eval ReplicaExportedStatus=if(host="AppAssure2" AND EventCode="250",ExportedStatus,null())
| stats values(*) as * by ServerName | fields - EventCode host

kltest
Explorer

Wow that's amazing!!!! So much quicker and I get all the info I needed.

Thanks very much, our OpsTeam will salute you! 🙂

0 Karma

mhpark
Path Finder

Try to fetch it all at once and then merge;

source="WinEventLog:AppAssureMonitoring" 
(host="AppAssure1" AND EventCode=350)
OR (host="AppAssure2" AND EventCode=150)
| dedup host, EventCode, ServerName
| eval Repo-{host} = RepositoryName
| stats values(AgentStatus) as AgentStatus values(Version) as Version values(Repo-AppAssure1) as LocalRepositoryName(..blahblah)  by ServerName

for the second;

source="WinEventLog:AppAssureMonitoring" EventCode=250
(host="AppAssure1" OR host="AppAssure2")
| dedup host, EventCode, ServerName
| eval ExportTime-{host} = ExportedTimeStamp
| eval ExportStatus-{host} = ExportedStatus
| stats values(ExportTime-AppAssure1) as LocalExportedTimeStamp (..blahblah) by ServerName

And if you need it faster,
you can save the search as a report and accelerate it,
or schedule the search and save the results in a lookup.

0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...