Latest revision as of 12:41, 24 March 2025

Pulse Queries

Default query when I start to create a pulse dashboard

SELECT
"collectorId" as 'Event Collector ID',
"qidEventId" as 'Event ID',
QIDNAME(qid) as 'Event Name',
logsourcename(logSourceId) as 'Log Source',
"eventCount" as 'Event Count',
"startTime" as 'Start Time',
categoryname(category) as 'Low Level Category',
"sourceIP" as 'Source IP',
"sourcePort" as 'Source Port',
"destinationIP" as 'Destination IP',
"destinationPort" as 'Destination Port',
"userName" as 'Username',
"magnitude" as 'Magnitude'
FROM events

Disk Space

Check enough disk space on all appliances:

SELECT
"Hostname",
"Metric ID",
AVG("Value") as "value",
"Element"
FROM events
WHERE LOGSOURCENAME(logsourceid) ILIKE '%%health%%'
and "Metric ID" = 'DiskUsage'
GROUP BY Hostname,"Metric ID", "Element"
ORDER BY "Hostname"
LAST 2 minutes

Payload sizes

The query below calculates the total uncompressed payload size stored on disk for each log source type in the last hour.

SELECT 
LOGSOURCETYPENAME(deviceType) AS LogSource, 
MIN(STRLEN(UTF8(payload))) AS Minimum, 
MAX(STRLEN(UTF8(payload))) AS Maximum, 
AVG(STRLEN(UTF8(payload))) AS AverageSize, 
STDEV(STRLEN(UTF8(payload))) AS STD,
COUNT(logsourceid) AS EventCount,
LONG(EventCount * AverageSize) / (1024 * 1024)  as TotalSizeUncompressedMB
FROM events 
GROUP BY deviceType 
ORDER BY TotalSizeUncompressedMB DESC
LAST 60 minutes

This query analyzes log event data over the last 24 hours and provides insights into the uncompressed payload sizes for each log source type.

SELECT 
LOGSOURCETYPENAME(deviceType) AS LogSource, 
LONG(MIN(STRLEN(UTF8(payload)))) AS "Minimum Payload Size (Bytes)", 
LONG(MAX(STRLEN(UTF8(payload)))) AS "Maximum Payload Size (Bytes)", 
LONG(AVG(STRLEN(UTF8(payload)))) AS "Average Payload Size (Bytes)", 
LONG(STDEV(STRLEN(UTF8(payload)))) AS "Standard Deviation (Bytes)",
LONG(COUNT(logsourceid)) AS EventCount,
LONG(EventCount * "Average Payload Size (Bytes)") / (1024 * 1024)  as "Total Storage (MB)",
EventCount / (24*60*60) as "EPS"
FROM events 
GROUP BY deviceType 
ORDER BY "Total Storage (MB)" DESC
LAST 24 HOURS

Ver também