The PowerON Power BI Configuration Manager Dashboard 1612 is now out!

With the 1611 version We've had some great feedback from the community and made some minor changes to the queries as well as adding some additional support information from Microsoft.

  Version 1611 Release Notes
•Included Branch Support information from https://docs.microsoft.com/en-us/sccm/core/servers/manage/current-branch-versions-supported into the admin settings.
 •Addressed duplicate entry issue on Client Information where devices with multiple chassistype were causing relationship error.
 •Updated EndPoint Protection table queries to address duplicate entry error.
 •Added parameter to Support Lifecycle URL note: The URL seems to be intermittently causing a 400 error. To to and fix this, flip the URL to send one and flip it back again.

You can download the Dashboard from the TechNet gallery:

DOWNLOAD

So I came across a random issue when working on enabling PS remoting on a Windows 2012 R2 server with a customer then other day.

Issue

When trying to run Enable-PSRemoting, It would return an error saying: Unable to check the status of the firewall issue.




Resolution

The customer had removed the Windows Remote Management inbound rules in Windows Firewall, there are two ways to sort this.

Using Windows Firewall with Advanced Security GUI

1. Select Inbound Rules
2. Select New Rule from the action pane
3. In the New Rule Wizard, Select Predefined
4. Drop the Predefined list and select Windows Remote Management
5. Click Next
6. In Predefined Rules, select the two rules and click Next
7. Click Finish


Using PowerShell to Create Copy of Predefined Rule

If((Get-NetFirewallRule -Group "@FirewallAPI.dll,-30267" -ErrorAction SilentlyContinue | Measure).Count -gt 0)

{

Write-Output "Firewall Rule Already Exists"

}

Else

{

Write-Output "Firewall Rule Not Found"

New-NetFirewallRule -Group "@FirewallAPI.dll,-30267" -Name "WINRM-HTTP-In-TCP" -DisplayName "Windows Remote Management (HTTP-In)" -Direction Inbound -Description 'Inbound rule for Windows Remote Management via WS-Management. [TCP 5985]' -Profile Domain,Private -Enabled True -Action Allow -Protocol TCP -LocalPort 5985 -Program System -EdgeTraversalPolicy Block -LooseSourceMapping $False -LocalOnlyMapping $false

New-NetFirewallRule -Group "@FirewallAPI.dll,-30267" -Name "WINRM-HTTP-In-TCP-PUBLIC" -DisplayName "Windows Remote Management (HTTP-In)" -Direction Inbound -Description 'Inbound rule for Windows Remote Management via WS-Management. [TCP 5985]' -Profile Public -Enabled True -Action Allow -Protocol TCP -LocalPort 5985 -Program System -RemoteAddress LocalSubnet -EdgeTraversalPolicy Block -LooseSourceMapping $False -LocalOnlyMapping $false

}
Enable-PSRemoting -Force
Traditionally getting the most out of reporting in Service Manager (SCSM) can be challenging. Gaining the visibility and information out of the reports, can be time consuming and difficult, using SQL reports or excel pivot tables to get the information required.

At PowerON we've been working hard to make reporting more accessible and take the pain out of System Center reporting. With the success of our community Power BI Configuration Manager Dashboard, I felt it was a good time to give back to the community and release PowerON BI Service Manager Community Dashboard!

With feedback from our customers and using our expert knowledge of SCSM, we've adapted our PowerON BI Service Manager Pro Dashboard to give the community a snippet of what is capable with Power BI and SCSM.



PowerON BI Service Manager Community Dashboard

This Power BI report enables you to view Incident and Service Request information from System Center Service Manager 2012 R2 and above.

It queries the SCSM DWDataMart SQL database to retrieve SCSM information and presents it in Power BI Desktop. This then allows you to dynamically filter your data in a more visual way.

Version

- 1711 Release Version
- Incident Overview
- Service Requests Overview
- Edit Language in settings
- Edit Company in settings
- Filter number of days returns from DW data.


Incidents (IR)

This provides an overview for incident management around:

- IR by Classifications
- IR by Support Group
- Total IR by Status
- IR by Affected Users
- IR by Assigned to Users
- IR by Urgency
- IR by Impact
- IR by Month and Year
- IR by Time Blocks *Allows you to see IR's by raised time giving you visibility of how busy you Service Desk is by time.
- Table overview



Service Requests (SR)

This provides overview information for service requests around:

- SR By Classifications
- SR By Support Group
- Total SR by Status
- SR by Affected Users
- SR by Assigned to Users
- SR by Urgency
- SR by Priority
- SR by Month and Year
- SR by Time Blocks *Allows you to see SR's by raised time giving you visibility of how busy you Service Desk is by time.
- Table overview



Installation


Ensure you have at least read access to the SCSM SQL Database and you can access the Internet from the machine running Power BI Desktop.

Download the file and the latest Power BI desktop (you can find the lastest BI desktop here).

Once installed Open Power BI.

1. Go to File > Options and Settings > Options.

