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

[kltest]

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

[somesoni2 · Accepted solution]

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]

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

Thanks very much, our OpsTeam will salute you! 🙂

[mhpark]

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.