MaKaBe

SolarWinds offre des pages par défaut avec de (trop) noébreuses informations mais parfois il manque quand même quelques informations. Les requêtes suivantes vont ajouter des informations sur les pages des nodes et de volumes.

Page node details

Ces requêtes sont à ajouter dans les pages de type node details.

Affichage des groupes

Il facile de savoir ce qui constitue un groupe, il suffit de déplier l’arborescence du groupe. Il est par contre moins facile de déterminer à quel groupe appartient un node spécifique, c’est tout l’intérêt de cette requête.

SELECT 
    cm.Container.Name AS [Group_Name]
    ,cm.Container.DetailsUrl AS [_LinkFor_Group_Name]
FROM 
    Orion.ContainerMembers cm 
WHERE cm.FullName = '${Caption}'

Historique des alertes

Une page simple mais vraiment efficace qui affiche l’historique des alertes du node. A noter qu’il est possible de retrouver ces informations par la page Message center, simplement cette requête donne l’info sans quitter la page du node.

SELECT 
    AlertHistory.AlertObjects.AlertConfigurations.Name AS [Alert Name], 
    Message, 
    AlertHistory.AlertObjects.EntityCaption AS [Triggering Object],
    ToLocal(Timestamp) AS [Time],
    AlertHistory.AlertObjects.RelatedNodeCaption AS [Related Node],
    'https://solarwinds/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) AS [_linkfor_Message],
    'https://solarwinds/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) AS [_linkfor_Alert Name]
FROM
    Orion.AlertHistory 
WHERE
    AlertHistory.AlertObjects.RelatedNodeID='${NodeID}'
    AND EventType = 0
ORDER BY
    TimeStamp desc

Maintenance status

SELECT
    N.Caption AS [Node],
    ToString(tolocal(N.UnManageFrom)) AS [Suppressed From],
    CASE
        WHEN ToString(N.UnManageUntil)='Jan  1 9999 12:00AM' THEN ' - Forever - '
        ELSE ToString(tolocal(N.UnManageUntil))
    END AS [Suppressed TO],
    CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
    N.DetailsUrl AS [_LinkFor_Node],
    CONCAT(DayDiff(N.UnManageFrom,GetDate()), ' days') as [Suppressed days],
    AE.AccountID AS [Supressed By],
    'Unmanaged' AS [Type]
FROM Orion.Nodes N
LEFT OUTER JOIN (
        SELECT
            AE.NetObjectID,
            Max(AE.AuditEventID) as [AuditEventID],
            Max(AE.TimeLoggedUtc) as [TimeLoggedUtc] 
        FROM Orion.AuditingEvents AE
        where ActionTypeID in
            ( 
            SELECT ActionTypeID 
            FROM Orion.AuditingActionTypes 
            where
                ActionType = 'Orion.AlertSuppressionAdded' 
                or ActionType = 'Orion.AlertSuppressionChanged' 
            ) 
        group by AE.NetObjectID
) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
WHERE
    N.UnManaged=1 and LastMuteEvents.NetObjectID = ${NodeId}

UNION
(
SELECT
    N.Caption AS [Node],
    ToString(tolocal(Supp.SuppressFrom)) AS [Suppressed From],
    ISNULL(ToString(tolocal(Supp.SuppressUntil)),' - Forever - ') AS [Suppressed TO],
 CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node],
   N.DetailsUrl AS [_LinkFor_Node],
    --CONCAT('/Orion/images/StatusIcons/',N.StatusIcon) as Icon,
    CONCAT(DayDiff(Supp.SuppressFrom,GetDate()), ' days') as [Suppressed days],
    AE.AccountID AS [Supressed By],
    'Muted' AS [Type]
FROM Orion.Nodes N
INNER JOIN Orion.AlertSuppression Supp ON Supp.EntityUri=N.Uri
LEFT OUTER JOIN (
        SELECT
            AE.NetObjectID,
            Max(AE.AuditEventID) as [AuditEventID],
            Max(AE.TimeLoggedUtc) as [TimeLoggedUtc] 
        FROM Orion.AuditingEvents AE
        where ActionTypeID in
            ( 
            SELECT ActionTypeID 
            FROM Orion.AuditingActionTypes 
            where
                ActionType = 'Orion.AlertSuppressionAdded' 
                or ActionType = 'Orion.AlertSuppressionChanged' 
            ) 
        group by AE.NetObjectID
) AS [LastMuteEvents] ON LastMuteEvents.NetObjectID=N.NodeID
LEFT OUTER JOIN Orion.AuditingEvents AE ON AE.AuditEventID=LastMuteEvents.AuditEventid
WHERE LastMuteEvents.NetObjectID = ${NodeId}
)

order BY N.Caption

Status du node avec Root Cause

SELECT   
    Caption, 
    statusdescription AS [Status],  
    n.NodeStatusRootCause AS [Root Cause],
    n.engine.servername AS [Poller]
FROM 
    Orion.Nodes n
WHERE 
    nodeid LIKE ${NodeId} --sera pris directement du node de la page

Page des volumes

Ces requêtes sont à ajouter dans les pages volume details.

Status de la collecte des métriques

Nous considérons ici comme obsolètes les données qui sont datées de plus de 90 minutes.

SELECT
    CASE
        WHEN n.Volumes.MinutesSinceLastSync > 90 THEN ' Data are obsolete !!'
        ELSE 'Data are up to date'
    END AS [Metrics status],
    CASE
        WHEN n.Volumes.MinutesSinceLastSync > 90 then '/Orion/images/StatusIcons/Small-Warning.gif'
        ELSE '/Orion/images/StatusIcons/Small-Up.gif'
    END AS [_iconfor_Metrics status] 
FROM 
    Orion.Nodes n
WHERE 
    n.volumes.volumeid = ${VolumeID} --pris de la page du volume