2. Under Privacy enable Fast Combine to file privacy.


3. Under Security untick "Require user approval for native SQL database queries".


4.  Select Edit Queries to load the Query Editor.


5. Supply parameter information in the Setup section.


- SCSM DW SQL Server
Specify the SCSM Data Warehouse SQL Server name or instance.
- SCSM DW SQL Database
Specify the SCSM Database name (you shouldn't need to change this unless you've changed the name from default).
- Language
Specify language if not using default. (Note: this is as is and not fully supported as not had chance to test)
- Organisation Name
Specify your Organisation name. 
- Filter Days 
By default "0" will return all the records from the SCSM DWDatamart, if you want to limit the data returned, you can specify the number of days to returned with this field (for example, 365 days would bring back the last 365 days worth of records.

6. Select the "Incident" or "Service Request" query under the Service Manager folder and supply any credentials required to access the database.


7. If prompted, Click OK on the encryption support message.

8. Close the Query Editor and load the data  


The next installment of the Power BI Configuration Manager Dashboard is here!

We've been busy making some changes to the successful 1.4 version which we brought out back in July. We're now over 4000 downloads on the technet gallery and can't believe the positive response from the community.

With the ever progressing Power BI (BI) and Configuration Manager (ConfigMgr) updates, we had to address a few problems with query and website changes. I took the decision to wait for this version rather than release a quick fix as we were so far into this one and wanted to get this version out asap.

With this version, we've taken the approach to change naming convention in line with ConfigMgr, moving to a Year/Month format. This should make it easier when BI and ConfigMgr receive updates, as we can release tested version based on a ConfigMgr or BI release.

I hope you'll enjoy the update and the improvements we've made to it :)


Version 1611 Release Notes

  • Tested with November Power BI desktop update
  • Workstation Admin Breakdown consolidated to one page.
  • Site Admin Page Redesign.
  • Ability to drill down through deployed OS support lifecycle added
  • Windows 10 Breakdown page added (Tested on 1602 and above)
  • Minor visual changes to page layouts.
  • Managed Mobile Devices page updated to include information table.
  • Chassis Type table changed to custom table.
  • Client version web query fixed after table column was changed. 
  • Support Life Cycle query fixed after MS changes. Now changed to api call and updated some of the naming conventions. 
  • SCCM Technet blog feed added.
  • DB Fragmentation page visual updated to include min/max on saturation and URL link added.





Client Overview

This view contains a basic overview of client information such as, EndPoint Compliance, Client Activity and Health.




Workstation / Server Breakdown

This contains a breakdown of your site workstations/Servers including manufacturer, operating systems, architecture and even chassis type info! The Chassis Type information now built into the report and workstation admin page consolidated into main page.







*New* Windows 10 Breakdown

This contains a breakdown of your Windows 10 workstations including things like branch and OS version.



Client Health Summary

Contains summary information on your clients health and activity status.



Update Compliance

This view contains a breakdown for update compliance, showing you missing updates by bulletin ID.



EndPoint Compliance

This view contains summary information on client EndPoint compliance and protection status.


*New* Site Admin

The site admin view has been completely revamped. With changes to the support lifecycle page, it stopped the query from pulling back information. We've updated this to now include a drill down for Mainstream, Extended and Service Pack end dates. We've also included the technet blog feed and site information to really made this page usefl for keeping on top of updates.



Managed Mobile Devices

This view contains summary information on Managed Mobile Devices in ConfigMgr. It allows you to view things like Make and OS version of devices connected. We've updated the visuals and included a table view for additional information.



DB Fragmentation 

DB Fragmentation Report into the dashboard. SQL query from Kent Agerlund's NIC 4th Edition. Updated saturation visual to highlight on min/max value.






Installation

You can download the dashboard from the Technet Gallery


Ensure you have at least read access to the SCCM SQL Database and you can access the Internet from the machine running Power BI.

Download the lastest Power BI desktop here.

Once installed Open Power BI.

1. Go to File > Options and Settings > Options.

2. Under Privacy enable Fast Combine to file privacy.

3. Under Security untick "Require user approval for native SQL database queries".


4.  Select Edit Queries to load the Query Editor.


5. Supply parameter information in the Setup section



6. Select the "_Client Information" query under the ConfigMgr and supply any credentials required to access the database.


7. If prompted, Click OK on the encryption support message.


8. Close the Query Editor and load the data  


Publishing to the Web


The desktop version of Power BI is free to use and I used this without publishing the report up to the web just to produce a quick report. The data is on this report is not live as its only from the last time you load the data but you can easily reload the information on startup. 

For an even better experience, you can publish the file up to the web and create a dashboard.  though there can be cost implications but there is a free version.  Check on Power BI's website for further information on what best suits you.

                      

The great thing about publishing is you can utilise the ask a question feature which is awesome!


                              

Power Bi also comes with a mobile app which allows you to access the report from your mobile device anywhere too.



For advanced support, customisations or consultancy contact the PowerON team below:



