Common custom report templates

Modify “username@domain.name: with the user required. By default this captures the last 365 days, modify “365” for more or less.

select NormalizedUser as User, WhenOccurred as 'When', EventMessage , EventType, FromIPAddress as 'From IP Address'
from Event
where 
WhenOccurred >= DateFunc('now', '-365') 
and NormalizedUser ='username@domain.name'
and EventMessage NOT LIKE '%<%'


    				

By default this captures the last 365 days, modify “365” for more or less.

select NormalizedUser as User, WhenOccurred as 'When', EventMessage , EventType, FromIPAddress as 'From IP Address'
from Event
where 
WhenOccurred >= DateFunc('now', '-365') 
and EventMessage NOT LIKE '%<%'


    				

You can modify the following:
“90” Amount of past days
“username@domain.name’ The User Account
“18” to “24” and “00” to “06” Logins between 6pm and 6am

select NormalizedUser as User, WhenOccurred as 'When', EventMessage , EventType, formatdate(WhenOccurred,"HH") AS Hour, FromIPAddress as 'From IP Address'
from Event
where
WhenOccurred >= DateFunc('now', '-90')
and EventType = 'Cloud.Core.MfaSummary'
and EventMessage NOT LIKE '%<%'
and user ='username@domain.name'
and ((Hour >= "18" and Hour <="24") Or (Hour >="00" and Hour <="06"))
ORDER BY whenoccurred DESC


    				

By default this captures the last 365 days, modify “365” for more or less.

select ThreadType,NormalizedUser, EventType, EntityName as Account_Deleted, Action, WhenOccurred from Event where WhenOccurred>= Datefunc ('now','-365') and eventType = 'Cloud.Core.DSEntityChange'
and Action = 'Delete'
and ThreadType LIKE '%RestCall%'
order by WhenOccurred desc


    				

By default this captures the last 365 days, modify “365” for more or less.

select NormalizedUser, EventType, EntityName as Account_Created, WhenOccurred
from Event
where WhenOccurred >= Datefunc ('now','-365')
and EventType like '%CusCreateUser'
order by WhenOccurred desc


    				

By default this captures the last 14 days, modify “14” for more or less.

select ID AS _ID, UserName, DisplayName, LastLogin 
from User where _ID not in
(select UserGUID from Event where EventType = 'Cloud.Core.Login'and WhenOccurred >= DateFunc('now','-14')) 
order by lastlogin desc, username asc


    				

select NormalizedUser as User, 
Count(*) as Count from Event
where EventType = 'Cloud.Core.Login'and WhenOccurred >= DateFunc('now', '-14') 
group by User order by count desc


    				

select DisplayName, AppType, WebAppType, State, Count(*) as Count
from Application
group by name


    				

SELECT ID AS _ID, UserName, DisplayName, LastLogin
From User