Microsoft Teams Dashboard

How do I import and configure this dashboard?

1. Ensure you're using SquaredUp DS v5.4 or above.

   Already a SquaredUp customer?

   Get the latest version of SquaredUp DS for SCOM

   New to SquaredUp?

   Try it for free

2. Download and install the public PowerShell module MicrosoftTeams ExchangeOnlineManagement. You can download it here.
3. Install a PowerShell module for the database interaction with your DBMS of choice.
4. Create the database with the following PowerShell script. The database defines the table definitions for MY SQL for the current semester . The current semester is used in the PowerShell profiles which enable you to dynamically switch between the current semester (defined in the database) and the previous semester (dynamically created based on whatever current semester is defined in the database).
   Note: For the table, you only need the TeamGroupID part for this dashboard. The other parts of the table are for functions the dashboard creator needed outside of this dashboard.
   Note: You'll need to replace the database calls Invoke-CoSBD with calls that work with whatever PowerShell database module or scripts you're using.
   Script for creating the database

   $ConnectionString = <yourConnectionString>
   
   $CreateTeamsTable = @"
   CREATE TABLE [dbo].[TeamsTable](
   	[ID] [int] NOT NULL,
       [GroupID] [varchar](355) NOT NULL,
   	[Semester] [nchar](10) NOT NULL,
   	[subject] [nchar](10) NOT NULL,
   	[number] [nchar](10) NOT NULL,
   	[section] [nchar](10) NOT NULL,
   	[teamgroupid] [varchar](355) NOT NULL,
   	[teamDisplayName] [varchar](355) NOT NULL,
       [Type] [varchar](10) NOT NULL,
       CONSTRAINT PK_TeamsTable_ID PRIMARY KEY (ID)
   ) ON [PRIMARY]
   "@
   
   $CreateCurrentSemester = @"
   CREATE TABLE [dbo].[CurrentSemester](
   	[ID] [int] NOT NULL
   ) ON [PRIMARY]
   "@
   
   $CreateSemseter = @"
   CREATE TABLE [dbo].[Semester](
   	[ID] [int] NOT NULL,
   	[SemesterCode] [int] NOT NULL
   ) ON [PRIMARY]
   "@
   
   Invoke-CoSDBMSSQLNonSelectQuery -ConnectionString $connectionString -Query $CreateTeamsTable
   Invoke-CoSDBMSSQLNonSelectQuery -ConnectionString $connectionString -Query $CreateCurrentSemester
   Invoke-CoSDBMSSQLNonSelectQuery -ConnectionString $connectionString -Query $CreateSemester

   Copy code to clipboard

   $ConnectionString = <yourConnectionString>
   
   $CreateTeamsTable = @"
   CREATE TABLE [dbo].[TeamsTable](
   	[ID] [int] NOT NULL,
       [GroupID] [varchar](355) NOT NULL,
   	[Semester] [nchar](10) NOT NULL,
   	[subject] [nchar](10) NOT NULL,
   	[number] [nchar](10) NOT NULL,
   	[section] [nchar](10) NOT NULL,
   	[teamgroupid] [varchar](355) NOT NULL,
   	[teamDisplayName] [varchar](355) NOT NULL,
       [Type] [varchar](10) NOT NULL,
       CONSTRAINT PK_TeamsTable_ID PRIMARY KEY (ID)
   ) ON [PRIMARY]
   "@
   
   $CreateCurrentSemester = @"
   CREATE TABLE [dbo].[CurrentSemester](
   	[ID] [int] NOT NULL
   ) ON [PRIMARY]
   "@
   
   $CreateSemseter = @"
   CREATE TABLE [dbo].[Semester](
   	[ID] [int] NOT NULL,
   	[SemesterCode] [int] NOT NULL
   ) ON [PRIMARY]
   "@
   
   Invoke-CoSDBMSSQLNonSelectQuery -ConnectionString $connectionString -Query $CreateTeamsTable
   Invoke-CoSDBMSSQLNonSelectQuery -ConnectionString $connectionString -Query $CreateCurrentSemester
   Invoke-CoSDBMSSQLNonSelectQuery -ConnectionString $connectionString -Query $CreateSemester

