Query for Top 10 / 25 Audit events and Event Messages

Hi,

I am looking for two separate queries which gives me an output of Top 25 Audit events & Top 25 Event Messages of Last 7 days/ 14 days / 30 days respectively.

Can anyone please help?

SWQL is more preferable else, SQL is also fine.

Find more posts tagged with

NPM2020.2.6

sql

solarwinds

SWQL

orionsdk

content_exchange

report

Accepted answers

yaquaholic

Hmmmm...
You'll want to filter with ADDDATE then...

Select Top 25 E.EventType, ET.Name, E.Message, count(E.EventType) AS Total
From Orion.Events AS E
INNER JOIN Orion.EventTypes AS ET

on E.EventType = ET.EventType

WHERE E.EventTime > ADDDATE('DAY', -7, GETDATE())

Group by EventType, Name, message

Order by Total desc

All comments

yaquaholic

Dear WinterSoldier

A quick and dirty SWQL answer for you:

select top 25 EventType, Message, count(EventType) AS Total
from Orion.Events
group by EventType, message
order by Total desc

It should be fairly easy to re-apply this to Orion.AuditingEvents on ActionTypeID.

Hope it helps

yaquaholic

WinterSoldier

Hi @yaquaholic

Thank you for sharing. Can you please also help me with the time duration?

Example, Top 25 events from last 7 days or last 24 hours!

I tried some changes on my end with below query but not receiving the expected outcome.

select top 25 ET.Name, E.Message, E.EventTime, count(E.EventType) AS Total
from Orion.Events E
Join Orion.EventTypes ET ON ET.EventType = E.EventType
group by ET.Name, E.message, E.EventTime
Order By Total DESC

Objective is to get the list of Event messages and audit events from last 24 hours & last 7 days.

yaquaholic

Hmmmm...
You'll want to filter with ADDDATE then...

Select Top 25 E.EventType, ET.Name, E.Message, count(E.EventType) AS Total
From Orion.Events AS E
INNER JOIN Orion.EventTypes AS ET

on E.EventType = ET.EventType

WHERE E.EventTime > ADDDATE('DAY', -7, GETDATE())

Group by EventType, Name, message

Order by Total desc

WinterSoldier

Awesome. Thank you!