Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Some Useful Event Log Searches

Hi all. I had thought about posting this earlier, but then the new Log Manager module came out, and I thought that it would cover this. But when I review the screen shots in the Log Manager thread, I see that it is set-up to search Syslog and Traps. I don't see an Event Log search option.

To be sure, there is some ability to search event logs built into Orion already:

However, there ae some features missing from their search features. basically, if you want to seach by key word, NodeID, or Caption, or to obtain counts then the built-in search is severely lacking. So, several weeks ago, in order to search for specific events in Event Log, I created some tools, which I will post here for your consideration and use. I run these in MS SQL Server Management Studio. If you don't have access to that, you could use these as a custom SQL report, to obtain the information you need. I'll post my tools below as separate messages. (I think I may have posted one of these before in a post about searching syslog.)

Find more posts tagged with

Accepted answers

All comments

ebradford

This one will tell you the count of all events per day, every day of the specified month. Change the @month string to change which month you want to count.

------------------------------------------------------------- Count of Events in Event Log-- See a count of events per day for a specific monthDECLARE @Month varchar = '%201805%'SELECT      [Cnt]=COUNT([e].[EventID]) , [Per Min Avg]=COUNT([e].[EventID])/(60*24) , [Date]=CONVERT(varchar, [e].[EventTime], 112)FROM [dbo].[Events] [e] with (nolock)WHERE CONVERT(varchar, [e].[EventTime], 112) LIKE @MonthGroup By CONVERT(varchar, [e].[EventTime], 112)Order By [Date] Desc

You can get a count for specific NodeID or specific text in the message by adding one of these lines between lines 10 and 11.

AND NetworkNode = ####

AND Message LIKE '%SearchString%'

Put the NodeID in where the #### are. Replace SearchString with the characters you want to search for.

Now you know how many of those messages occurred each day for the month you indicated.

ebradford

This one is flexible, and takes a little explaining. First here's the code:

