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

Report Logic Help

I am trying to write a report to capture all triggered events for specific services over the last 30 days. I built this query but i get nothing. I know the PRTG has a triggered event as i stopped the service and started it (see screenshot below first query)

What am i doing wrong? My train of thought is Message contains any of the OR listed AND the Event Type Name must contain Application. So it should return all triggered events with those services but only with "Application" Event Type Name similar to screenshot bottom.

If i do this it works. if i add the other services into the below query it doesn't work. i am confused to why. I am not understanding the logic. Can someone help explain?

Find more posts tagged with

Accepted answers

All comments

malasherv

bman6074

I guess the below should work.

Let me know how that goes

Malcolm.

zackm

So, in SQL, that would look like this:

SELECTEventsFROM dbo.EventsWHEREMessage LIKE '%PK_%'ORMessage LIKE '%OCA%'ORMessage LIKE '%GWS Open%'ORMessage LIKE '%Nuance NMS%'ORMessage LIKE '%RightFax%'ORMessage LIKE '%Hyland%'ORMessage LIKE '%PRTG%'OR(EventTypeName LIKE '%Application%')

Note that the “And” condition there would only be applied within the parenthesis and therefore isn’t really doing anything at all…

What you need to do is flip those conditions around where the EventTypeName is outside of the and/or block and then put the rest inside so the SQL looks like this:

SELECTEventsFROM dbo.EventsWHEREEventTypeName LIKE '%Application%'AND(     Message LIKE '%PK_%'     OR     Message LIKE '%OCA%'     OR     Message LIKE '%GWS Open%'     OR     Message LIKE '%Nuance NMS%'     OR     Message LIKE '%RightFax%'     OR     Message LIKE '%Hyland%'     OR     Message LIKE '%PRTG%')

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Loop1 Twitter: @Loop1Systems
ZackM Twitter: @ZackM

bman6074

Nether of the suggestions worked

i end up with every triggered alert that has Application in the message.

zackm

Alright, so the following has been tested in 2 separate environments; this should work for you

To begin, I wanted to make sure that filtering events to ONLY Application Up/Application Down -types was working, so I created a datasource as so:

To get this working, I used the default "Event Type" field from the root of the "Events" tree in the Add Column popup:

From there, I added a Node, Event Time, Message, and Event Type column entry to my table so it would resemble your latest screenshot.

The next step was to add a second table to this report with a new datasource so I could ensure that my extra filters for the Message field (contains "SHAREPOINT2013" or contains "Yammer") worked as expected:

The "Event Type" and "Message" fields were both taken from the root of the "Events" tree in the Add Column popup (as before):

The end result is a report with 2 tables as such: (Top is all Up/Down events, Bottom is filtered to specific entries)

end results.png

Just to verify, I ran a query in SQL to make sure that the logic was holding true:

SELECT   n.Caption 'DEVICE'  ,e.EventTime  ,e.Message  ,t.Name  FROM Events eJOIN NodesData n ON n.NodeID = e.NetworkNodeJOIN EventTypes t ON t.EventType = e.EventTypeWHERE-- Only SAM Application Up and SAM Application Down event types(  e.EventType = 504  OR  e.EventType = 505)AND-- Only Messages containing SHAREPOINT2013 or Yammer(  e.Message LIKE '%SHAREPOINT2013%'  OR  e.Message LIKE '%Yammer%')-- Sort by the Timestamp, descendingORDER BY e.EventTime DESC

And the end results from there:

sql results.png

Hope the extra screenshots and whatnot helps!

bman6074

Zack,

How did you filter to only show the specified applications?

That part I am not understanding. i am still getting everything.

My setup

bman6074

Ok, I am not getting everything but I am getting other services with OCA like DNS.LOCAL I may need to add more detail to the "Message Like" statement to omit those other services i don't want.

Also the underscore is not accepted by the query. This is why i am getting other services with PK in the name. Even when i changed the OCA to OCA_ i still get the DNS.LOCAL services. Is there a way to make underscores recognized in the statement?