Filtering events by time with DateFunc()
When you query the Event table, you must include a time boundary to limit your query results. Admin Portal provides a DateFunc() SQL function to filter events based on time.
The time span argument of DateFunc allows you to specify a combination of days, hours, minutes, and seconds. Its most generic form is:
‘[-]d.hh:mm:s’
The leading ‘-‘ is optional. Days supports any number of digits, seconds supports 1 or 2 digits, and hours and minutes require 2 digits. It also supports the following forms so you don’t have to use place holders for unneeded data:
Days: ‘[-]d’ (equivalent to [-]d.00:00:00)
Hours/minutes: ‘[-]hh:mm’ (equivalent to [-]0.hh:mm:00)
Hours/minutes/seconds” ‘[-]hh:mm:s’ (equivalent to [-]0.hh:mm:s)
Examples of placeholders
- Last week: ‘-7’
- Next 2 hours: ’02:00’
- Last 90 mins and 30 seconds: ‘-01:30:30’
- Last 7 days, 7 hours, 7 minutes, 7 seconds: ‘-7.07:07:7’
Description | SQL Query |
Events that occurred in the last 30 days |
select WhenOccurred, FailUserName, FromIPAddress from event where EventType = 'Cloud.Core.LoginFail' and whenoccurred >= DateFunc('now','-30') |
Events that occurred in the last 24 hours |
Select WhenOccurred,EventType from Event where WhenOccurred > datefunc('now', '-1') |
Events that occurred in the last 48 hours |
Select * from Event where WhenOccurred > DateFunc('now', '-2') |
Events that occured in the last 54 hours |
Select * from Event where WhenOccurred > DateFunc('now', '-2.06:00') |
Events that occurred on or before August 7, 2013 |
select WhenOccurred, UserName, FromIPAddress, AuthMethod, Factors from Event where EventType = 'Cloud.Core.Login' and WhenOccurred > datefunc('now', -7) |
Events that occurred yesterday |
select eventtype,WhenOccurred from event where whenoccurred>datefunc('now', '-3') and whenoccurred < datefunc('now', '-2') |
DateFunc Syntax
Use the following syntax:
DateFunc( <stringdate>, [<offset>])
where
<
stringdate>
can be one of the following three options:
'now'
- this means now (current time)'today'
- this means the start of today (current day)<date string>
- a string that represents a specific date and time, such as '09.30.2016:01:00'.
<
offset>
is a string representing an offset.
-n
means minus n days-5:00
means minus 5 hours
Note: Privileged Access Service operates using UTC time and displays in local time. So, “today” means the start of today according to UTC time, and ‘3:15’ means 3:15 today in UTC time. For example, if you specify ‘3:15’ while you’re in California during Daylight Savings Time, you’re actually specifying 8:15 am UTC time.