Atria Dashboard - Add new Metrics/Graphs and other Examples

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:
  1. select count(*) as 'TotalUsers' from Users


Pie Chart
             
SQL:
  1. select CustomerType, count(RequestID) as 'RequestCount' 
  2. from (
  3. select RequestID, iif(ParentID is null, 'ServiceProvider', iif(Reseller is not null, 'Reseller', 'Customer')) as 'CustomerType'
  4. from (
  5. select r.RequestID, u.[Name] as 'User', c.[Name] as 'Customer', c.ParentID
  6. , (select top(1) 1 from Services where CustomerID = c.CustomerID and [Name] = 'Proxy' and ResellerService = 1) as 'Reseller'
  7. from Requests r
  8. inner join Users u on u.UserID = r.CreatedByUserID
  9. inner join Customers c on c.CustomerID = u.CustomerID
  10. where r.ParentID = 0
  11. ) as a
  12. ) as b
  13. group by CustomerType


Line/Spline Chart
         
SQL:
  1. select distinct DATEADD(MONTH, DATEDIFF(MONTH, 0, DateAdded), 0) As Month 
  2. , count(UserID) as UserCount 
  3. from users 
  4. where DateAdded > DATEADD(m, -6, GetDate()) 
  5. group by DATEADD(MONTH, DATEDIFF(MONTH, 0, DateAdded), 0) 
  6. order by DATEADD(MONTH, DATEDIFF(MONTH, 0, DateAdded), 0) asc


Grid Chart
              
SQL:
  1. select c.[Label] as 'Customer'
  2. , asji.[Label] as 'Event'
  3. , asji.[Message]
  4. , case 
  5. when asji.MessageType = 1 then 'Warning' 
  6. when asji.MessageType = 2 then 'Error' 
  7. else 'Info' end as 'Type'
  8. , asj.[Started] as 'DateTime'
  9. from AzureSyncJobs asj
  10. inner join AzureSyncJobItems asji on asji.SyncJobId = asj.SyncJobId
  11. inner join Customers c on c.CustomerID = asj.CustomerId
  12. where asji.[Message] is not null
  13. 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.
  1. select LH.LoginDateTime, u.name, u.Label, u.upn, c.Label, u.Administrator, LH.Reason, LH.SessionID
  2. from LoginHistory LH
  3. inner join users u on lh.UserID = u.UserID
  4. inner join customers c on u.CustomerID = C.CustomerID
  5. where LoginDateTime between (DATEADD(day,-5,GETDATE())) and GETDATE()

  7. and C.CustomerId != 1
  8. order by 1 desc

Customer Dashboard Examples
Recent User Related Actions in the Last 7 Days
SQL:
  1. select
  2.         u.[Label]   as 'User'
  3.      , c.[Label]   as 'Customer'
  4.      , a.ActionTaken as 'Action Taken'
  5.      , a.UserId
  6.      , c.CustomerID  as 'CustomerId'
  7. from (
  8.     select (
  9. case
  10.             when ot.[Type] = 'Users' then (select UserID from Users        where ObjectID = r.ObjectID)
  11.             when ot.[Type] = 'ServiceUsers' then (select UserID from ServiceUsers where ObjectID = r.ObjectID)
  12.         end
  13. )                 as 'UserId'
  14. , r.[Description] as 'ActionTaken'
  15. , r.Created
  16. from Requests r
  17.             inner join Objects      o on  o.ObjectID     = r.ObjectID
  18.             inner join ObjectTypes ot on ot.ObjectTypeID = o.ObjectTypeID
  19.     where    r.ParentID  = 0
  20. and  r.ObjectID != 0
  21. and ot.[Type]   in ('Users', 'ServiceUsers')
  22. and datediff(day, cast(r.Created as Date), cast(getdate() as Date)) <= 7
  23. ) as a
  24. inner join Users u on u.UserID = a.UserId
  25. inner join Customers c on c.CustomerID = u.CustomerID
  26. where c.CustomerID in (select CustomerID from tf_CustomerHierarchy(@CustomerId, 1))
  27. order by a.Created desc
Metadata:
  1. {
  2. "Title":"Recent User Related Actions (Last 7 Days)",
  3. "sort":{
  4. "allowSorting":true,
  5. "isDefault":true,
  6. "defaultSortBy":"Customer",
  7. "direction":"ASC"
  8. },
  9. "filter":{
  10. "allowFiltering":true
  11. },
  12. "pagination":{
  13. "allowPaging":true,
  14. "pageSize":10
  15. },
  16. "columns":[
  17. {
  18. "name":"User",
  19. "autoFit":true,
  20. "allowResizing":true,
  21. "width":null,
  22. "isLinkable":true,
  23. "link":"/User/Users.aspx?CustomerID={CustomerId}\u0026UserID={UserId}",
  24. "linkText":"{User}"
  25. },
  26. {
  27. "name":"Customer",
  28. "autoFit":true,
  29. "allowResizing":true,
  30. "width":null,
  31. "isLinkable":true,
  32. "link":"/Customer/Customers.aspx?CustomerID={CustomerId}",
  33. "linkText":"{Customer}"
  34. },
  35. {
  36. "name":"Action Taken",
  37. "autoFit":false,
  38. "allowResizing":false,
  39. "width":null,
  40. "isLinkable":false,
  41. "link":null,
  42. "linkText":null
  43. }
  44. ]
  45. }
Chart:



    • Related Articles

    • Atria Billing Setup User Guide

      Objective This article describes how to configure Atria to utilize the latest billing features. This document outlines the billing setup attributes that should be configured.    Applies to Introduced in Atria version 12.0.0 Billing Setup Overview To ...

    • Microsoft New Commerce Experience in Atria

      What is Microsoft's New Commerce Experience? Microsoft New Commerce is the next evolution in the Microsoft Cloud Solution Provider (CSP) Program where they are making changes to support the future of business. Microsoft has put through a raft of ...

    • Creating a new Microsoft Online Contact in Atria

      OBJECTIVE This aims to provide guidance on how to create Microsoft Online Contacts in Atria. APPLIES TO Atria Version 12.13+ Steps in Creating a new Microsoft Online Contact Go to Customer Main Menu in the left pane > Click Customers > Search for the ...

    • Creating a new Microsoft Online groups via Atria

      Objectives This aims to provide guidance on MSOL Group Management in Atria Applies to: Atria Version 12.13.15 or Newer. Steps in Creating a new Microsoft 365 Group via Atria Go to Customer Main Menu in the left pane > Click Customers > Search for the ...

    • Billing Process Overview

      Objective This article outlines, at a high level, the billing features available in Atria. The billing feature provides transparency of Atria billable services that have been consumed, down to the day. Applies To Introduced in Atria version 12.0.0. ...