The following custom SQL can be used to alert when a matching syslog message is found within the last 5 minutes.
Note: -65 is used for the time calculation for the previous 5 minutes as the message time and SQL GETDATE() times are different. You may need to adjust this.
Replace the following:
- SolarWindsOrionLogRC with the name of your OrionLogs database
- LOGIN_FAILED with the string you want to search within the syslog messages
Add the following to a custom SQL Alert for a Node.
WHERE nodes.NodeID in (
SELECT LEV.NodeID
FROM SolarWindsOrionLogRC.dbo.OrionLog_LogEntryView LEV
INNER JOIN
(
SELECT NodeID,
MAX(SolarWindsOrionLogRC.dbo.OrionLog_LogEntryView.DateTime) as MessageTime, SolarWindsOrionLogRC.dbo.OrionLog_LogEntryMessageView.Message
FROM SolarWindsOrionLogRC.dbo.OrionLog_LogEntryView
INNER JOIN SolarWindsOrionLogRC.dbo.OrionLog_LogEntryMessageView ON SolarWindsOrionLogRC.dbo.OrionLog_LogEntryView.LogEntryID = SolarWindsOrionLogRC.dbo.OrionLog_LogEntryMessageView.LogEntryID
GROUP BY NodeID,SolarWindsOrionLogRC.dbo.OrionLog_LogEntryMessageView.Message
) LEM
on LEV.NodeID = LEM.NodeID
and LEV.DateTime = LEM.MessageTime
and LEM.Message like '%LOGIN_FAILED%'
and lEV.DateTime > DATEADD(mi,-65,GETDATE())
)
Massive thanks to mark.d for pointing out that this alert will never work if you are testing against a server without LogManager, no matter how many times you click validate and change your code!