------------------------------------------------------ Find messages in Event Log based upon Hostname-- and portion of message text.DECLARE @DT DATETIME = DATEADD(day,-3,GETDATE())SELECT * FROM ( SELECT * FROM (         SELECT        [e].[EventID]     , [e].[EventTime]     , [e].[NetworkNode]     , [e].[Message]     , [e].[EventType]     , [n].[IP_Address]     , [n].[Caption]      , [n].[NodeID]  FROM [Events] [e] WITH (NOLOCK) JOIN [NodesData] [n] WITH (NOLOCK) ON [e].[NetworkNode] = [n].[NodeID]  WHERE [e].[EventTime]>@DT  ) [Y]  -- WHERE [Y].[Caption]='nodename'  -- WHERE [Y].[NodeID]=####    WHERE ([Y].[IP_Address]='10.1.###.###')  )[X] WHERE [X].[Message] LIKE '%Application "HTTP - %'ORDER BY [X].[EventTime] DESC

Line 4 make this search look for messages from the last 3 days. You can change days to hours or minutes, etc.; and the -3 to another negative number to indicate how many of the units (days, hours, etc) you want to go back. Make sure it is a negative number.

The WHERE clauses (lines 20-24) are where you set what to search for. Leave line 18 alone, if refers to the date.

Lines 20-22 restrict from which devices the events will be searched. Lines 24 searches among those messages for the specific string of text in the message. If you want to search by line 20 and not by line 22, then remove the two dashes in front of line 20, enter the EXACT node caption in place of nodename, and put two dashes in front of line 22. The dashes tell SQL to ignore that line. Don't change line 23.

[You could search by Node Caption and IP if you wanted (but likely doing so it not needed) by removing the dashes from in front of lines 20 and 22, and putting an AND in front of line 22, and entering the correct nodename and IP address.]

You can also search just for events just by one of the node identifiers (IP Address, Node ID, or Caption) by putting 2 dashes in front of line 24. Likewise, you could find all messages with such and such text regardless of node by putting 2 dashes in front of all three lines 20-22.

I also use the words WITH (NOLOCK) on my searches because I don't want SQL Mgmt Studio to lock the records while searching. When using these searches from within Orion, you probably don't need WITH (NOLOCK).

So, using the above SQL code, you can easily have more targeted search of Event Log, and find just the Event messages you want. I started another SQL query that will use a date range instead of Last X Days (or hours, etc). I will post that too, separately.

ebradford

This SQL query is similar to the previous one in many respects. It first searches for Events by date range, then searches those events for IP address, Caption or NodeID, then searches those results for the message type and/or Message Text, or if the event was acknowledged.

DECLARE @DTBegin DATETIME = '06/02/2018 20:00'  -- Use Format: 'mm/dd/yyyy' or 'mm/dd/yyyy hh:mi' DECLARE @DTEnd DATETIME = '06/04/2018 21:00'    -- Use Format (cont): may use AM or PM or 24 hr timeDECLARE @SearchCaption NVARCHAR(255) = ''       -- set to exact caption or ''DECLARE @SearchIP NVARCHAR(50)= ''              -- Set'##.##.##.##' or ''DECLARE @SearchNodeID INT= 1212                 -- Set to node number (without  quotes) or NULLDECLARE @SearchMsg NVARCHAR = ''                -- Set the the string you want to search for, or ''DECLARE @SearchAck BIT = ''                     -- Set 0= Not Acknowledged, 1= Acknowledged, or '' ; do not use quotes around the 0 or 1.DECLARE @SearchEvType SMALLINT = ''             -- Set number here if you know what EventType number to use (no quotes), otherwise ''SELECT * FROM ( SELECT *    FROM (   SELECT      [e].[EventID]   , [e].[EventTime]   , [e].[NetworkNode]   , [e].[Message]   , [e].[EventType]   , [e].[Acknowledged]   , [n].[IP_Address]   , [n].[Caption]   , [n].[NodeID]   FROM [Events] [e] WITH(NOLOCK) JOIN [NodesData] [n] WITH(NOLOCK) ON [e].[NetworkNode] = [n].[NodeID]   WHERE ([e].[EventTime]> @DTBegin) AND ([e].[EventTime]< @DTEnd)   ) [Y] -- WHERE ([Y].[IP_Address]= @SearchIP ) -- WHERE ([Y].[Caption]= @SearchCaption) WHERE ([Y].[NodeID]= @SearchNodeID) )[X]  WHERE [X].[Message] LIKE @SearchMsg OR [X].[EventType]= @SearchEvType OR [X].[Acknowledged]= @SearchAckORDER BY [X].[EventTime]DESC

I have also moved the search strings above, and assigned the value to variables, which are used later. The only lines you need to mess with changing are the vales in lines 1-8, and the dashes at lines 25-27. You may leave the variables blank '' or set to NULL, if you don't want to search by those fields. No need to comment out with dashes there. On my database when I searched by 2 lines of 25-27 by using an AND in place of the second WHERE (25-27 only), my search slowed down. It was even much worse with using OR -- so I recommend using just one of lines 25-27 active, and putting dashes in front of the other of these lines. Lines 25-27 are the only lines you should need to put any double-dashes in front of.

Note the time and date format, use appropriately. In the above example, it will looks for 2 days, 1 hour worth of events, as that is the message range I set. You set your date range accordingly.

ebradford

Those are my Event Log Search tools I made. I hope they serve you well if you need to search Event Log.

Cheers! Eric

jeilers

Where I am the events are pretty underutilized. I typically use them to determine reasons why a device is considered down. Typically seeing request time out. Do you have any type of events you have found useful to search for using these queries?

ebradford

If I were interested in a single device going up down, I would examine several searches:

1 - All events for the device (what else is going on),

2 - All down events for the device (look at timing, any pattern),

3 - Look at nearby and related device events (like UPS or core switch/router).

These might expose something that you might want to look at in Syslog or by using PerfStack, or other Orion pages. Event Log can be a helpful tool, just one of many of course.

On your alert actions, do you also write the alert to Event Log?

Also, we have some more "informational" alerts configured which write to Event Log, but not to send out an e-mails.

jeilers

I don't typically write the alert to the Event Log as a trigger condition. Although looking at this it might be a good idea to do so.

ebradford

We all have customers who come to us asking, why am I getting these alerts? I often want to retort, "because there is something wrong with your device." But most likely the customer is actually saying, "I get these alerts for devices which I don't care about. Can you make them stop?"

So, you ask them to forward the e-mail to you, which they do, and sure enough, it was not an alert that you created. If it had been, there would be some sort of marker in the text which you could use to track down the alert. But instead, the alert is too simple, and doesn't give you enough data to find the specific configured alert or alert action among the 500 or so active alerts which are configured on you Orion deployment..

You want the customer off your back as much as the customer wants to stop getting the alerts. Here's how to find the alert name from the message text, so you can review the trigger logic, or the subset of nodes, or the alert action, whatever.

Take the exact output, or partial text, of the alert message (likely, the the alert message will have the node name and/or component listed in it), put the text in the [Message] LIKE line between the '% %' symbols (no extra spaces). Then run the query in Custom SQL Report or MS SQL Server Management Studio. You can use the Alert Name or Action Name to find the Alert or Action in Alert Manager. Make your changes. Happy customer; happy manager; happy you.

SELECT top 100
   [Alert Time] = [TimeStamp]
   , [Alert Name] = [Name]
   , [Alert Message] = [Message]
   , [Action Name] = [Title]
   , [Trigger Node] = [RelatedNodeCaption]
   , [Trigger Component] = [EntityCaption]
FROM [AlertHistoryView] WITH (NOLOCK)
WHERE [Message] LIKE '%Alert: srv0prn01 is Down%'
ORDER BY [TimeStamp] DESC

This will create a little report telling you when the alert was triggered, what the Alert Name is, the Alert message, the Action name, and which node and which component triggered the alert.