Jeudi, Novembre 18, 2021

Amélioration des pages avec des requêtes

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

Mercredi, Novembre 10, 2021

Connexion aux API en Powershell et première requête

Prérequis

Intérêts des API

  • Pas d’accès direct à la base de données, il suffit d’une connexion à un serveur SolarWinds (central ou poller aditionnel)
  • Indépendant des changements de schéma de la DB
  • Limitations de compte héritées directement de SolarWinds

Connexion basique

# import du module nécessaire
Import-Module SwisPowerShell

# définition des variables
$hostname = '127.0.0.1'
$user = 'admin'
$password = 'password'

# connexion au serveur
$swis = connect-swis -hostname $hostname -user $user -password $password

# requête SWQL
$query = 'select top 10 caption, nodeid from orion.nodes'

# récupération basique des données
get-swisdata -query $query -swisconnection $swis

Il est possible également depuis le serveur lui-même de se connecter avec un certificat du serveur

# import du module
Import-Module SwisPowerShell

# définition des variables
$hostname = '127.0.0.1'

$swis = connect-swis -hostname $hostname -certificate

$query = 'select top 10 nodeid, vendor, machinetype, caption, ip_address from orion.nodes'

get-swisdata -query $query -swisconnection $swis # | Export-Csv -Path "SW_NODE_EXPORT.csv" -NoTypeInformation

Quelques informations de plus

Import du module powershell nécessaire

Import-Module SwisPowerShell

Connexion aux APIs

Il existe plusieurs méthode d’authentification aux APIs :

  • credential : à récupérer depuis get-credential
  • username et password : des strings
  • certificate : utilisation du certificat local du serveur Orion
  • trusted : utilisation du compte Windows connecté
# credential
$creds = Get-Credential
$swis = connect-swis -hostname $hostname -credential $creds

# username password
$swis = connect-swis -hostname $hostname -username admin -password password

# certificat
$swis = connect-swis -hostname $hostname -certificate

# trusted
$swis = connect-swis -hostname $hostname -trusted

A noter l’argument -hostname est optionnel, par défaut la connexion se fait sur le localhost, en exécution locale sur un serveur SolarWinds on peut donc s’en passer.

Récupération des données

La commande get-swisdata peut prendre 3 arguments:

  • SwisConnection : obligatoire, c’est le résultat de la commande précédente
  • Query : obligatoire, c’est la requête SWQL, elle peut être directement incluse dans la commande get-swisdata ou passée par une variable
  • Parameters : facultatif, peut être utiliser pour fournir des paramètres à la requête

Requête basique

$query = 'select top 10 nodeid, vendor, machinetype, caption, ip_address from orion.nodes'
get-swisdata -query $query -swisconnection $swis

Requête avec paramètre

$query = 'select top 10 nodeid, vendor, machinetype, caption, ip_address from orion.nodes where vendor = @v'
get-swisdata -query $query -swisconnection $swis -parameters @{ v = 'Cisco' }

Vendredi, Novembre 5, 2021

Info sur les derniers failover

C’est le genre d’information à garder dans un dashboard ou bien un rapport régulier.

Les failover du jour

SELECT
    TOLOCAL(e.EventTime) AS [Failover time] 
    , SUBSTRING(
        e.Message, -- which colomn
        CHARINDEX('''',e.Message)+1, --where to start
        CHARINDEX('''',e.Message,(CHARINDEX('''',e.Message)+1) )- CHARINDEX('''',e.Message)-1 -- where to end
    ) AS [Pool Name]
    , SUBSTRING(
        e.Message, -- which colomn
        CHARINDEX('Pool member',e.Message)+length('Pool member ''') , --where to start
        (CHARINDEX('''',e.Message,CHARINDEX('Pool member',e.Message)+length('Pool member ''')) ) - (CHARINDEX('Pool member',e.Message)+length('Pool member ''')) -- where to end
    ) AS [From Poller]
    ,SUBSTRING(
        e.Message,
        CHARINDEX('Pool member',e.Message,CHARINDEX('changed',e.Message))+length('Pool member ''',CHARINDEX('''',e.Message,CHARINDEX('Pool member',e.Message)+length('Pool member '''))),
        (CHARINDEX('''',e.Message,CHARINDEX('Pool member',e.Message)+length('Pool member ''')) ) - (CHARINDEX('Pool member',e.Message)+length('Pool member ''')) 
    ) AS [To Poller]
    , SUBSTRING(
        e.Message,
        CHARINDEX('Reason of fail',e.Message)+length('Reason of fail: '),
        length(e.Message)-CHARINDEX('Reason of fail',e.Message)
    ) as [Reason]
FROM 
    orion.Events e
