Custom SWQL to Manage/Display UnDP Assignments

Version 1

    Hi, all having had many useful tips and downloads from Thwack over the last few years, I thought it was time to put something back.

     

    Recently I have been creating & assigning a load of UnDP's, and thought what a pain it is using the server application to view overall what is allocated, and even that is not particularly nice to use!

     

    I already have a panel in all my node summary pages to display UnDP, but that is not very useful for an overall view of what is assignment or the state of all values in a UnDP group.

     

    Hence, using hcy01's "CUSTOM SWQL TO MANAGE ASSIGNED APPLICATION MONITORS WITH SEARCH BAR" as a very helpful "template"(and this is a fantastic screen for SAM management that I've used for a while), I have knocked up the following screen:

     

     

     

     

     

    Here is the SWQL for the top data panel (Custom Query display resource) - use same code in Custom SWQL Query and Search SWQL Query boxes , just uncomment the lines below "Search String Below" (in orange) for the search box:

     

    SELECT c.Caption AS NodeName,

      '/Orion/NetPerfMon/NodeDetails.aspx?netobject=N:' + ToString(a.NodeID) AS [_LinkFor_NodeName],

      b.groupname AS UnDP_Group,

      'Edit' AS Edit,

      '/Orion/NPM/NodeCustomPollers.aspx?Nodes=' + ToString(a.NodeID) AS [_LinkFor_Edit],

      '/Orion/Nodes/images/icons/icon_edit.gif' AS [_IconFor_Edit],

      a.CustomPollerName AS UnDP_Name,

      a.CurrentValue AS Value,

      a.StatusDescription AS UnDP_Status,

      CASE

      WHEN a.Status=1  THEN '/Orion/images/StatusIcons/Small-Up.gif' 

      WHEN a.Status=9  THEN  '/Orion/images/StatusIcons/Small-Unmanaged.gif'

      WHEN a.Status=0  THEN  '/Orion/images/StatusIcons/Small-Unknown.gif'

      WHEN a.Status=12  THEN  '/Orion/images/StatusIcons/Small-Unreachable.gif'

      WHEN a.Status=3  THEN  '/Orion/images/StatusIcons/Small-Up-Warn.gif'

      WHEN a.Status=14  THEN  '/Orion/images/StatusIcons/Small-Critical.gif'

      WHEN a.Status=2  THEN  '/Orion/images/StatusIcons/Small-Down.gif'

      ELSE  '/Orion/images/StatusIcons/Small-Unreachable.gif'

      END AS [_IconFor_UnDP_Status],

      a.CustomPollerDescription AS Description_from_MIB,

      a.CustomPollerOid AS OID

     

    FROM Orion.NPM.CustomPollerAssignment a

    INNER JOIN Orion.NPM.CustomPollers b on a.CustomPollerID = b.CustomPollerID

    INNER JOIN Orion.Nodes c on a.Nodeid = c.Nodeid

    AND a.statusdescription <> 'Unmanaged'

    --

    -- Search String Below --

    --

    --WHERE UnDP_Status LIKE (CASE WHEN '${SEARCH_STRING}' LIKE 'US %' THEN SubString('${SEARCH_STRING}%',4,11) ELSE  ('dummy' + '${SEARCH_STRING}%')  END)

    --     OR NodeName LIKE (CASE WHEN '${SEARCH_STRING}' LIKE 'NN %' THEN ('%' + SubString('${SEARCH_STRING}%',4,25)) ELSE  ('dummy' + '${SEARCH_STRING}%')  END)

    --     OR UnDP_Group LIKE (CASE WHEN '${SEARCH_STRING}' LIKE 'UG %' THEN ('%' + SubString('${SEARCH_STRING}%',4,25)) ELSE  ('dummy' + '${SEARCH_STRING}%')  END)

    --     OR Description_from_MIB LIKE (CASE WHEN '${SEARCH_STRING}' LIKE 'DE %' THEN ('%' + SubString('${SEARCH_STRING}%',4,25)) ELSE  ('dummy' + '${SEARCH_STRING}%')  END)

    --     OR OID LIKE (CASE WHEN '${SEARCH_STRING}' LIKE 'OI %' THEN ('%' + SubString('${SEARCH_STRING}%',4,50)) ELSE  ('dummy' + '${SEARCH_STRING}%')  END)

    --     OR Value LIKE (CASE WHEN '${SEARCH_STRING}' LIKE 'VA %' THEN ('%' + SubString('${SEARCH_STRING}%',4,50)) ELSE  ('dummy' + '${SEARCH_STRING}%')  END)

    --     OR UnDP_Name LIKE '%${SEARCH_STRING}%'

    --

    ORDER BY UnDP_Group, Nodename

     

     

    Here is the HTML for the bottom instructions panel (Custom HTML diaplay resource):

     

    <p>

    <table style="width: 100%" cellpadding="0" cellspacing="0">

      <tr>

      <td><font color="#0007FF">NodeName</font></td>

      <td><font color="#000000">Click to to go to Node Summary Page</font></td>

       <td><font color="#000000">UnDP_Group</font></td>

      <td><font color="#000000">Name of UnDP Group as defined in UnDP application on main polling server</font></td>

      </tr>

     

      <tr>

      <td><font color="#0007FF">Edit</font></td>

      <td><font color="#000000">Click to edit UnDP assignments for NodeName</font></td>

      <td><font color="#000000">UnDP_Name</font></td>

      <td><font color="#000000">Name of poller as defined in UnDP application on main polling server</font></td>

      </tr>

           

      <tr>

      <td><font color="#000000">Value</font></td>

      <td><font color="#000000">The current value for the UnDP as returned from SNMP query of the OID on NodeName</font></td>

      <td><font color="#000000">UnDP_Status</font></td>

      <td><font color="#000000">UnDP Status - based upon UnDP Thresholds as defined in Orion Settings</font></td>

      </tr>

     

      <tr>

      <td><font color="#000000">Description_from_MIB</font></td>

      <td><font color="#000000"> Detailed description of the OID as returned by SNMP query of MIB table on NodeName</font></td>

      <td><font color="#000000">OID</font></td>

      <td><font color="#000000"> Unique OID number used by UnDP to query NodeName MIB table</font></td>

      </tr>

     

    </table>

     

    <p>

    <font color="#000000">Columns in</font><font color="#0007FF"> blue </font><font color="#000000">are clickable. Hold CTRL when clicking to open in a new tab, SHIFT to open in a new window.<br></font>

    </p>

     

    <p>

    <font color="#000000">NB. UnDP pollers that return a table of values (i.e. tabular OID) cannot display their values here, though their assignments will still be shown. Click on node link to see details of values in node summary screen p.<br><br></font>

    </p>

     

    <p><ul>

    <li>- The following columns are searchable:</li>

    <ul>

    <li> <b>NodeName</b>: Full or part of node name (Maximum 25 letters) </li>

    <li> <b>UnDP_Group</b>: Full or part of UnDP Group name (Maximum 25 letters) </li>

    <li> <b>UnDP_Name</b>: Full or part of UnDP name (Maximum 25 letters) </li>

    <li> <b>Value</b>: Full or part of Value (Maximum 25 letters) </li>

    <li> <b>UnDP_Status</b>: 'Up', 'Down', 'Warning', 'Critical', 'Unmanaged', 'Unknown', 'Unreachable'.</li>

    <li> <b>Description_from_MIB</b>: Full or part of MIB OID Description (Maximum 25 letters).</li>

    <li> <b>OID</b>: Full or part of MIB OID Number (Maximum 50 letters).</li>

    </ul>

     

    <li>- You can search individual columns like this:</li>

    <ul>

    <li> To search the <b>NodeName</b> column start your search with <b>"nn  "</b> followed by node name (full or part of node name, such as: <font color="#0007FF"> "nn mysqlserver1" or "nn mysql")</font>.</li>

    <li> To search the <b>UnDP_Group</b> column start your search with <b>"ug  "</b> followed by UnDP Group name (full or part of template name, such as: <font color="#0007FF"> "ug Checkpoint_Cluster" or "ug cluster")</font>.</li>

    <li> To search the <b>UnDP_Name</b> column just type full or part of UnDP name in the search box (Maximum 25 letters), such as: <font color="#0007FF"> "hastatus"</font>.</li>

    <li> To search the <b>Value</b> column start your search with <b>"va  "</b> followed by value (full or part of node name, such as: <font color="#0007FF"> "va standby" or "va stand")</font>.</li>

    <li> To search the <b>UnDP_Status</b> column start your search with <b>"us  "</b> followed by application status (full or part of status name, such as: <font color="#0007FF"> "us up", "us unmanaged" or "us unm")</font>.</li>

    <li> To search the <b>Description_from_MIB</b> column start your search with <b>"de  "</b> followed by MIB OID description (full or part of description, such as: <font color="#0007FF"> "de power")</font>.</li>

    <li> To search the <b>OID</b> column start your search with <b>"oi  "</b> followed by OID number (full or part of OID number, such as: <font color="#0007FF"> "oi 1.3.6.1.4.1.318.1.1.8.5.1.3")</font>.</li>

    </ul>

     

    </ul></p>

    <p>An entered search will remain for as long as the browser stays open or until the search is manually cleared, even though the results refresh on a timer.</p>

     

     

    I hope this is of some use (and that I have posted in the right place)!