Calling all Thwackers who are good with SQL as well. I am not a SQL guru although I know "Select * from table" and know that I should not use "Drop Table" ;-)
Due to lack of OOB stats collection for Current user count on my checkpoint firewalls, I had to apply bunch of custom poller through UnDP. We all know that reporting on these custom pollers is difficult. There is also a feature request to show data in a better format (mostly tabular).
So after dabbling a bit trying to turn similar queries into SQL and using one of the script here at thwack which assists in getting data in columnar format, I was able to build something. I do not have any way to confirm the data output, I am adding the script below to know if I have it right.
- Will it sample data from the past 24 hours
- Will it get the MAX from the columns selected
- Is getting Statistic_detail column better than Daily or any other table? I used it because I saw the values were updated every polling interval. The original script was polling status table which had only last updated status.
=========
SELECT
A.NodeName AS NodeName,
MAX(CASE WHEN Poller_Name='fwNumConn' THEN CAST(Status AS varchar) ELSE '' END) fwNumConn,
MAX(CASE WHEN Poller_Name='fwPeakNumConn' THEN CAST(Status AS varchar) ELSE '' END) fwPeakNumConn,
MAX(CASE WHEN Poller_Name='ipsofwConnTableLimit' THEN CAST(Status AS varchar) ELSE '' END) ipsofwConnTableLimit,
MAX(CASE WHEN Poller_Name='vsxCountersConnNum_VSID3' THEN CAST(Status AS varchar) ELSE '' END) vsxCountersConnNum_VSID3,
MAX(CASE WHEN Poller_Name='vsxCountersConnNum_VSID4' THEN CAST(Status AS varchar) ELSE '' END) vsxCountersConnNum_VSID4,
MAX(CASE WHEN Poller_Name='vsxCountersConnNum_VSID5' THEN CAST(Status AS varchar) ELSE '' END) vsxCountersConnNum_VSID5,
MAX(CASE WHEN Poller_Name='vsxCountersConnNum_VSID6' THEN CAST(Status AS varchar) ELSE '' END) vsxCountersConnNum_VSID6,
MAX(CASE WHEN Poller_Name='vsxCountersConnNum' THEN CAST(Status AS varchar) ELSE '' END) vsxCountersConnNum
FROM
(
SELECT
Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.UniqueName AS Poller_Name, CustomNodePollerStatus_CustomPollerStatistics_Detail.Status AS Status
FROM
((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID)) INNER JOIN CustomPollers CustomNodePollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerID = CustomNodePollers_CustomPollers.CustomPollerID)) INNER JOIN CustomPollerStatistics_Detail CustomNodePollerStatus_CustomPollerStatistics_Detail ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatus_CustomPollerStatistics_Detail.CustomPollerAssignmentID)
WHERE
(
( DateTime BETWEEN 42687.5221064815 AND 42688.5416666667 ) OR
(CustomNodePollers_CustomPollers.UniqueName = 'fwNumConn') OR
(CustomNodePollers_CustomPollers.UniqueName = 'fwPeakNumConn') OR
(CustomNodePollers_CustomPollers.UniqueName = 'ipsofwConnTableLimit') OR
(CustomNodePollers_CustomPollers.UniqueName = 'vsxCountersConnNum_VSID3') OR
(CustomNodePollers_CustomPollers.UniqueName = 'vsxCountersConnNum_VSID4') OR
(CustomNodePollers_CustomPollers.UniqueName = 'vsxCountersConnNum_VSID5') OR
(CustomNodePollers_CustomPollers.UniqueName = 'vsxCountersConnNum_VSID6') OR
(CustomNodePollers_CustomPollers.UniqueName = 'vsxCountersConnNum')
)
AND
(
(CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)
)
)A
GROUP BY NodeName