X·STAZE

Hello everyone, today we will discover how to make advanced AQL request with subqueries. They allow us to do action that are not possible with simple AQL request as well as combined requests in one request.

There are two types of subqueries:

• subqueries that define datas which will be used in the main request
• subqueries that allow you to filter on a property with the result of the subquery

2. HowTo

In a more technical point of view, you can see bellow the structure:

Type 1 :

select /*columns*/
from (/*subquery*/)
where
/*filters*/
/*aggregation*/
/*time filters*/

Type 2 :

select /*columns*/
from events /*or flows*/
where
myproperty in (/*subquery*/)
/*other filters*/
/*aggregation*/
/*time filters*/

In both case, the subquery structure is the same as a classic AQL query structure. As a reminder, here is the classic AQL structure without advanced statements:

select /*columns*/
from events /*or flows*/
where
/*filters*/
/*aggregation*/
/*time filters*/

Now we have the basics, lets dive into examples that show how usefull subqueries are.

Case n°1 :

I want to see what are the usernames that appear the most in all my log sources. Furthermore, I don’t want usernames that count for less than 100 events.

For this case, the AQL query would have been simple without the second part:

select logsourcename(logsourceid) as 'LogSource', username as 'Username', sum(eventcount) as 'TotalEvents', count(*) as 'CoalescedEvents'
from events
where
logsourceid > 69 /*exclusion of internal log sources*/
and Username <> null /*exclusion of logs without username*/
group by Username, LogSource
order by TotalEvents desc
last 1 days

Now, if we want to apply a filter on the total events, we can think of a query like this one bellow:

select logsourcename(logsourceid) as 'LogSource', username as 'Username', sum(eventcount) as 'TotalEvents', count(*) as 'CoalescedEvents'
from events
where
logsourceid > 69 /*exclusion of internal log sources*/
and Username <> null /*exclusion of logs without username*/
and TotalEvents > 100
group by Username, LogSource
order by TotalEvents desc
last 1 days

Unfortunalty it is not possible and you will obtain the following error:

Indeed, the property on which we try to filter is calculate by aggregation and it is not possible to filter on aggregated properties. That why, we need to use subqueries of type 1 to transform this aggregated property into a simple property that we can filter.

select *
from (select logsourcename(logsourceid) as 'LogSource', username as 'Username', sum(eventcount) as 'TotalEvents', count(*) as 'CoalescedEvents'
from events
where
logsourceid > 69 /*exclusion of internal log sources*/
and Username <> null /*exclusion of logs without username*/
group by Username, LogSource
order by TotalEvents desc
last 1 days)
where
TotalEvents > 100
last 1 days

In query above we can see the fact that my subquery (in blue) is the same as previously but without the filtering on total events. It is after that, in the principal query, we filter on total event and it is possible now because the property ‘TotalEvents’ and datas is considered as calculated.

In query above we can see the fact that my subquery (in blue) is the same as previously but without the filtering on total events. It is after that, in the principal query, we filter on total event and it is possible now because the property ‘TotalEvents’ and datas is considered as calculated.

Note: You need to set time filtering twice, one in the main query and also in the subquery. If not, by default in QRadar, time filtering for an AQL query is “last 5 minutes”, and result may not be what you expected.

Case n°2 :

One of my user tell me that his account has been pwned and I want to investigate every actions attacker may have done.

For this case, we want to filter on source IP but we don’t know which IP(s). In a other hand, we do know the username pwned and we can use this information as a pivot. First, we need to build a query to extract all source IP involved in every actions for a specific username.

select sourceip
from events
where
username = 'root'
group by sourceip
last 1 days

Now, we extend our query by surrounding it with the main query.

select logsourcename(logsourceid) as 'LogSource', qidname(qid) as 'EventName', count(*) as 'TotalEvents'
from events
where
sourceip in (select sourceip
from events
where
username = 'root'
group by sourceip
last 1 days)
group by EventName
order by TotalEvents desc
last 1 days

It will allow to get every events where the source IP is one of our filtering on the username pwned.

3. Conclusion

We almost are at the end of this article which, I hope, will extend your knowledge in QRadar. We did see a small part of what subqueries can do, we will, in further articles, other usages that will show you more use cases.

Meanwhile, I let you discover by yourself what can subqueries can achieve and resolve. Don’t hesite to share with us your question or findings about AQL and subqueries.

4. Bibliography

• IBM documentation: https://www.ibm.com/docs/en/qsip/7.4?topic=language-aql-subquery