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
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
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
Wow that's amazing!!!! So much quicker and I get all the info I needed.
Thanks very much, our OpsTeam will salute you! 🙂
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.