PowerShell and SQL: Monitoring Disk Space - XML and ADO.NET Class Libraries
Introduction
This article shows one possible solution for monitoring disk space usage using SQL Server and a simple PowerShell script by parsing XML data and Using ADO.NET class libraries. The idea is borrowed from the external blog Monitoring Disk Space with SQL Server and PowerShell. Have made customization like adding server name to a central table and reading server name from the input file which acts as a source for an entire script Also, the SP is altered to add server name.
Note: The intention behind this article is to show the different methods of capturing disk space metrics.
Step by Step details
The section talks about the high-level objectives of this article
- Input File "Server.csv"
- Create table "DiskVolume"
- Prepare XML data
- Parsing XML data using openXML
- Call PoSH script
- Insert data into repository using SP
Input File
To execute the script, the input file is necessary. First, create a “Server.csv” file containing all the SQL Servers that your intend to monitor and store disk space usage metrics
Table Creation
In this first code section is the table definition I will be using to store the data collected by the PowerShell script. Please note that I have opted to define the [UsedSpaceKB], [PercentFree] and [PercentUsed] fields as computed columns to simplify the reports which will be utilizing this data. I have also chosen to use kilobytes (kb) as my unit of measurement, You can customize as per your requirement. The columns are self-explanatory.
CREATE TABLE [dbo].[DiskVolume]
(
[Server] varchar( 25 ) NOT NULL,
,[DiskVolumeID] INT IDENTITY( 1 , 1 ) NOT NULL
,[CheckDate] DATETIME NOT NULL
,[Name] VARCHAR( 128 ) NULL
,[Label] VARCHAR( 128 ) NULL
,[DriveLetter] VARCHAR( 2 ) NOT NULL
,[CapacityKB] BIGINT NOT NULL
,[FreeSpaceKB] BIGINT NOT NULL
,[UsedSpaceKB] AS ([CapacityKB] - [FreeSpaceKB])
,[PercentFree] AS (CONVERT(DECIMAL( 18 , 2 ), CAST([FreeSpaceKB] AS FLOAT) / CAST([CapacityKB] AS FLOAT) * 100 ))
,[PercentUsed] AS (CONVERT(DECIMAL( 18 , 2 ), CAST([CapacityKB] - [FreeSpaceKB] AS FLOAT) / CAST([CapacityKB] AS FLOAT) * 100 ))
)
ON [PRIMARY];
Prepare XML document
This section prepares the XML document to demonstrate the use of sp_xml_preparedocument and openXML clause. The below code generates the valid XML document consists of root level, top level, and child level tags.
$server= 'hqdbsp18'
[object]$Volumes = Get-WmiObject -NameSpace "root/cimv2" -ComputerName $Server Win32_Volume -Filter "DriveType = 3 AND Label != 'System Reserved'";
[string]$XmlData = "<root><cimv2>" ;
$Volumes | % { $XmlData = $XmlData + "<Win32_Volume ServerName=`" " + $Server + " ` " Name=`" " + $_.Name + " ` " Label=`" " + $_.Label + " ` " DriveLetter=`" " + $_.DriveLetter + " ` " Capacity=`" " + $_.Capacity + " ` " FreeSpace=`" " + $_.FreeSpace + " ` "></Win32_Volume>" };
$XmlData = $XmlData + "</cimv2></root>" ;
$XmlData
Parsing XML Document
This next code section shows the definition of the procedure used to insert data into the table above. The idea is to pass in one XML parameter as data, containing all the information collected from all the disk drives. The procedure then reads the XML text provided as input, parses the text by using the MSXML parser, and then inserts the parsed data into the table.The second parameter specifies how many days of data to retain in the table and then the procedure purges any expired data after completing the insert.
sp_xml_preparedocument hdoc OUTPUT, xmltext, xpath_namespaces
sp_xml_preparedocument is a Microsoft shipped extended stored procedure to parse xml document in SQL Server
- hdoc: This is an output parameter of INT datatype, this generates a handle of parsed XML.
- Xmltext: XML document to parse.
- xpath_namespaces: The namespace of XML, if you are parsing an untyped XML then this parameter is not mandatory. Default is <root xmlns:mp=”urn:schemas-microsoft-com:xml-metaprop”>
OPENXML: This is the next step after parsing and getting a handle from sp_xml_preparedocument procedure. OPENXML is a function to read the xml into a table.
SELECT * FROM OPENXML (@hdoc, rowpattern, flag) WITH (Column patterns)
@hdoc: Is an output variable of hdoc (XML handle)
Row pattern: An XML can have many tags like root tags, top-level tags and many child tags. So row pattern defines an XPATH, telling your query, from where to start looking for the data in XML while shredding.
Column pattern: we know row pattern is useful for targeting a level and start searching the data from the target level, but what if some of the data are out of the targeted row pattern level and are present in one upper or lower level? You can’t change the row pattern, so you will have to use column pattern to reach out one upper or lower level to fetch the required data.
flags :Indicates the mapping that should be used between the XML data and the relational rowset, and how the spill-over column should be filled. flags is an optional input parameter. The flag value is set to 0 which indicates the sql is using default mapping
DECLARE @xml xml, @hdoc int
SET @xml = '
<root><cimv2>
<Win32_Volume ServerName="HQDBSP18" Name="C:\" Label="OS" DriveLetter="C:" Capacity="42947571712" FreeSpace="8456220672"></Win32_Volume><Win32_Volume ServerName="HQDBSP18" Name="E:\" Label="Apps" DriveLetter="E:" Capacity="8587833344" FreeSpace="4280815616"></Win32_Volume><Win32_Volume ServerName="HQDBSP18" Name="F:\" Label="Data" DriveLetter="F:" Capacity="220113924096" FreeSpace="24309551104"></Win32_Volume>
<Win32_Volume ServerName="HQDBSP18" Name="G:\" Label="logs" DriveLetter="G:" Capacity="51537506304" FreeSpace="41026211840"><Win32_Volume>
</cimv2></root>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml --Preparing XML handle
SELECT * FROM OPENXML(@hdoc, '/root/cimv2/Win32_Volume', 0) --Row Pattern
WITH (
[ServerName] VARCHAR (128) '@ServerName' --column Pattern
,[ Name ] VARCHAR (128) '@Name'
,[Label] VARCHAR (128) '@Label'
,[DriveLetter] VARCHAR (2) '@DriveLetter'
,[Capacity] BIGINT '@Capacity'
,[FreeSpace] BIGINT '@FreeSpace')
EXEC sp_xml_removedocument @hdoc --Releasing memory
Here in above query, row pattern is "/root/cimv2/win32_Volume”, that means, the starting point is “win32_volume” tag. For each column, we have different column patterns defined, “ServerName”, "Name","Label","Label","DriveLetter", "Capacity","FreeSpace" under “win32_Volume” element, so we did not have to traverse any level up or down to get the data. Its pretty straight forward hence the default flag setting is in place.
This is another Microsoft shipped extended stored procedure to remove the XML handle and release the memory that was generated by sp_xml_preparedocument while parsing and storing the XML document. It's important to release the memory after shredding the XML otherwise, this will keep the memory allocated until the session is closed.
EXECUTE [dbo].[sp_xml_removedocument] @hdoc;
Stored Procedure Creation
This section creates the stored procedure on the central server where you already create the repository to store the disk space usage metrics
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[InsertDiskVolume]') AND [type] IN (N'P', N'PC'))
BEGIN
EXECUTE ('CREATE PROCEDURE [dbo].[InsertDiskVolume] AS RAISERROR(''UNDEFINED!'', 16, 1);');
END ;
GO
ALTER PROCEDURE [dbo].[InsertDiskVolume]
(
@XmlData XML,
@HistoryDays SMALLINT = 90
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @hdoc AS INT;
--Create an internal representation of the XML document.
EXECUTE [dbo].[sp_xml_preparedocument] @hdoc OUTPUT, @XmlData;
INSERT INTO [dbo].[DiskVolume]
(
[Server]
,[CheckDate]
,[ Name ]
,[Label]
,[DriveLetter]
,[CapacityKB]
,[FreeSpaceKB]
)
SELECT [ServerName]=[Volume].[Servername]
,[CheckDate] = CURRENT_TIMESTAMP
,[ Name ] = [Volume].[ Name ]
,[Label] = [Volume].[Label]
,[DriveLetter] = [Volume].[DriveLetter]
,[CapacityKB] = [Volume].[Capacity] / 1024
,[FreeSpaceKB] = [Volume].[FreeSpace] / 1024
FROM OPENXML(@hdoc, '/root/cimv2/Win32_Volume', 0)
WITH
(
[ServerName] VARCHAR (128) '@ServerName'
,[ Name ] VARCHAR (128) '@Name'
,[Label] VARCHAR (128) '@Label'
,[DriveLetter] VARCHAR (2) '@DriveLetter'
,[Capacity] BIGINT '@Capacity'
,[FreeSpace] BIGINT '@FreeSpace'
)
AS [Volume];
EXECUTE [dbo].[sp_xml_removedocument] @hdoc;
DELETE
FROM [dbo].[DiskVolume]
WHERE [CheckDate] < DATEADD(d, -@HistoryDays, CURRENT_TIMESTAMP);
END ;
Execute PoSH script
Now all we need is the Powershell script to generate the same XML structured data to pass into the stored procedure. The following is the PowerShell code, created to generate the XML data and calls the insert procedure. You should be able to copy/paste the PowerShell code below into your IDE of choice and run it without error. I’ve highlighted line #7 where you will need to change the connection string parameters(Server and Database) and line #12 where you will need to change the procedure name if needed.
ADO.NET
It is possible to use ADO.NET within PowerShell to pass queries, sp, and commands to SQL Server.
Create a Connection
You simply create an object of System.Data.SqlClient.SqlConnection and pass the connection string that will be used to connect to the given SQL Server instance…don't forget to open it.
Create Command
You have a few options here because the SqlConnection actually contains a method that you can use to create your command object,
ExecuteNonQuery
Executes a Transact-SQL statement against the connection and returns the number of rows affected.
01. Import-csv C:\InputServer.csv|%{
02. If ((Test-Connection $_.ServerName -count 1 -quiet)) {
03. [object]$Volumes = Get-WmiObject -NameSpace "root/cimv2" -ComputerName $_.ServerName Win32_Volume -Filter "DriveType = 3 AND Label != 'System Reserved'";
04. [string]$XmlData = "<root><cimv2>" ;
05. $Volumes | % { $XmlData = $XmlData + "<Win32_Volume ServerName=`" " + $_.SystemName + " ` " Name=`" " + $_.Name + " ` " Label=`" " + $_.Label + " ` " DriveLetter=`" " + $_.DriveLetter + " ` " Capacity=`" " + $_.Capacity + " ` " FreeSpace=`" " + $_.FreeSpace + " ` "></Win32_Volume>" };
06. $XmlData = $XmlData + "</cimv2></root>" ;$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
07. $SqlConnection.ConnectionString = "Server=HQDBSP18;Database=PowerSQL;Integrated Security=TRUE;" ;
08. $SqlConnection.Open();
09. $SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
10. $SqlCommand.CommandTimeout = 120 ;
11. $SqlCommand.Connection = $SqlConnection;
12. $SqlCommand.CommandText = "EXECUTE [dbo].[InsertDiskVolume] @XmlData = N'$XmlData';" ;
13. $Result = $SqlCommand.ExecuteNonQuery();
14. $SqlConnection.Close();
15. }
16. }
Output
Conclusion
- It shows the use of ADO.Net class library as an alternative for invoke-sqlcmd
- The advantage of this script is that from the point of scheduling. The same script can be scheduled in task scheduler or SQL Agent
- The data is stored in the repository and it can be used for capacity planning and forecasting
- Data purging in place. We can schedule a job to purge the data
- It's an effort to demonstrate the one other way of gathering important metrics of system measurement
Reference