Not long ago, we had an interface of interest on a specific node. Sometime in the past 3 weeks (since the device had been up for 3 weeks), a MAC address showed up on a sticky port-security interface. It was longer ago that it no longer showed up in the device logs. We use Orion for our syslog manager. Unfortunately, it is very hard to find specific messages using Orion Syslog interface. So, I wrote a SQL script which runs against a Node Caption and a text string, and without a certain last amount of time. I have since re-written the code to be for the last 10 minutes, because I am in the process of looking to see if I can set this up as an Alert. Be aware, I run this script using Microsoft SQL Server Management Studio (which you can download for free). You can run this as a report in Orion if you wish. When running the code, be aware, it could take a while depending upon how many syslog messages you have, and how busy your server is. We have about 650k. When our server is busy, it runs in 2.5 minutes; when the server is free, it runs in 10 seconds.
Here is the original code, It has no time limitations, searched the whole syslog database. I will explain after:
SELECT *
FROM ( SELECT
[s].[MsgID]
, [s].[DateTime]
, [x].[NodeID]
, [x].[IP]
, [x].[HostName]
, [s].[Message]
, [s].[MessageType]
FROM ( SELECT
[NodeID]=[n].[NodeID]
, [IP]=[n].[IP_Address]
, [HostName]=[n].[Caption]
FROM [NodesData][n] WITH(NOLOCK)
WHERE [n].[Caption]='HQ-CORE-SW-01'
) [x]
JOIN [SysLog][s] WITH(NOLOCK) ON [x].[NodeID]=[s].[NodeID]
) [y]
WHERE PATINDEX ('%1/0/49%',[y].[Message]) > 0
Note that I am using information from NodesData Table instead of Nodes. Nodes is a view, and all info I needed was in the source table, so I obtain data from there. Lines 15 and 19 are where you put the Node Name and the text you want to search for. I suppose if I wanted to make this a bit easier to use, I would use a couple of declare statements at the top, and then put the variables in line 15 and 19. But that's just style, not function.
Above, we look for the NodeID from NodesData using the Caption (HQ-CORE-SW-01 in this example). It is easier to search for NodeID than hostname due to NodeID being numeric, and in 650k entries, a lit bit of savings adds up. Then we create a table with the information we want to see in the final report, for that NodeID, with fields from NodesData and from Syslog. The fields MdgID, DateTime, Message and Message Type are from Syslog Table, and the fields NodeID, Hostname and IP are from NodesData Table. We then search the resulting table for the string we are looking for (1/0/49 in this example). Note, leave the apostrophies and percent signs in.
The above SQL query will show all syslog messages for that interface on that node. This is good if you want to read the messages, but not so good for alerts. If there is a syslog message every minute, then you won't want alerts going off every minute. Here is the revision I am working on regarding creating alerts off syslog data.
DECLARE @DT DATETIME = DATEADD(mi,-10,GETDATE())
SELECT *
FROM (
SELECT
[n].[NodeID]
, [n].[IP_Address]
, [n].[Caption]
, [x].[Message]
FROM (
SELECT DISTINCT
[NodeID]=[s].[NodeID]
, [Message]=RIGHT([s].[Message], LEN([s].[Message])-CHARINDEX(N':', [s].[Message]))
FROM [SysLog][s] WITH(NOLOCK)
WHERE [s].[DateTime]>@DT
) [x]
JOIN [NodesData][n] WITH(NOLOCK) ON [n].[NodeID]=[x].[NodeID]
-- WHERE [n].[Caption]='IPHD2SWI01'
) [y]
WHERE PATINDEX ('%service recommended%',[y].[Message]) > 0
This query is very similar in nature as the above, but I removed a level of SELECTs -- it didn't seem necessary. I also added a DECLARE for the time. This one is set to -10 minutes. That's because I am thinking that I might want to check the last 10 minutes of syslogs to see if such-and-such string exists, and check this every 10 mintues. If you wanted to search for the last 5 days, the line would look like this. Or you could set it for any amount of time you want.
DECLARE @DT DATETIME = DATEADD(day,-5,GETDATE())
When creating alerts, we don't want to see every instance of a message, we just want to if there is such a message, and then be able to obtain information from the tables. When you run the top query, and search for the string (service recommended in this example), you will see many such messages, and they have leading 6 or 7 digit numbers followed by a colon (at least they do in our tables). Since the leading numbers change per entry, I search for the colon, find it's place in the message, and then truncate to the left, plus the colon. I then look for unique (distinct) occurrences of the results. Without distinct, I could receive many messages, and we just want one. Here is the resulting table (edited IPs and names).
| Header 1 | Header 2 | Header 3 | Header 4 | Header 5 |
|---|
| NodeID | IP_Address | Caption | Message |
| 1 | 3617 | 10.6.4.1 | BR-EDGE-RT-01 | CEST: Fan 2 service recommended |
| 2 | 1569 | 10.1.255.30 | HQ-ACC-SW-01 | Fan 1 service recommended |
| 3 | 3636 | 10.6.4.20 | BR-ACC-SW-01 | Fan 1 service recommended |
So, Nice result. Notice that I removed a couple of fields from the top SQL: DateTime, Message Type, MsgID. Had we left DateTime or MsgID in, every message would be unique. Also, in this example, I commented out the nodeID I was looking for. you would have it in there if you wanted, but then it would return just one result. If you want to alert on any device that is recommending service, then leave Caption undefined.
Of course, now you need to know you syslog messages, because you will need to change the last line depending upon what syslog string you want to alert on. Also, I haven't yet tried putting this in an alert yet. The Search for the colon and truncation to the left is indiscriminate right now. It will look for the first colon and then truncate everything to the left. The next step will be to write a regex string that will make it so that it will only truncate is the colon follows only a numeric string which is at the beginning of the string. That shouldn't be too hard. Also, I haven't yet looked to see --LOL!-- if we can run our own SQL string as part of an Alert. I thought we could, but I'm not entirely sure. So, hehehe... this may or maynot be for naught. Still, it is a useful tool.
Cheers! Eric
