Sample event, log source, and storage usage AQL statements

Use the following examples to monitor events, log sources, and storage usage or you can edit the queries to suit your requirements.

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

(top)


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

(top)


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

(top)


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
Tip: Add more fields as required for your environment. Create an AQL parameter for the devicetype=11 variable. For more information, see Creating parameters for your dashboards.

(top)


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

(top)


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

(top)


Storage consumption per log source type

Provides a breakdown of storage that is used by each log source type during 1 day.

Note: This query doesn't include the storage that is used for QRadar® indexes. However, most indexes are equally distributed among all events regardless of the log source type or the size of the payload. 
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

(top)


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"
Tip: Run the search for a minimum of 1 day to return results. To improve results, index the Metric ID custom property.

(top)


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"

(top)


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"

(top)


Expensive CEPs, log sources, and rules

Provides a complete list of expensive complex event processing (CEP), log sources, and rules that are identified by QRadar.
Note: This statement uses the following custom properties from QRadar Development Intelligence app: expensivecp, expensivelogsource, and expensiverules.
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

(top)


Expensive searches

Provides a list of searches that were run in the past 24 hours. The list is sorted based on the execution time.

Note: This statement uses the following custom properties from QRadar Development Intelligence app: expensivecp, expensivelogsource, and expensiverules.
SELECT "searchid", "searchpriority", "searchlimit", "searchtime" 
FROM events 
WHERE qid=28250295 
ORDER BY searchtime 
DESC 
LAST 24 HOURS

(top)


Executed AQL searches by user

Provides a list of all searches that were run by each user.

Note: This statement uses the following custom properties from QRadar Development Intelligence app: expensivecp, expensivelogsource, and expensiverules.
SELECT username, "Ariel Source", "Ariel Cursor ID", "searchpriority", "AQL Statement" 
FROM events 
WHERE qid=28250254 
ORDER BY username 
LAST 2 HOURS

(top)


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.