Log parser view

[petervandivier]

I've got a handy dandy snippet I use for parsing out the information from the JSON payload in the Logs. This should probably be tidied up & included in the repo:

select top 50
    est = LogDateTime at time zone 'UTC' at time zone 'Eastern Standard Time',
    LogDateTime,
    json_value(ExtraInfo,'$.Info[0].PolicyViolation') as PolicyViolation,
    json_value(ExtraInfo,'$.Info[0].session_id') as session_id,
    json_value(ExtraInfo,'$.Info[0].Login') as [Login],
    rtrim(json_value(ExtraInfo,'$.Info[0].program')) as program,
    json_value(ExtraInfo,'$.Info[0].timeinminutes') as timeinminutes, -- [0].timeinminutes for earlier versions
    json_value(ExtraInfo,'$.Info[0].DatabaseName') as DatabaseName,
    json_value(ExtraInfo,'$.Info[0].HostName') as HostName,
    substring(ExtraInfo,patindex('%create proc%',ExtraInfo),100) as ProcName,
    left(json_value(ExtraInfo,'$.Info[0].Sqltext'),1000) as Sqltext,
    --json_value(ExtraInfo,'$.Info[0].PolicyViolation') as PolicyViolation,
    Spid,
    recipients,
    [Subject],
    [Message],
    IsKill,
    ExtraInfo
from ED209.Logs 
where IsKill = 1
order by LogDateTime desc;