Email
 
Feedback
 
Print
 

Jul 18, 2018

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.

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

Idaptive Identity Services 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.