Report query examples: Built-in report definitions

Admin Portal provides some built-in reports that you can use or copy and then modify as desired. You can view the SQL statements for any of the built-in reports in Admin Portal. For convenience, here are some examples of the report definitions for several of the built-in reports so you can see examples of the SQL syntax being used.

Report description Query syntax

Web apps used the most often during the last 30 days

 
select ApplicationName as Name,
count(*) as Count from Event
where WhenOccurred >= DateFunc('now', '-30')
and EventType='Cloud.Saas.Application.AppLaunch'
group by name order by count desc

Web apps added and used in the last 30 days

 
select distinct ApplicationName from Event 
where eventtype='Cloud.Saas.Application.AppLaunch' 
and ApplicationName in
(select applicationname from event where whenoccurred >datefunc('now','-30') and  eventtype='Cloud.Saas.Application.AppAdd'

Web apps that weren't used in the last 30 days

 
select Name from application where DisplayName not in 
(select ApplicationName from Event 
where WhenOccurred >= DateFunc('now', '-30')
and EventType='Cloud.Saas.Application.AppLaunch')
and AppType = 'Web'

A listing of the different Android versions in use

 
select OSVersion,Count(*) as Count
from device where InternalDeviceType = 'A'
group by osversion order by count desc

Number of devices, organized by mobile carrier

 
select Carrier, count(*) as Count from device
group by Carrier

Number of devices, organized by iOS, Mac, Android, and Windows

 
select case(InternalDeviceType)
when 'I' then 'iOS'
when 'M' then 'Mac'
when 'A' then 'Android'
when 'W' then 'Windows'
end as Platform, Count(*) as Count from device
group by InternalDeviceType order by Count
desc","DisplayName":"DeviceByPlatform

A listing of the different iOS versions in use

 
select OSVersion,Count(*) as Count
from device where InternalDeviceType = 'I'
group by osversion order by count desc

All mobile apps, organized by the number of installations

 
select Name, Count(*) as Count from InstalledApp
group by name 
order by count desc

Failed logins in the last 30 days

 
select WhenOccurred, FailUserName, FromIPAddress from event
where EventType = 'Cloud.Core.LoginFail' 
and whenoccurred >= DateFunc('now','-30')

Users who haven't logged in during the last 30 days

 
select UserName, DisplayName, LastLogin from User where ID not in
(select UserGUID from Event
where EventType = 'Cloud.Core.Login'
and WhenOccurred >= DateFunc('now', '-30'))

The users who have logged in the most often during the past 30 days

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