Sample event, log source, and storage usage AQL statements
- Log source summary
- Unique events
- Unparsed events percentage
- Unparsed events percentage for a specific log source type
- Number of partial matches per rule
- Number of partial matches per event processor
- Number of partial matches per rule and per event processor
- Storage consumption per log source type
- Storage usage
- CPU usage
- Memory usage
- Expensive CEPs, log sources, and rules
- Expensive searches
- Executed AQL searches by user
Log source summary
Provides a list of unique log source types, including the number of log sources, EPS, and the percentage of unparsed events.
SELECT LOGSOURCETYPENAME(devicetype) AS "LogSourceType",UNIQUECOUNT(logsourceid) as "Number of Log Sources",
COUNT(*)/3600 as "EPS",(DOUBLE(COUNT(isunparsed)) / COUNT(*)) * 100 AS "PercentUnparsed"
FROM events
GROUP BY devicetype
ORDER BY "PercentUnparsed"
DESC
LAST 1 HOURS
Unique events
Provides a list of unique events and the log source type, QID, high-level category, low-level category, and the event count.
SELECT LOGSOURCETYPENAME(devicetype) AS "Log Source Type",
QIDNAME(qid) AS "Event Name",
qid as "QID",
CATEGORYNAME(highlevelcategory) AS "High-level Category",
CATEGORYNAME(category) AS "Low-level Category",
LONG(COUNT(*)) as "Number of Events"
FROM events
GROUP BY qid, devicetype
ORDER BY COUNT(*)
DESC
LAST 1 HOURS
Unparsed events percentage
Provides the percentage of events that are unparsed for each log source type. Any log sources that are greater than 20% unparsed must be addressed.
SELECT LOGSOURCETYPENAME(devicetype) AS "Log Source Type",
devicetype AS "Log Source ID",
LONG(UNIQUECOUNT(logsourceid)) as "Number of Log Sources",
LONG(SUM(eventcount)) as "Total Events",
LONG(COUNT(*)) as "Aggregated Events",
LONG(COUNT(isunparsed)) AS "Unparsed Events",
STR(LONG(SUM(eventcount)/24*3600)) as "EPS",
LONG("Unparsed Events"*100) / "Total Events" AS "PercentUnparsed"
FROM events
GROUP BY devicetype
ORDER BY "PercentUnparsed","Unparsed Events"
DESC
LAST 24 HOURS
Unparsed events percentage for a specific log source type
To further investigate the parsing issues with one specific log source type (for example, devicetype=11), you can run the following query that returns a breakdown of the statistics per log source.
SELECT LOGSOURCENAME(logsourceid) AS "Log Source Name",
LONG(SUM(eventcount)) as "Total Events",
LONG(COUNT(*)) as "Aggregated Events",
LONG(COUNT(isunparsed)) AS "Unparsed Events",
STR(LONG(SUM(eventcount)/24*3600)) as "EPS",
LONG("Unparsed Events"*100) / "Total Events" AS "PercentUnparsed"
FROM events
WHERE devicetype=11
GROUP BY logsourceid
ORDER BY "PercentUnparsed","Unparsed Events"
DESC
LAST 24 HOURS
Number of partial matches per rule
Provides a list of all rules and building blocks with the number of partial matches within the specified time period.
SELECT RULENAME(partialmatchlist) as "Rule Name",
LONG(COUNT(*)) as "Number of Partial Matches"
FROM events
WHERE partialmatchlist IS NOT NULL
GROUP BY "Rule Name"
ORDER BY "Number of Partial Matches"
DESC
LAST 1 HOURS
Number of partial matches per event processor
Provides a list of all rules and building blocks with the number of partial matches within the specified time period.
SELECT HOSTNAME(processorid) as "Event Processor Name",
LONG(COUNT(*)) as "Number of Partial Matches"
FROM events
WHERE partialmatchlist IS NOT NULL
GROUP BY "Event Processor Name"
ORDER BY "Number of Partial Matches"
DESC
LAST 1 HOURS
Number of partial matches per rule and per event processor
Provides a list of all rules and building blocks, including the number of partial matches per rule within the specified time period.
SELECT HOSTNAME(processorid) as "Event Processor Name",
RULENAME(partialmatchlist) as "Rule Name",
LONG(COUNT(*)) as "Number of Partial Matches"
FROM events
WHERE partialmatchlist IS NOT NULL
GROUP BY "Event Processor Name", "Rule Name"
ORDER BY "Number of Partial Matches"
DESC
LAST 1 HOURS
Storage consumption per log source type
Provides a breakdown of storage that is used by each log source type during 1 day.
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)"
FROM events
GROUP BY deviceType
ORDER BY "Total Storage (MB)"
DESC
LAST 24 HOURS
Storage usage
Provides a daily summary of the amount of storage that is used on all QRadar appliances.
SELECT DATEFORMAT(starttime, 'yyyy-MM-dd') as "Date", "Hostname" as "QRadar Appliance Name",
LONG(MAX("Value")/(1024*1024*1024)) as "Storage Used (GB)"
FROM events
WHERE (qid = 94000001) AND ((LONG(starttime/1000)%(24*3600)) < 20) AND ("Metric ID" = 'DiskSpaceUsed') AND (Element = '/store')
GROUP BY "Date", "Hostname"
ORDER BY "Date", "Hostname"
CPU usage
Provides a break-down of CPU time that is used by each component on every QRadar appliance
SELECT DATEFORMAT(starttime, 'yyyy-MM-dd') as "Date", "Hostname" as "QRadar Appliance Name",
"Component Type", LONG(SUM("Value")) as "CPU Time"
FROM events
WHERE (qid = 94000001) AND ("Metric ID" = 'ProcessCPUTime')
GROUP BY "Date", "Hostname", "Component Type"
ORDER BY "Date", "Hostname", "Component Type"
Memory usage
Provides a break-down of heap memory that is used by each component on every QRadar appliance.
SELECT DATEFORMAT(starttime, 'yyyy-MM-dd') as "Date", "Hostname" as "QRadar Appliance Name",
"Component Type", LONG(SUM("Value")/(1024*1024*1024)) as "Memory Usage Per Day (GB)"
FROM events
WHERE (qid = 94000001) AND ("Metric ID" = 'HeapMemoryUsed')
GROUP BY "Date", "Hostname", "Component Type"
ORDER BY "Date", "Hostname", "Component Type"
Expensive CEPs, log sources, and rules
SELECT DATEFORMAT(starttime,'yyyy-MM-dd HH:mm:ss') as "timestamp",sourceip, "expensivecp","expensivelogsource","expensiverules", UTF8(payload)
FROM events
WHERE devicetype=147 AND (expensivecp is not NULL OR expensivelogsource is not NULL OR expensiverules is not NULL)
ORDER BY timestamp
DESC
LAST 48 HOURS
Expensive searches
Provides a list of searches that were run in the past 24 hours. The list is sorted based on the execution time.
SELECT "searchid", "searchpriority", "searchlimit", "searchtime"
FROM events
WHERE qid=28250295
ORDER BY searchtime
DESC
LAST 24 HOURS
Executed AQL searches by user
Provides a list of all searches that were run by each user.
SELECT username, "Ariel Source", "Ariel Cursor ID", "searchpriority", "AQL Statement"
FROM events
WHERE qid=28250254
ORDER BY username
LAST 2 HOURS
Copying query samples
If you copy and paste a query sample that contains single or double quotation marks, you must retype the quotation marks to be sure that the query parses.