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, |
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 |