My scenario is that I am sending Syslog from our Trend Deep Security Appliance. I am trying process those into a Modern Dashboard so that I can share with customers as they cant access the Appliance for security reasons. I am able to process the syslogs fine using substrings and charindex, but what I need to be able to is set the Target as the Node itself so that Account Limitations apply when viewing the page and would be nice to pull the node.detailsurl and status as an added bonus for visuals. I am having trouble when I try to use the Alias Target as it cant be used as the query cant resolve the property. So I guess I need to pull the nodes first, then search orion.olm.logentry.message using the substring, but can't figure out how to format it as I'm quite new to writing queries. Can anyone help me structure this correctly please? If I could include the Node ID I could in theory add to a widget with ${NodeID} on the node details page too?
SELECT
Nodes.DisplayName AS AlertingServer,
AddHour(+1,L.DateTime) AS AlertDate,
SUBSTRING(L.Message,CHARINDEX('cat=',L.Message)+4,
(CHARINDEX('name=',L.Message) - CHARINDEX('cat=',L.Message)-4)) as AlertCategory,
SUBSTRING(L.Message,CHARINDEX('name=',L.Message)+5,
(CHARINDEX('desc=',L.Message) - CHARINDEX('name=',L.Message)-5)) as AlertName,
SUBSTRING(L.Message,CHARINDEX('desc=',L.Message)+5,
(CHARINDEX('sev=',L.Message) - CHARINDEX('desc=',L.Message)-5)) as NodeMessage,
SUBSTRING(L.Message,CHARINDEX('sev=',L.Message)+4,
(CHARINDEX('src=',L.Message) - CHARINDEX('sev=',L.Message)-4)) as Severity,
SUBSTRING(L.Message,CHARINDEX('target=',L.Message)+7,(CHARINDEX('msg=',L.Message) - CHARINDEX('target=',L.Message)-7)) as Target,
SUBSTRING(L.Message,CHARINDEX('TrendMicroDsTenant=',L.Message)+19,
(CHARINDEX('TrendMicroDsTenantId=1',L.Message) - CHARINDEX('TrendMicroDsTenant=',L.Message)-19)) as Tenant,
CASE
WHEN
SUBSTRING(L.Message,CHARINDEX('name=',L.Message)+5,
(CHARINDEX('desc=',L.Message) - CHARINDEX('name=',L.Message)-5)) LIKE '%Alert Started%' THEN 2
WHEN
SUBSTRING(L.Message,CHARINDEX('name=',L.Message)+5,
(CHARINDEX('desc=',L.Message) - CHARINDEX('name=',L.Message)-5)) LIKE '%Alert Ended%' THEN 1
WHEN
SUBSTRING(L.Message,CHARINDEX('name=',L.Message)+5,
(CHARINDEX('desc=',L.Message) - CHARINDEX('name=',L.Message)-5)) LIKE '%Alert Dismissed%' THEN 19
WHEN
SUBSTRING(L.Message,CHARINDEX('name=',L.Message)+5,
(CHARINDEX('desc=',L.Message) - CHARINDEX('name=',L.Message)-5)) LIKE '%Success%' THEN 1
WHEN
SUBSTRING(L.Message,CHARINDEX('name=',L.Message)+5,
(CHARINDEX('desc=',L.Message) - CHARINDEX('name=',L.Message)-5)) LIKE '%Requested%' THEN 5
WHEN
SUBSTRING(L.Message,CHARINDEX('name=',L.Message)+5,
(CHARINDEX('desc=',L.Message) - CHARINDEX('name=',L.Message)-5)) LIKE '%Offline%' THEN 2
ELSE 1
END AS AlertStatusStatus
FROM Orion.Nodes Nodes
INNER JOIN Orion.OLM.LogEntry AS L
ON Nodes.NodeID = L.NodeID
WHERE L.Message LIKE '%cat=System%'
ORDER BY L.DateTime DESC
