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