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.




      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


      Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.UniqueName AS Poller_Name, CustomNodePollerStatus_CustomPollerStatistics_Detail.Status AS Status
      ((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)
      ( 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')
      (CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)
      GROUP BY NodeName