Like most people, I've been having major withdrawal symptoms from Game of Thrones (GoT) recently and was left wondering what to do until next year to get my GoT fix!

After watching a few YouTube theory videos and restarting the series from season one, I started to
think (being the geek I am) how cool it would be to have a Power BI report specifically for GoT, so I decided to make one!



In this series of blog posts, I'll show you how you can import & manipulate JSON content from web API data sources, use functions to create multiple queries and connect disjointed information to present meaningful data.

That's what I do, I Use BI and I Know Things...

To set upon my mission to create a Power BI report for GoT and we'll need a data source.

After a bit of googling I found a great site called AnAPIofIceandFire.com this was a open API database containing information from A Song of Ice and Fire. We'll use this data to create the report.



Importing JSON Data to Power BI

To begin will start off importing the Houses into BI, the website has excellent information on how to do this and we can view the information via the following URL: http://www.anapioficeandfire.com/api/houses

1. Go to Get Data > Web


2. Paste the URL into the URL section and click OK.


 3. This imports data and automatically detects its a JSON file but notice we have no readable information yet :(


4. We can use the convert to table option to convert and then expand the table to view the content.



Note: Notice I only got 10 results back, this is because by default the header only returns 10, to check this, I added ?page=1&pageSize=1000. This returned a maximum of 50 Houses which obviously isn't enough houses.

In the next post we'll look at creating a function with a parameter to specify the page in the URL to get other results.

So in my previous post, I explained how functions have changed and how you can convert existing queries to a function. In this post I'll continue with my original issue of using multiple queries but using parameters to connect to one SQL server/db.

With the June Power BI desktop update, we can now specify parameters to connect to data sources and there are a couple of ways we can do this...

Creating a Parameter - Through the Query Editor

To use parameters on creation of a data source, you need to have already created a parameter prior to selecting one.

1. Load the query editor.



2. Click Manage Parameters.



3. Create two new parameters for the SQL Server and Database and add the default values in and click OK.



4. Verify you can see the parameters in the left hand queries pane.







Creating a Parameter - On Getting Data

If your query already contains parameters, you can select or create additional parameters through the below:

1. Get data. 


2. Select your data source (I used SQL Server for this example). You'll notice that there is a couple of new drop down boxes...



3. You can select the "New Parameter" option to create (step 3 from previous section) or select "Parameter" and specify an existing one.



When done, you can follow the rest of the wizard and connect to your data source. So what about updating existing queries...?


Updating Existing Queries

In my Part 1 I talked about using a dynamic XML table as a source for my parameters. To update an existing query to use the new parameter method, we need to edit the existing reference variable below.



In the above, you can see we have our variables which currently point to my XML table. To change this to the new parameters all we need to do is the below:



This tells the query that the SCCMServer and SCCMDatabase variables are the relevant parameters and the source then uses these to connect.

If you don't have a preset variable specified like mine, to amend an existing query, all you need to do it add variables first

Let
<VariableName0> = #"<Parameter Name0>",
<VariableName1> = #"<Parameter Name1>",
Source = sql.database(VariableName0, VariableName1, [Query="


With the SQL parameters now set and tested, all I needed to do was then go through the rest of the queries and do the above then all good again!








I created a post a while back on invoking functions to specify parameters for data queries (Link Here). This was happily working using a single function to update multiple queries... until what seems to be the May/June Update. This introduced inline functions and parameters to the BI desktop, which caused me a bit of pain on some of my reports as it switched the way functions were used.


The Problem

So in previous versions when invoking a function, I could use the below code to create a dynamic  XML table, allowing me to use this table as a parameter reference in my other SQL queries.

let SQLSource = (SCCMSQLServer as text, SCCMDatabase as text) =>
let
    Source = Xml.Tables("<Source><Server>" & SCCMSQLServer & "</Server><Database>" & SCCMDatabase & "</Database></Source>"),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Server", type text}, {"Database", type text}})
in
    #"Changed Type"
in
    SQLSource



In the above example, this would have invoked the configuration function and allowed me to reference a field by using the below:

SCCMServer = Configuration{0}[Server],
SCCMDatabase = Configuration{0}[Database],

Since the Power BI desktop update this has changed as it no longer updates its original table but creates a new executed query called "Invoked Function#".

When attempting to create a function off my original query via the right click options,  I'd also get the below.



The Quick Fix

To quickly fix the issue, I initially wrapped the function around one of the SQL queries, added the variables in and then invoked it.

let SQLSource = (SCCMSQLServer as text, SCCMDatabase as text) =>
let  
     
    SCCMServer = SCCMSQLServer,
    SCCMDatabase = SCCMDatabase,

<Rest of Code>
in
    SQLSource

This returned my data but again...in a new query. This wouldn't have matted so much if I was using one query but I had multiple referencing my original table.


The Solution

To properly resolve this issue, we can utilise the parameter feature to create the references required for executing the SQL queries. In part 2, I'll go through how we use parameters in the Power BI desktop and how we update existing queries to variables instead.


Previous PostOlder Posts Home