Hello,
I have a SQL query that displays the Pool Member status of our Load Balancers like below:
I have been asked to add a count to the table for each server name of how many EventType = 1 events have occurred. I can't figure out how to add that to my query. If possible, I would be very grateful if anyone could help me out on adding a count to the following query. I've been struggling with this all week without success. : (
SELECT GETDATE() "DateTime", n.Caption, State.Label "Server Name" , GroupName.Status "Load Balancer", Status.Status "Status"
FROM [dbo].[Nodes] n
JOIN (SELECT cpa.NodeID, cpl.rowid, cpl.Label FROM [dbo].[CustomPollerLabels] cpl
JOIN CustomPollerAssignment cpa ON (cpl.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID)
JOIN CustomPollers cp ON (cpa.PollerID = cp.PollerID) WHERE cp.UniqueName= 'svcGrpMemberState') State ON (n.NodeID = State.NodeID)
LEFT JOIN (SELECT cpa.NodeID, cps.rowid, cps.status FROM CustomPollerStatus cps
JOIN CustomPollerAssignment cpa ON (cps.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID)
JOIN CustomPollers cp on (cpa.PollerID = cp.PollerID) WHERE cp.UniqueName = 'svcGrpMemberGroupName') GroupName
ON (n.NodeID=GroupName.NodeID and State.RowID=GroupName.RowID)
LEFT JOIN (SELECT cpa.NodeID, cps.rowid, cps.status FROM CustomPollerStatus cps JOIN CustomPollerAssignment cpa ON (cps.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID)
JOIN CustomPollers cp on (cpa.PollerID = cp.PollerID) WHERE cp.UniqueName = 'svcGrpMemberServerName') ServerName
ON (n.NodeID=ServerName.NodeID and State.RowID=ServerName.RowID)
LEFT JOIN (SELECT cpa.NodeID, cps.rowid, cps.status, cps.DateTime FROM CustomPollerStatus cps
JOIN CustomPollerAssignment cpa ON (cps.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID)
JOIN CustomPollers cp on (cpa.PollerID = cp.PollerID) WHERE cp.UniqueName = 'svcGrpMemberState') Status
ON (n.NodeID=Status.NodeID and State.RowID=Status.RowID) WHERE GroupName.Status LIKE '%rdpricing%' AND n.Caption LIKE 'su%' AND Status.Status != 'Out of Service'
Thank you for any help on this one,
Tom