JOIN 
    orion.HA.Pools p on p.DisplayName = SUBSTRING(
        e.Message, -- which colomn
        CHARINDEX('''',e.Message)+1, --where to start
        CHARINDEX('''',e.Message,(CHARINDEX('''',e.Message)+1) )- CHARINDEX('''',e.Message)-1 -- where to end
    ) 
WHERE 
    e.EventType = 6500
    AND TOLOCAL(e.EventTime) > DATETRUNC('day',GETDATE())
ORDER BY e.EventTime DESC

Failover des 2 derniers jours

select top 15
    tolocal(e.EventTime) as [Failover time] --, e.Message

, SUBSTRING(
e.message, -- which colomn
charindex('''',e.message)+1, --where to start
charindex('''',e.message,(charindex('''',e.message)+1) )- charindex('''',e.message)-1 -- where to end
) as [Pool Name]
, SUBSTRING(
e.message, -- which colomn
charindex('Pool member',e.message)+length('Pool member ''') , --where to start
(charindex('''',e.message,charindex('Pool member',e.message)+length('Pool member ''')) ) - (charindex('Pool member',e.message)+length('Pool member ''')) -- where to end
) as [From Poller]
,substring(
e.Message,
charindex('Pool member',e.message,charindex('changed',e.message))+length('Pool member ''',charindex('''',e.message,charindex('Pool member',e.message)+length('Pool member '''))),
(charindex('''',e.message,charindex('Pool member',e.message)+length('Pool member ''')) ) - (charindex('Pool member',e.message)+length('Pool member ''')) 
) as [To Poller]
, substring(
e.message,
charindex('Reason of fail',e.message)+length('Reason of fail: '),
length(e.message)-charindex('Reason of fail',e.message)
) as [Reason]

from orion.Events e
join orion.HA.Pools p on p.DisplayName = SUBSTRING(
e.message, -- which colomn
charindex('''',e.message)+1, --where to start
charindex('''',e.message,(charindex('''',e.message)+1) )- charindex('''',e.message)-1 -- where to end
) 
where e.EventType = 6500
and tolocal(e.EventTime) < DateTrunc('day',GETDATE())
and tolocal(e.EventTime) > DateTrunc('day',addday(-2,GETDATE()))
order by e.EventTime desc

Les 10 derniers failover

select top 10
    tolocal(e.EventTime) as [Failover time]

, SUBSTRING(
e.message, -- which colomn
charindex('''',e.message)+1, --where to start
charindex('''',e.message,(charindex('''',e.message)+1) )- charindex('''',e.message)-1 -- where to end
) as [Pool Name]
, SUBSTRING(
e.message, -- which colomn
charindex('Pool member',e.message)+length('Pool member ''') , --where to start
(charindex('''',e.message,charindex('Pool member',e.message)+length('Pool member ''')) ) - (charindex('Pool member',e.message)+length('Pool member ''')) -- where to end
) as [From Poller]
,substring(
e.Message,
charindex('Pool member',e.message,charindex('changed',e.message))+length('Pool member ''',charindex('''',e.message,charindex('Pool member',e.message)+length('Pool member '''))),
(charindex('''',e.message,charindex('Pool member',e.message)+length('Pool member ''')) ) - (charindex('Pool member',e.message)+length('Pool member ''')) 
) as [To Poller]
, substring(
e.message,
charindex('Reason of fail',e.message)+length('Reason of fail: '),
length(e.message)-charindex('Reason of fail',e.message)
) as [Reason]

from orion.Events e
join orion.HA.Pools p on p.DisplayName = SUBSTRING(
e.message, -- which colomn
charindex('''',e.message)+1, --where to start
charindex('''',e.message,(charindex('''',e.message)+1) )- charindex('''',e.message)-1 -- where to end
) 
where e.EventType = 6500
--and tolocal(e.EventTime) > DateTrunc('day',GETDATE())
order by e.EventTime desc

Configuration des DB

Récupération de configuration des données en SWQL

SELECT 
    sql.Node.Caption, 
    sql.InstanceName, 
    sql.ProductVersion, 
    sql.ProductLevel, 
    sql.Edition, 
    sql.Name,type.value,
    (CASE 
        WHEN type.Value = 'Static' THEN port.Value 
        ELSE 1433 
    END) as PortNumber
FROM 
    Orion.APM.SqlServerApplication as sql
LEFT JOIN 
    Orion.APM.ApplicationSettings as port ON sql.ApplicationID = port.ApplicationID
LEFT JOIN 
    Orion.APM.ApplicationSettings as type ON sql.ApplicationID = type.ApplicationID
WHERE 
    port.Key = 'PortNumber'
    AND type.Key = 'PortType'
ORDER BY
    sql.Node.Caption, 
    sql.InstanceName

Vendredi, Octobre 29, 2021

Trouver toutes les IPs d’une machine

Afficher les IPs d’une machine

SELECT 
    nia.node.Vendor, nia.node.MachineType,
    nia.Node.Caption, 
    nia.IPAddress
FROM 
    Orion.NodeIPAddresses nia
WHERE 
    nia.IPAddress not in ('::1','127.0.0.1') and nia.Node.Caption = 'mytestmachine' -- exclusion des loopback et filtre sur la machine 'mytestmachine'
ORDER BY
    nia.Node.Vendor,
    nia.Node.MachineType,
    nia.Node.Caption

Afficher les IPs dupliquées

SELECT 
    nia.Node.Caption as [Node 1], 
    nia.IPAddress, 
    nia2.Node.Caption as [Node 2]
FROM 
    Orion.NodeIPAddresses nia
JOIN 
    Orion.NodeIPAddresses nia2 ON nia.IPAddress = nia2.IPAddress 
    AND nia.NodeID != nia2.NodeID

Afficher toutes les IPs de tous les nodes

SELECT 
    nia.node.Vendor, nia.node.MachineType,
    nia.Node.Caption, 
    nia.IPAddress
FROM 
    Orion.NodeIPAddresses nia
WHERE 
    nia.IPAddress not in ('::1','127.0.0.1') -- exclusion des loopback
ORDER BY
    nia.Node.Vendor,
    nia.Node.MachineType,
    nia.Node.Caption