0 Replies Latest reply on Nov 16, 2016 1:51 PM by Al Ma

    Suggestions on SQL query for custom poller data

    Al Ma

      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