1. Home
  2. Reporting
  3. Common custom report templates

Common custom report templates

Below is a list of custom report code. Once chosen follow the guide on creating a custom report and copy the code into the script editor.

In Script Editor, enter the below script in the box saying “Enter code here…”

User History Logs - Per User

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 user ='username@domain.name'
and EventMessage NOT LIKE '%<%'

User History Logs - All Users

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 '%<%'

User Login - By Time

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

Deleted User

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

Created User

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

Inactive Users

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

Top User

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

List Applications

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

List All Users

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

Updated on April 5, 2019

Related Articles