Office365 Licence report query

[floorsweeper]

Hello,

We have been using the report option in the 365 licencing to see which users are assigned a licence.

Normally with reports, they can be edited and the query viewed but the 365 reports don't allow this.

Does anyone one where to find the SQL query?

All I can see is this in the URL (For an E1). server.domain/Report/report.aspx?det=web50Office365LicenseUsers&title=Users%20With%20License:%20Enterprise+Plan+E1&@id=105


Thanks

[njordur]

Jayden wrote:
Hello,

We have been using the report option in the 365 licencing to see which users are assigned a licence.

Normally with reports, they can be edited and the query viewed but the 365 reports don't allow this.

Does anyone one where to find the SQL query?

All I can see is this in the URL (For an E1). server.domain/Report/report.aspx?det=web50Office365LicenseUsers&title=Users%20With%20License:%20Enterprise+Plan+E1&@id=105


Thanks

This is in the tsysDBobjects table. You can copy the query and use as your own. Note that this report has a parameter for the licenseid. I don't recommend editing directly in the Query column since lansweeper will most likely update this in due time or you could break it if you are not careful.

SELECT [DBobjName]
      ,[Query]
      ,[Comment]
      ,[DisplayName]
      ,[HDOverseerOnly]
  FROM [lansweeperdb].[dbo].[tsysDBobjects] 
  where DBobjName = 'web50Office365LicenseUsers'

Result is here:

  SELECT TOP 1000000 'user.png' AS Icon, tblO365User.DisplayName, tblO365User.UserPrincipalName, tblADusers.Username,
tblADusers.Userdomain, tblO365User.Department, tblO365User.Mail, tblO365Organization.DisplayName AS Organization, 
tblO365Organization.TenantId FROM tblO365User 
INNER JOIN tblO365Organization ON tblO365User.OrganizationId = tblO365Organization.OrganizationId 
INNER JOIN tblO365AssignedLicense ON tblO365AssignedLicense.UserId = tblO365User.UserId
LEFT OUTER JOIN tblADusers ON tblADusers.UPN = tblO365User.UserPrincipalName
WHERE tblO365AssignedLicense.LicenseId = @id
ORDER BY tblO365User.DisplayName ASC

[floorsweeper]

bump