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 NormalizedUser ="username@domain.name"
and EventMessage not like "%<%"
order by WhenOccurred desc

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 "%<%"
order by WhenOccurred desc

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 NormalizedUser="username@domain.name"
and ((formatdate(WhenOccurred,"HH") >= "18" and formatdate(WhenOccurred,"HH")<="24") or (formatdate(WhenOccurred,"HH") >="00" and formatdate(WhenOccurred,"HH") <="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, 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, 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 WhenOccurred >= DateFunc("now","-14")
and EventType = "Cloud.Core.Login"
group by NormalizedUser
order by 2 desc

List Applications

select Name, AppType, WebAppType, State
from Application

List All Users

SELECT ID, UserName, DisplayName, LastLogin
From User

Updated on June 14, 2021

Related Articles