How to exclude nodes from application availability report?

TreyJ

Tweaking a canned Application Availability report.  I have SAM querying a load-balanced IP for Exchange services, and also for individual Exchange servers.  I would like to exclude the individual servers from the report.  I've tried several SWQL statements, but cannot find the correct one.

Here is the current code from Orion SDK:

SELECT MAX(ApplicationStatus.TimeStamp) AS Month,
       Nodes.Caption  AS  NodeName,
       Nodes.DetailsUrl  AS  NodesDetailsUrl,
       Application.Name  AS  ApplicationName,
       Application.DetailsUrl  AS  ApplicationDetailsUrl,
       ( SUM( ApplicationStatus.PercentAvailability * ApplicationStatus.RecordCount ) / SUM( ApplicationStatus.RecordCount ) ) AS PercentAvailability
FROM Orion.APM.ApplicationStatus AS ApplicationStatus
INNER JOIN Orion.APM.Application AS Application
  ON Application.ApplicationID  =  ApplicationStatus.ApplicationID
INNER JOIN Orion.Nodes AS Nodes 
  ON Nodes.NodeID  =  Application.NodeID
WHERE ApplicationStatus.TimeStamp >= ADDMINUTE(MINUTEDIFF(GetDate(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()),  0))
GROUP BY Application.ApplicationID, Application.Name, Nodes.Caption, NodesDetailsUrl, ApplicationDetailsUrl
ORDER BY Nodes.Caption, Application.Name

Any assistance would be greatly appreciated!

KMSigma.SWI

I don't have any Exchange servers currently being monitored, but you'll need to add more WHERE clauses to filter out what you want.

SELECT MAX(ApplicationStatus.TimeStamp) AS Month
     , ApplicationStatus.Application.Node.Caption AS NodeName
     , ApplicationStatus.Application.Node.DetailsUrl AS NodesDetailsUrl
     , ApplicationStatus.Application.Name AS ApplicationName
     , ApplicationStatus.Application.DetailsUrl AS ApplicationDetailsUrl
     , ( SUM( ApplicationStatus.PercentAvailability * ApplicationStatus.RecordCount ) / SUM( ApplicationStatus.RecordCount ) ) AS PercentAvailability
FROM Orion.APM.ApplicationStatus AS ApplicationStatus
WHERE ApplicationStatus.TimeStamp >= ADDMINUTE(MINUTEDIFF(GetDate(), GETUTCDATE()), ADDMONTH(MONTHDIFF(0, GETDATE()),  0))
  AND ApplicationStatus.Application.Name = 'Microsoft Exchange' -- <-- Return only Exchange Services
  AND ApplicationStatus.Application.Node.Caption NOT LIKE '%EXCH%' -- <-- Exclude or include filter on a 'thing' to omit the individual servers
GROUP BY ApplicationStatus.Application.ApplicationID
       , ApplicationStatus.Application.Name
       , ApplicationStatus.Application.Node.Caption
       , ApplicationStatus.Application.Node.DetailsUrl
       , ApplicationStatus.Application.DetailsUrl</pre>I also got rid of your joins and used Navigation Properties instead.  I my opinion, it just makes it cleaner to read.