Hi,
did you try to use the Event and flow metric Dashboard from Pulse? There are 2 that migth help and you can adopt the AQLs
Top 10 log sources by event count
SELECT starttime/(1000*60) as minute,
(minute * (1000*60)) as stime,
DATEFORMAT(starttime,'YYYY MM dd HH:mm:ss') as showTime,
logsourcename(logSourceId) AS 'Log Source',
SUM("eventCount") AS 'Event Count (Sum)',
logsourceid as 'Log Source ID'
from events
where logsourceid in
(
select logsourceid from (
select
logsourceid,
SUM("eventCount") AS 'Event Count (Sum)'
from events
where logSourceId not in (63,64,65,69)
group by logSourceId
order by 'Event Count (Sum)'
limit 10
)
)
GROUP BY minute, logSourceId
order by minute ASC
last 1 DAYS
Top 10 log sources
SELECT logsourcename(logsourceid) AS 'MY Log Sources', LONG(if (MAX(endtime) > MIN(startTime) ) then (SUM(eventcount)/((MAX(endtime) - MIN(starttime))/1000.0)) else SUM(eventcount)) as event_rate FROM events where logSourceId not in (63,64,65,69) GROUP BY logsourceid ORDER BY event_rate DESC LIMIT 10 LAST 1 DAYS
We changed it to:
SELECT logsourcename(logsourceid) as 'Log Source',"SUM_eventCount"/3600 as 'EPS', Time*1000 as 'realtime'
FROM GLOBALVIEW('Top Log Sources','HOURLY')
where logsourceid in
(
select logsourceid from (
select
logsourceid,
SUM("SUM_eventCount") AS 'Event Count (Sum)'
from GLOBALVIEW('Top Log Sources','HOURLY')
where NOT (logSourceId BETWEEN 62 AND 69)
group by logsourceid
order by "Event Count (Sum)" DESC
limit 10
last 30 DAYS
)
)
order by "realtime"
LAST 30 DAYS
------------------------------
Martin Schmitt
------------------------------
Original Message:
Sent: Tue June 08, 2021 03:13 AM
From: Abhishek Kakkireni
Subject: how do we get Top 10 Log sources EPS Utilization for last 30 days
Hi Team,
Requirement was to get top 10 Logsource EPS utilization for the last 30 days.I have used below AQL Query but its not giving correct value and not matching with Monthly EPS for that particular domain.
SELECT LOGSOURCENAME(logsourceid) AS "Log Source", SUM(eventcount) AS "Number of Events in Interval", SUM(eventcount) / 2592000 AS "EPS in Interval" FROM events where domainid=domainid GROUP BY "Log Source" ORDER BY "EPS in Interval" DESC LAST 30 DAYS.
AQL Query for EPS utilzation for the domian
select DOMAINNAME(domainid) as LogSource, sum(eventcount) /( ( max(endTime) - min(startTime)) / 1000 ) as EPS from events where domainid=domainid group by domainid order by EPS desc last 24 hours
Can someone help me with how to calculate top 10 Logsource EPS utilization for the last 30 days
------------------------------
Abhishek Kakkireni
------------------------------