Pulling Reports from a DSC Pull Server Configured for SQL

Microsoft

Sep 20, 2018

First published on TechNet on Jul 23, 2018

Hi! Serge Zuidinga here, SCOM PFE, and I would like to thank you for visiting this blog and welcome you to the second post about using a PowerShell DSC Web Pull Server with a SQL database. If you haven't read through it already, you can find my first post on this topic here: Configuring a PowerShell DSC Web Pull Server to use SQL Database Now that you've installed and configured a pull server, it's time to do some reporting. After all, you do want to know if all connected nodes are compliant. We have several ways of going about it, and in this post, I will show you how you can get this information from the SQL database. #Disclaimer The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.#

Let's get started

Using PowerShell to retrieve compliancy information

As you can see in the following screenshot, I've got my node configured to connect to my pull server that I created earlier: I can easily check to see if I'm compliant (the Telnet client should be installed): So far, so good! You can even do this for multiple nodes that are connected to the pull server: You can even do something like this: But how do we go about getting compliancy information for hundreds of servers? It's stored in our SQL database so let's head over there and get the information!

Prerequisites

We are going to create four different views within the DSC SQL database that we can query to see how are connected nodes are doing. Before we can create those views and query them, we need to create three functions first. Let's get cracking! Creating the three functions Let's open SQL Server Management Server and connect to our SQL server instance where the DSC SQL database is hosted. Execute the following query which will create the three functions we need: USE [DSC] GO CREATE FUNCTION [dbo] . [Split] ( @InputString VARCHAR ( 8000 ), @Delimiter VARCHAR ( 50 ) ) RETURNS @Items TABLE ( Item VARCHAR ( 8000 ) ) AS BEGIN IF @Delimiter = ' ' BEGIN SET @Delimiter = ',' SET @InputString = REPLACE ( @InputString , ' ' , @Delimiter ) END IF ( @Delimiter IS NULL OR @Delimiter = '' ) SET @Delimiter = ',' DECLARE @Item VARCHAR ( 8000 ) DECLARE @ItemList VARCHAR ( 8000 ) DECLARE @DelimIndex INT SET @ItemList = @InputString SET @DelimIndex = CHARINDEX ( @Delimiter , @ItemList , 0 ) WHILE ( @DelimIndex != 0 ) BEGIN SET @Item = SUBSTRING ( @ItemList , 0 , @DelimIndex ) INSERT INTO @Items VALUES ( @Item ) -- Set @ItemList = @ItemList minus one less item SET @ItemList = SUBSTRING ( @ItemList , @DelimIndex + 1 , LEN ( @ItemList )- @DelimIndex ) SET @DelimIndex = CHARINDEX ( @Delimiter , @ItemList , 0 ) END -- End WHILE IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString BEGIN SET @Item = @ItemList INSERT INTO @Items VALUES ( @Item ) END -- No delimiters were encountered in @InputString, so just return @InputString ELSE INSERT INTO @Items VALUES ( @InputString ) RETURN END -- End Function GO CREATE FUNCTION [dbo] . [tvfGetRegistrationData] () RETURNS TABLE AS RETURN ( SELECT NodeName , AgentId , ( SELECT TOP ( 1 ) Item FROM dbo . Split ( dbo . RegistrationData . IPAddress , ';' ) AS IpAddresses ) AS IP , ( SELECT ( SELECT [Value] + ',' AS [text()] FROM OPENJSON ( [ConfigurationNames] ) FOR XML PATH ( '' ))) AS ConfigurationName , ( SELECT COUNT (*) FROM ( SELECT [Value] FROM OPENJSON ( [ConfigurationNames] )) AS ConfigurationCount ) AS ConfigurationCount FROM dbo . RegistrationData ) GO CREATE FUNCTION [dbo] . [tvfGetNodeStatus] () RETURNS TABLE AS RETURN ( SELECT [dbo] . [StatusReport] . [NodeName] , [dbo] . [StatusReport] . [Status] , [dbo] . [StatusReport] . [Id] AS [AgentId] , [dbo] . [StatusReport] . [EndTime] AS [Time] , [dbo] . [StatusReport] . [RebootRequested] , [dbo] . [StatusReport] . [OperationType] ,( SELECT [HostName] FROM OPENJSON ( ( SELECT [value] FROM OPENJSON ( [StatusData] )) ) WITH ( HostName nvarchar ( 200 ) '$.HostName' )) AS HostName ,( SELECT [ResourceId] + ',' AS [text()] FROM OPENJSON ( ( SELECT [value] FROM OPENJSON (( SELECT [value] FROM OPENJSON ( [StatusData] ))) WHERE [key] = 'ResourcesInDesiredState' ) ) WITH ( ResourceId nvarchar ( 200 ) '$.ResourceId' ) FOR XML PATH ( '' )) AS ResourcesInDesiredState ,( SELECT [ResourceId] + ',' AS [text()] FROM OPENJSON ( ( SELECT [value] FROM OPENJSON (( SELECT [value] FROM OPENJSON ( [StatusData] ))) WHERE [key] = 'ResourcesNotInDesiredState' ) ) WITH ( ResourceId nvarchar ( 200 ) '$.ResourceId' ) FOR XML PATH ( '' )) AS ResourcesNotInDesiredState ,( SELECT SUM ( CAST ( REPLACE ( DurationInSeconds , ',' , '.' ) AS float )) AS Duration FROM OPENJSON ( ( SELECT [value] FROM OPENJSON (( SELECT [value] FROM OPENJSON ( [StatusData] ))) WHERE [key] = 'ResourcesInDesiredState' ) ) WITH ( DurationInSeconds nvarchar ( 50 ) '$.DurationInSeconds' , InDesiredState bit '$.InDesiredState' ) ) AS Duration ,( SELECT [DurationInSeconds] FROM OPENJSON ( ( SELECT [value] FROM OPENJSON ( [StatusData] )) ) WITH ( DurationInSeconds nvarchar ( 200 ) '$.DurationInSeconds' )) AS DurationWithOverhead ,( SELECT COUNT (*) FROM OPENJSON ( ( SELECT [value] FROM OPENJSON (( SELECT [value] FROM OPENJSON ( [StatusData] ))) WHERE [key] = 'ResourcesInDesiredState' ) )) AS ResourceCountInDesiredState ,( SELECT COUNT (*) FROM OPENJSON ( ( SELECT [value] FROM OPENJSON (( SELECT [value] FROM OPENJSON ( [StatusData] ))) WHERE [key] = 'ResourcesNotInDesiredState' ) )) AS ResourceCountNotInDesiredState ,( SELECT [ResourceId] + ':' + ' (' + [ErrorCode] + ') ' + [ErrorMessage] + ',' AS [text()] FROM OPENJSON ( ( SELECT TOP 1 [value] FROM OPENJSON ( [Errors] )) ) WITH ( ErrorMessage nvarchar ( 200 ) '$.ErrorMessage' , ErrorCode nvarchar ( 20 ) '$.ErrorCode' , ResourceId nvarchar ( 200 ) '$.ResourceId' ) FOR XML PATH ( '' )) AS ErrorMessage ,( SELECT [value] FROM OPENJSON ( [StatusData] ) ) AS RawStatusData FROM dbo . StatusReport INNER JOIN ( SELECT MAX ( EndTime ) AS MaxEndTime , NodeName FROM dbo . StatusReport AS StatusReport_1 WHERE EndTime > '1.1.2000' GROUP BY [StatusReport_1] . [NodeName] ) AS SubMax ON dbo . StatusReport . EndTime = SubMax . MaxEndTime AND [dbo] . [StatusReport] . [NodeName] = SubMax . NodeName ) GO Note : In regards to line 103: SELECT SUM ( CAST ( REPLACE ( DurationInSeconds , ',' , '.' ) AS float )) AS Duration Based on your regional settings, this can throw an error after executing this script. Please consult your local SQL expert to fix the error if it is thrown. Creating the four views With the three functions created, we can now execute the following query to create the views that'll give us the information about all our connected nodes: USE [DSC] GO CREATE VIEW [dbo] . [vRegistrationData] AS SELECT GetRegistrationData .* FROM dbo . tvfGetRegistrationData () AS GetRegistrationData GO CREATE VIEW [dbo] . [vNodeStatusSimple] AS SELECT dbo . StatusReport . NodeName , dbo . StatusReport . Status , dbo . StatusReport . EndTime AS Time FROM dbo . StatusReport INNER JOIN ( SELECT MAX ( EndTime ) AS MaxEndTime , NodeName FROM dbo . StatusReport AS StatusReport_1 GROUP BY NodeName ) AS SubMax ON dbo . StatusReport . EndTime = SubMax . MaxEndTime AND dbo . StatusReport . NodeName = SubMax . NodeName GO CREATE VIEW [dbo] . [vNodeStatusComplex] AS SELECT GetNodeStatus .* FROM dbo . tvfGetNodeStatus () AS GetNodeStatus GO CREATE VIEW [dbo] . [vNodeStatusCount] AS SELECT NodeName , COUNT (*) AS NodeStatusCount FROM dbo . StatusReport WHERE ( NodeName IS NOT NULL) GROUP BY NodeName GO Creating a trigger Almost there! We only need execute the following code which will create and enable a trigger to update the status report information if the DSC database gets an update: USE [DSC] GO CREATE TRIGGER [dbo] . [DSCStatusReportOnUpdate] ON [dbo] . [StatusReport] AFTER UPDATE AS SET NOCOUNT ON BEGIN DECLARE @JobId nvarchar ( 50 ) = ( SELECT JobId FROM inserted ); DECLARE @StatusData nvarchar ( MAX ) = ( SELECT StatusData FROM inserted ); IF @StatusData LIKE '\[%' ESCAPE '\' SET @StatusData = REPLACE ( SUBSTRING ( @StatusData , 3 , Len ( @StatusData ) - 4 ), '\' , '' ) DECLARE @Errors nvarchar ( MAX ) = ( SELECT [Errors] FROM inserted ); IF @Errors IS NULL SET @Errors = ( SELECT Errors FROM StatusReport WHERE JobId = @JobId ) IF @Errors LIKE '\[%' ESCAPE '\' AND Len ( @Errors ) > 4 SET @Errors = REPLACE ( SUBSTRING ( @Errors , 3 , Len ( @Errors ) - 4 ), '\' , '' ) UPDATE StatusReport SET StatusData = @StatusData , Errors = @Errors WHERE JobId = @JobId END GO ALTER TABLE [dbo] . [StatusReport] ENABLE TRIGGER [DSCStatusReportOnUpdate] GO

Getting information

We are now ready to get the information from the database! Registration data Example: SELECT * FROM [DSC] . [dbo] . [RegistrationData] Node status count Example: SELECT * FROM [DSC] . [dbo] . [vNodeStatusCount] Node status (basic information) Example: SELECT * FROM [DSC] . [dbo] . [vNodeStatusSimple] Node status (detailed information) Example: SELECT * FROM [DSC] . [dbo] . [vNodeStatusComplex]

Let's summarize

Original source

I got the idea and all the SQL stuff from this great post by my esteemed colleague Raimund.

The difference

Whereas Raimund is focusing on visualizing data in Power BI, I am focusing on retrieving the data from SQL server itself and providing queries you can use in SQL Server Reporting Services.

The other difference

I also want to point out the different types of JSON data that is stored within our DSC database. For example, if you run this code: USE [DSC] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SELECT FirstStatusReport . NodeName , StatusDataInJSON .*, AdditionalDataInJSON .* FROM dbo . StatusReport AS FirstStatusReport INNER JOIN ( SELECT MAX ( EndTime ) AS EndTime , NodeName FROM dbo . StatusReport AS SecondStatusReport GROUP BY SecondStatusReport . NodeName ) AS MostRecentEntry ON FirstStatusReport . EndTime = MostRecentEntry . EndTime AND FirstStatusReport . NodeName = MostRecentEntry . NodeName CROSS APPLY OPENJSON ( StatusData ) AS StatusDataInJSON CROSS APPLY OPENJSON ( AdditionalData ) AS AdditionalDataInJSON From that, you will see that the outcome is: Focusing on the "Type"-columns you will see that the type for "ResourcesInDesiredState" equals to 4 and that the type for "MetaConfiguration" equals to 5. So, the "ResourcesInDesiredState"-column holds JSON data of the "array"-data type and the "MetaConfiguration"-column holds JSON data of the "object"-data type. The most important reasons for me to point out the differences is so that you are aware of these differences and are aware of the different ways you can pull date from the DSC database.

How does this affect me?

When creating the functions, views and trigger we have been using a lot of "SELECT"-statements but when we queried data to show the difference between the JSON data types, we used "CROSS APPLY"-statements. For example, getting resources in desired state could also be done with this query (as opposed to the function mentioned earlier where we don't use any "CROSS APPLY"-statements): USE [DSC] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SELECT FirstStatusReport . NodeName , ResourcesInDesiredStateInJSONResults .* FROM dbo . StatusReport AS FirstStatusReport INNER JOIN ( SELECT MAX ( EndTime ) AS EndTime , NodeName FROM dbo . StatusReport AS SecondStatusReport GROUP BY SecondStatusReport . NodeName ) AS MostRecentEntry ON FirstStatusReport . EndTime = MostRecentEntry . EndTime AND FirstStatusReport . NodeName = MostRecentEntry . NodeName CROSS APPLY OPENJSON ( StatusData ) WITH ( ResourcesInDesiredState NVARCHAR ( MAX ) '$.ResourcesInDesiredState' AS JSON ) AS ResourcesInDesiredStateInJSON CROSS APPLY OPENJSON ( ResourcesInDesiredStateInJSON . ResourcesInDesiredState ) WITH ( Feature NVARCHAR ( MAX ) '$.InstanceName' ) AS ResourcesInDesiredStateInJSONResults The result is: However, when using this query to pull data from the DSC database the outcome might not be what you expect: USE [DSC] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SELECT FirstStatusReport . NodeName , ResourcesInDesiredStateInJSONResults .*, AdditionalDataInJSONResults .* FROM dbo . StatusReport AS FirstStatusReport INNER JOIN ( SELECT MAX ( EndTime ) AS EndTime , NodeName FROM dbo . StatusReport AS SecondStatusReport GROUP BY SecondStatusReport . NodeName ) AS MostRecentEntry ON FirstStatusReport . EndTime = MostRecentEntry . EndTime AND FirstStatusReport . NodeName = MostRecentEntry . NodeName CROSS APPLY OPENJSON ( StatusData ) WITH ( ResourcesInDesiredState NVARCHAR ( MAX ) '$.ResourcesInDesiredState' AS JSON ) AS ResourcesInDesiredStateInJSON CROSS APPLY OPENJSON ( ResourcesInDesiredStateInJSON . ResourcesInDesiredState ) WITH ( Feature NVARCHAR ( MAX ) '$.InstanceName' ) AS ResourcesInDesiredStateInJSONResults CROSS APPLY OPENJSON ( AdditionalData ) WITH ( PSVersion NVARCHAR ( MAX ) '$.PSVersion' AS JSON ) AS AdditionalDataInJSON CROSS APPLY OPENJSON ( AdditionalDataInJSON . PSVersion ) WITH ( PSVersion NVARCHAR ( MAX ) '$.PSVersion' ) AS AdditionalDataInJSONResults The outcome is:

Conclusion

I hope you have gained more insight into how reporting data is stored in the DSC database, and how to pull data from your DSC database and create reports using just SQL server, SQL Server Reporting Services or even Power BI. Happy reporting and stay tuned on more PowerShell Desired State Configuration!