5. Populate the database with your data.
6. Create two PowerShell profiles with the following settings:
   Note: The reason why you create two profiles is to be able to dynamically switch between semesters. By default, the tiles use the Current Semester profile. The second profile Previous Semester enables you to switch to the previous semester just by selecting this profile in the tile. You could also create a second dashboard for the previous semester with identical tiles and set them to use the previous semester profile.
   How to create a PowerShell profile

   1. From the top right hand menu ☰ click system.
   2. Go to the PowerShell tab.
   3. Click add new profile.
   4. Enter a name and a description for the new profile.
   5. Enter the profile script.
   6. Click add profile.
      The profile is now saved and can be used in a PowerShell tile.

   For more help creating a PowerShell profile see How to use the PowerShell tile

   Profile 1:[@portabletext/react] Unknown block type "tableBlock", specify a component for it in the `components.types` prop
   PowerShell profile script

   #database Connection
   $connectionString = "Server=itassqlprod01.boilerad.purdue.edu;database=CosReporting;Trusted_connection=True;"
   
   #Create the credential needed
   $cert = get-childitem -path cert:\localmachine\my | where {$_.subject -like "*cn=purdue*"}
   $cypherText = '<EncryptedPassword>'
   $un = "<Username>"
   $credential = new-object -typename System.Management.Automation.PSCredential -ArgumentList $un, $<decrpytedPassword>)
   
   #Get the semester code based on the active semester table
   $QueryCurrentSemester = "SELECT semestercode FROM ClassTeamsSemester JOIN ClassTeamsCurrentSemester on ClassTeamsSemester.ID = ClassTeamsCurrentSemester.ID"
   $SemesterCode = (Invoke-CoSDBMSSQLSelectQuery -ConnectionString $connectionString -Query $QueryCurrentSemester).tables[0].rows[0].semestercode

   Copy code to clipboard

   #database Connection
   $connectionString = "Server=itassqlprod01.boilerad.purdue.edu;database=CosReporting;Trusted_connection=True;"
   
   #Create the credential needed
   $cert = get-childitem -path cert:\localmachine\my | where {$_.subject -like "*cn=purdue*"}
   $cypherText = '<EncryptedPassword>'
   $un = "<Username>"
   $credential = new-object -typename System.Management.Automation.PSCredential -ArgumentList $un, $<decrpytedPassword>)
   
   #Get the semester code based on the active semester table
   $QueryCurrentSemester = "SELECT semestercode FROM ClassTeamsSemester JOIN ClassTeamsCurrentSemester on ClassTeamsSemester.ID = ClassTeamsCurrentSemester.ID"
   $SemesterCode = (Invoke-CoSDBMSSQLSelectQuery -ConnectionString $connectionString -Query $QueryCurrentSemester).tables[0].rows[0].semestercode

   Profile 2:[@portabletext/react] Unknown block type "tableBlock", specify a component for it in the `components.types` prop
   PowerShell profile script

   #database Connection
   $connectionString = "Server=itassqlprod01.boilerad.purdue.edu;database=CosReporting;Trusted_connection=True;"
   
   #Create the credential needed
   $cert = get-childitem -path cert:\localmachine\my | where {$_.subject -like "*cn=purdue*"}
   $cypherText = '<EncryptedPassword>'
   $un = "<Username>"
   $credential = new-object -typename System.Management.Automation.PSCredential -ArgumentList $un, $(<DecryptedPassword>)
   
   #Get the semester code based on the active semester table
   $QueryCurrentSemester = "SELECT ClassTeamsSemester.ID FROM ClassTeamsSemester JOIN ClassTeamsCurrentSemester on ClassTeamsSemester.ID = ClassTeamsCurrentSemester.ID"
   $CurrentSemesterCode = (Invoke-CoSDBMSSQLSelectQuery -ConnectionString $connectionString -Query $QueryCurrentSemester).tables[0].rows[0]
   $PreviousSemesterID = $($CurrentSemesterCode.ID) - 1
   $QueryPreviousSemester = "SELECT semestercode FROM ClassTeamsSemester WHERE ID = '$PreviousSemesterID'"
   $SemesterCode = (Invoke-CosDBMSSQLSelectQuery -ConnectionString $connectionString -Query $QueryPreviousSemester).tables[0].rows[0].SemesterCode

   Copy code to clipboard

   #database Connection
   $connectionString = "Server=itassqlprod01.boilerad.purdue.edu;database=CosReporting;Trusted_connection=True;"
   
   #Create the credential needed
   $cert = get-childitem -path cert:\localmachine\my | where {$_.subject -like "*cn=purdue*"}
   $cypherText = '<EncryptedPassword>'
   $un = "<Username>"
   $credential = new-object -typename System.Management.Automation.PSCredential -ArgumentList $un, $(<DecryptedPassword>)
   
   #Get the semester code based on the active semester table
   $QueryCurrentSemester = "SELECT ClassTeamsSemester.ID FROM ClassTeamsSemester JOIN ClassTeamsCurrentSemester on ClassTeamsSemester.ID = ClassTeamsCurrentSemester.ID"
   $CurrentSemesterCode = (Invoke-CoSDBMSSQLSelectQuery -ConnectionString $connectionString -Query $QueryCurrentSemester).tables[0].rows[0]
   $PreviousSemesterID = $($CurrentSemesterCode.ID) - 1
   $QueryPreviousSemester = "SELECT semestercode FROM ClassTeamsSemester WHERE ID = '$PreviousSemesterID'"
   $SemesterCode = (Invoke-CosDBMSSQLSelectQuery -ConnectionString $connectionString -Query $QueryPreviousSemester).tables[0].rows[0].SemesterCode

