Custom User Attributes
In addition to basic user attributes like
department, Teamwork Analytics allows you to report on custom user attributes from your active directory (we currently support Directory Extensions).
Gathering the attributes
To configure custom attributes, you must add their names to the
configuration.CustomUserProperties table in the TWA SQL database.
The table has one column called
Name which supports alphanumeric characters and underscores (
_), as in the following examples:
The next time TWA queries user data it will search for these attributes and save any values it finds to the SQL database within the
† Properties that were migrated from on-premesis Active Directory will often have a legacy ID in their names. This must be included.
Troubleshooting missing attributes
Custom attributes can be checked in advance by using Microsoft Graph Explorer to run a query for
GET https://graph.microsoft.com/beta/users (pasting this URL into your browser will not work).
However, the most thorough way to check an attribute is to configure it as above and run TWA until it completes a scan. Then, run the following SQL query against the TWA database and examine the results.
WITH ValueCountByProperty AS ( SELECT q1.[Property], CASE WHEN up.[Value] IS NULL THEN '[Microsoft Graph API did not return a value]' WHEN LEN(RTRIM(LTRIM(up.[Value]))) = 0 THEN '[Microsoft Graph API returned a blank]' ELSE up.[Value] END AS [Value], COUNT(*) AS [UserCount] FROM ( SELECT u.[Id] AS [UserId], cup.[Name] AS [Property] FROM [configuration].[CustomUserProperties] cup, [dbo].[Users] u WHERE u.[Deleted] = 0) q1 LEFT OUTER JOIN [dbo].[UserProperties] up ON q1.[Property] = up.[Path] AND q1.[UserId] = up.[UserId] AND up.[Deleted] = 0 GROUP BY q1.[Property], up.[Value] ) SELECT q1.[Property], q1.[Value], q1.[UserCount], ROUND(q1.[UserCount] / CAST((SELECT COUNT(*) FROM dbo.[Users] WHERE [Deleted] = 0) AS FLOAT) * 100, 2) AS [UserPercent] FROM ( SELECT [Property], '[Microsoft Graph API returned a unqiue value for a User]' AS [Value], COUNT(*) AS [UserCount] FROM ValueCountByProperty WHERE [UserCount] = 1 GROUP BY [Property] UNION ALL SELECT [Property], [Value], [UserCount] FROM ValueCountByProperty WHERE [UserCount] <> 1 ) q1 --WHERE q1.[Property] = 'property' ORDER BY 1, 4 DESC
The results will show the spread of values for each custom attribute accross the whole userbase.
|preferredColour||[Graph API returned a blank]||36||1.77|
|preferredColour||[Graph API returned a unqiue value for a User]||16||0.78|
If an attribute has been misconfigured, or it doesn’t exist, the results will show “Graph API did not return a value”.
|shoeSize||[Graph API did not return a value]||2039||100|
Filtering by custom attributes in Power BI
To configure custom attributes to show up in your Power BI reports, you must add their names to the
configuration.CustomUserPropertiesForReports table in the TWA SQL database.
The table has numbered CustomAttribute columns for their corresponding filter in Power Bi. The values must match whichever custom attributes names you have previously configured in the
configuration.CustomUserProperties table to use. They can be in whatever order you wish for them to show up in, such as in the following example.
There can only be one row in this table and we currently only support up to 10 custom attributes to show in Power BI at a time.
These configured filters will then be displayed in the filter bar in all supported pages.