Server Man
Well-Known Member
- May 17, 2015
- Windows 10
- Firefox 61.0
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!
References
These are sites I got information from the understand how to get data from our DSC database and use it.
JSON in SQL Server
Posts from fellow colleagues
My previous posts
Continue reading...
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!
References
These are sites I got information from the understand how to get data from our DSC database and use it.
JSON in SQL Server
- JSON data in SQL Server
- OPENJSON (Transact-SQL)
- Parse and Transform JSON Data with OPENJSON (SQL Server)
Posts from fellow colleagues
My previous posts
Continue reading...