MaKaBe

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