Atria Dashboard - Add new Metrics/Graphs and other Examples

Overview

Starting in version 15.7, we introduced the Dashboard page in Atria where basic reports are displayed. The powershell script/app in this article will provide customization feature to add custom dashboard components/widgets.

Scripts

Set-AtriaDashboardComponent.ps1	Main script
Set-AtriaDashboardComponent.Designer.ps1	Contains UI components of main script
New-AtriaCustomerDashboard.ps1	For creating customer dashboards
Remove-AtriaCustomerDashboardComponent.ps1	For removing dashboard components/widgets

Powershell UI

Execute main script Set-AtriaDashboardComponent.ps1

Field Definitions

Field	Type	Description
Chart Title	Text	This will be displayed as the chart title in dashboard page.
Stored Procedure	Dropdown	Name of stored procedure that will be created to store the SQL query. Automatically generated based from chart title but can be renamed or you can re-use existing dashboard component's stored procedures.
Chart Type	Dropdown	These are the available chart types within Atria.
Dashboard	Dropdown	These are the available dashboards in Atria.
Order	Number	The order in which the component gets displayed in dashboard page.
Width	Number	Width of table/component in a 12-grid layout (Select from 1-12). Note: Some chart types has minimum width requirement so specifying a width less than it's minimum will never be enforced.
Height	Number	Height of table/component in a 12-grid layout (Select from 1-12). Note: Some chart types has minimum height requirement so specifying a height less than it's minimum will never be enforced.
SQL Query	Text	Custom SQL query script to generate data that will supply values to dashboard component.
Chart Metadata	Text	Generated when the "Generate" button gets clicked in the UI. The JSON metadata needs to be manually updated to configure how the data gets displayed. Some values will be automatically inserted to JSON text such as chart title, column names, etc.

Examples

Metric Widget

SQL:

select count(*) as 'TotalUsers' from Users

Pie Chart

SQL:

select CustomerType, count(RequestID) as 'RequestCount'
from (
select RequestID, iif(ParentID is null, 'ServiceProvider', iif(Reseller is not null, 'Reseller', 'Customer')) as 'CustomerType'
from (
select r.RequestID, u.[Name] as 'User', c.[Name] as 'Customer', c.ParentID
, (select top(1) 1 from Services where CustomerID = c.CustomerID and [Name] = 'Proxy' and ResellerService = 1) as 'Reseller'
from Requests r
inner join Users u on u.UserID = r.CreatedByUserID
inner join Customers c on c.CustomerID = u.CustomerID
where r.ParentID = 0
) as a
) as b
group by CustomerType

Line/Spline Chart

SQL:

select distinct DATEADD(MONTH, DATEDIFF(MONTH, 0, DateAdded), 0) As Month
, count(UserID) as UserCount
from users
where DateAdded > DATEADD(m, -6, GetDate())
group by DATEADD(MONTH, DATEDIFF(MONTH, 0, DateAdded), 0)
order by DATEADD(MONTH, DATEDIFF(MONTH, 0, DateAdded), 0) asc

Grid Chart

SQL:

select c.[Label] as 'Customer'
, asji.[Label] as 'Event'
, asji.[Message]
, case
when asji.MessageType = 1 then 'Warning'
when asji.MessageType = 2 then 'Error'
else 'Info' end as 'Type'
, asj.[Started] as 'DateTime'
from AzureSyncJobs asj
inner join AzureSyncJobItems asji on asji.SyncJobId = asj.SyncJobId
inner join Customers c on c.CustomerID = asj.CustomerId
where asji.[Message] is not null
and asji.MessageType in (1, 2)

Additional Useful Potential Scripts

Log of Customer Administrator Log ins in the last 7 days

For use in a grid, this will output any Administrator who has logged into the Atria platform - This would mean either Customers or Resellers.

select LH.LoginDateTime, u.name, u.Label, u.upn, c.Label, u.Administrator, LH.Reason, LH.SessionID
from LoginHistory LH
inner join users u on lh.UserID = u.UserID
inner join customers c on u.CustomerID = C.CustomerID
where LoginDateTime between (DATEADD(day,-5,GETDATE())) and GETDATE()
and C.CustomerId != 1
order by 1 desc

Customer Dashboard Examples

Recent User Related Actions in the Last 7 Days

SQL:

select
u.[Label] as 'User'
, c.[Label] as 'Customer'
, a.ActionTaken as 'Action Taken'
, a.UserId
, c.CustomerID as 'CustomerId'
from (
select (
case
when ot.[Type] = 'Users' then (select UserID from Users where ObjectID = r.ObjectID)
when ot.[Type] = 'ServiceUsers' then (select UserID from ServiceUsers where ObjectID = r.ObjectID)
end
) as 'UserId'
, r.[Description] as 'ActionTaken'
, r.Created
from Requests r
inner join Objects o on o.ObjectID = r.ObjectID
inner join ObjectTypes ot on ot.ObjectTypeID = o.ObjectTypeID
where r.ParentID = 0
and r.ObjectID != 0
and ot.[Type] in ('Users', 'ServiceUsers')
and datediff(day, cast(r.Created as Date), cast(getdate() as Date)) <= 7
) as a
inner join Users u on u.UserID = a.UserId
inner join Customers c on c.CustomerID = u.CustomerID
where c.CustomerID in (select CustomerID from tf_CustomerHierarchy(@CustomerId, 1))
order by a.Created desc

Metadata:

{
"Title":"Recent User Related Actions (Last 7 Days)",
"sort":{
"allowSorting":true,
"isDefault":true,
"defaultSortBy":"Customer",
"direction":"ASC"
},
"filter":{
"allowFiltering":true
},
"pagination":{
"allowPaging":true,
"pageSize":10
},
"columns":[
{
"name":"User",
"autoFit":true,
"allowResizing":true,
"width":null,
"isLinkable":true,
"link":"/User/Users.aspx?CustomerID={CustomerId}\u0026UserID={UserId}",
"linkText":"{User}"
},
{
"name":"Customer",
"autoFit":true,
"allowResizing":true,
"width":null,
"isLinkable":true,
"link":"/Customer/Customers.aspx?CustomerID={CustomerId}",
"linkText":"{Customer}"
},
{
"name":"Action Taken",
"autoFit":false,
"allowResizing":false,
"width":null,
"isLinkable":false,
"link":null,
"linkText":null
}
]
}

Chart: