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.