Absolutely this is something we're planning to address, allowing the timerange to be based on log source time. Can't really provide a timeline at this point because there are some competing priorities but it's pretty high up the list, might be something we tackle this year.
If you don't already have Log Source Time indexed, it really will make a difference. Just bear in mind that enabling the index won't retroactively fill in indexes for all existing records already on disk, it'll just enable indexing of the field going forward, so it'll take some time before you really see the benefits.
Original Message:
Sent: Thu April 30, 2020 03:51 PM
From: Richard Giesige
Subject: AQL for dealing with searching for logs with a different time field.
Colin,
Thanks for replying to this. Do you ever think ibm and the QRadar group would think about allowing the time base to not be when it was accepted into QRadar but the actual log source time?
we are doing these lookups for 100's of offenses and doing this programmatically with maybe tens of these searches occurring at the same time. We saw lots of negative impact on our appliances when these were running causing lots of performance issues.
But I understand casting the wider net is probably the only option right now for our offenses and pulling data into our SOAR platform.
------------------------------
Richard Giesige
Security Engineer
Oshkosh Corporation
Oshkosh
Original Message:
Sent: Thu April 30, 2020 01:51 PM
From: COLIN HAY
Subject: AQL for dealing with searching for logs with a different time field.
Hi Richard,
The "deviceTime" field that Jeremy is using in his AQL to get the "actualTime" column is the AQL equivalent of Log Source Time (so the timestamp parsed from the raw event).
Unfortunately the "LAST X minutes/hours/days" and "START X STOP Y" constructs in AQL only refer to the time at which we store the event data. Events and flows in QRadar are stored in a proprietary time-series database organized based on when the events are received and stored, and those constructs basically tell the query to only search the files written during that time range. If you look on disk under /store/ariel/events/records/, you'll see there is a time-based hierarchy of <year>/<month>/<day>/<hour>/, and within that hour directory there is a file per minute. So those time constructs tell the system which files to read and all others are ignored.
Where you want to search based on the time events actually occurred, and have events potentially being written into time-based files much later than when the events actually occurred, instead of relying on the "LAST X" or "START X STOP Y" constructs you need to use a deviceTime-based filter in your AQL WHERE clause, and really open up the range of files examined by doing LAST 7 DAYS or something large like that. You can even do LAST 1 MONTH if you're worried that some events may be that delayed reaching QRadar.
A really important step here though is to ensure the Log Source Time property is indexed. You can do this via the Index Management UI from the Admin tab. If you don't have access to the Admin tab you'll need to ask one of your admins to do this. The wide-raneg searches Jeremy and I have suggested will still work accurately without doing this, but it will make the searches slow, because the search threads will have to read all those files (and there will be a lot, if you look at the last week or month) and examine the Log Source Time/devicetime for each to see if it matches your WHERE clause. But if Log Source Time is indexed, this is vastly faster - the search just has to check the index files for each hour and you should see performance similar to if you were just searching by storage time.
In summary, cast a "wider net" in terms of the time range of files on disk you want to search (to ensure if an event arrived late you still catch it), but use a devicetime filter (just like you would for username, source IP, etc) in your WHERE clause to only only at events that actually occurred (in real life) within the time range you care about. Just make sure Log Source Time is indexed so you don't negatively impact your search performance too much.
Cheers
Colin
------------------------------
COLIN HAY
IBM Security
Original Message:
Sent: Thu April 30, 2020 09:53 AM
From: Richard Giesige
Subject: AQL for dealing with searching for logs with a different time field.
Thanks for the feedback Jeremy,
What do you mean by actual time? When trying to search we did try using LAST but we won't ever know the time range, if a computer is off and the logs come in three days later that query would fail.
This is also one of the things we are trying to automate for our response, responding to alerts and pulling all the logs around that alert to give context. I'm guessing we would have to get the events in the log then see what the logs import time was and the actual log time from the event. We would then have to wrap that around the criteria of how long back and forward we are looking.
We were just hoping that there would be an option to search based on something other than log import time.
------------------------------
Richard Giesige
Security Engineer
Oshkosh Corporation
Oshkosh
Original Message:
Sent: Thu April 30, 2020 08:02 AM
From: Jeremy Nielson
Subject: AQL for dealing with searching for logs with a different time field.
Search for a broader time range, and then compare to the "devicetime" field.
select DATEFORMAT(devicetime, 'YYYY-MM-dd HH:mm:ss') as actualTime, * from events where
actualTime > '2020-04-30 05:00' and actualTime < '2020-04-30 06:00'
last 48 hours
------------------------------
Jeremy Nielson
Original Message:
Sent: Wed April 29, 2020 10:56 PM
From: Richard Giesige
Subject: AQL for dealing with searching for logs with a different time field.
So we are doing AQL searches but we have been running into an issue much more often where "Start Time" and "Log Source Time" are off by hours because of us importing workstation logs and having systems have outages/issues with wincollect. This means that when we try to run an AQL search for a specific log time frame it doesn't work.
Example: We had DHCP logs get delayed for 12 hours and then come into QRadar. The AQL we usually run is like "Select * from events where log source = DHCP and start {time from log source} STOP {time from log source for end of events}.
But because the log source time doesn't match when it got imported into QRadar (Start Time), we can't do a simple query to get the logs from a host for a specific time frame. (Example: Start Time from Log: 04/12/2020 09:50:00 PM but Log Source Time: 04/12/2020 10:23:53 AM)
What is the best way to handle this in QRadar? Is there some way to search for a time range based on a different time field (and not have to do it for a whole day)?
This is happening more often as we have started importing logs from workstations and then come on and off the network. We don't want to be sending logs outside of our network for data privacy (and everything is on premises) so we only send the logs when they are back on the network (VPN/physically on the network). This means that the logs queue up in the computer and then get sent all at a different time.
------------------------------
Richard Giesige
Security Engineer
Oshkosh Corporation
------------------------------