MaKaBe

Prérequis

• installation du SWQL Studio qui vient avec le module nécessaire pour PowerShell https://github.com/solarwinds/OrionSDK
• ouverture des ports vers un serveur SolarWinds port 17778 en SSL

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' }

Se connecter avec le user root

Démonter la partition /home

Le plus simple depuis l’aplication Disk

lvresize -L -20G --resizefs /dev/fedora_localhost-live/home 
vgs
lvresize -L +20G --resizefs /dev/fedora_localhost-live/root 

Restart

c’est OK !

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

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

Get-ChildItem -Path D:tempfolder_to_test_scripts -recurse -Include "*.bat" | Select-String REM | select Path, LineNumber