7. Download and import this dashboard pack.

   Download pack for SCOM Dashboard Server
   How to import a dashboard pack

   1. Download the dashboard pack zip file for the dashboard pack you wish to import.

      There may be additional steps before or after the import to get the dashboard working in your environment.

   2. In DS for SCOM go to the top right hand menu ☰ > Import Dashboard Packs and drag the zip file into the Manual Import box.
      

      

   3. The dashboard pack is imported and if the pack contains top-level dashboards, these will automatically appear in the navigation bar, usually in a folder called 'Community Dashboards' for dashboard packs from the SquaredUp Community. If the dashboard pack also contains perspectives (see Working with perspectives), then you'll see these when you drilldown to the relevant object.
   4. Carry out any additional steps required to make the dashboard work in your environment. These steps will be described on the download page for each dashboard. For example, you may need to create the correctly named Web API provider, create a PowerShell profile, or edit tile collections.
   5. Edit the imported dashboard as required.

8. Add an interval and a timeout to your PowerShell tiles.
   1. Edit the tile by clicking the Edit button.
   2. Open the environment panel.
   3. Enter an interval and a timeout fit to your environment.
      Note: The MicrosoftTeams and ExchangeOnlineManagement modules do not process large numbers of teams quickly. Searching for deleted teams out of 200 teams can take 6-7 minutes. For those scripts, you either need to increase the timeout length or run them outside of SquaredUp DS.
      What is the timeout and how do I run scripts outside of SquaredUp DS?

      You can control when the script will run into a timeout. By default, scripts will be aborted after 1 min. You can define a longer timeout for long running scripts.

      Best practice:

      Even if you can use long running scripts by setting the timeout to several minutes, you shouldn't use scripts that run for a long time. Long running scripts lead to tiles that show a loading spinner for minutes and are not user-friendly.

      Tip:

      Instead of setting the timeout for scripts to a longer time, run the script outside of SquaredUp DS periodically, and store the results in a CSV file. Create a PowerShell tile that accesses and visualizes the results in the CSV file.

      What is the interval?

      The interval enables you to limit how often a script gets executed in SquaredUp DS. For example, you might want to run a script less frequently if it makes a request to an API that charges you per request or an API that puts a limit on how many requests you can make within a time period.

      How does the interval affect how often PowerShell scripts are run in SquaredUp DS?

      By default, a PowerShell script will be executed every time the dashboard refreshes (every minute). If you set the interval to 10 mins, you create a 10 min cache for your script results and your script won't run until the last fetched results are 10 mins old.

      The interval is not a schedule, it does not trigger a script execution every 10 mins. Scripts will only be executed when the dashboard is reloaded and the results in the cache are older than the interval (10 mins in this example). The cache is valid for all users, which means if another user opens the same dashboard in SquaredUp DS or via Open Access and the script has been executed 5 mins ago, they see the cached results immediately but have to wait for 5 mins to see fresh results.

      Can multiple tiles share the same cache?

      Identical tiles share the same cache. For tiles to be considered identical, the JSON code of both tiles needs to be identical. If you want to use identical tiles to reduce the load on the back-end, you can copy the JSON from one tile and paste it into a different tile.
      The shared cache works across your whole SquaredUp DS instance on:

      • All dashboards (including Open Access dashboards), as long as they use the same page timeframe
      • Perspectives for the same object on the same drill-down using the same page timeframe

9. Publish the dashboard.

   How to publish a dashboard

   A newly created dashboard will not be visible to others until it is published.

   Only admins can publish dashboards, unless you have been given author permissions to a Team Folder see Team Folders

   If you made changes to an existing dashboard, the changes will only be visible to others after you published the dashboard again.

   You can identify a dashboard that has not been published yet or has unpublished changes by the unpublished button at the top:

   

   When you click on the unpublished button, you'll have two options:

   • Publish will make the dashboard or changes visible to others.
     Note: A newly created dashboard will appear on the menu where you created it. To move the dashboard to a different place on the navigation bar see How to edit the Navigation Bar.
   • Discard will delete your draft dashboard if it has never been published or, if you made changes to an already published dashboard, discard the changes.

   Publishing dashboards to different audiences

   Find out how to publish dashboards to a subset of users using Team Folders or visible to anyone even unlicensed users with Open Access (Sharing Dashboards with anyone - Open Access).