Use PowerShell to Collect Server Data and Write to SQL

Use PowerShell to Collect Server Data and Write to SQL

  • Comments 22
  • Likes

 

Summary: Use Windows PowerShell to collect server data and automatically store that information in a Microsoft SQL Server.

 

Hey, Scripting Guy! QuestionHey, Scripting Guy! How is Windows PowerShell usage by database professionals different from the way that other IT Pros use Windows PowerShell?

 

-- MC

 

Hey, Scripting Guy! AnswerHello MC, Microsoft Scripting Guy Ed Wilson here. Next week, Nov 8-11, 2010, is the annual SQL PASS Summit in Seattle. In honor of this event, we will have guest bloggers from the SQL side of life. Today we will begin with Chad Miller.

 

Chad Miller(Blog|Twitter) is a SQL Server DBA and Senior Manager of Database Administration at Raymond James Financial. In his spare time, he is the Project Coordinator/Developer of the Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at SQL Saturdays and Code Camps.

Take it away Chad!

 

We are data people and we believe data should exist in databases. We spend a good part of our day writing Transact-SQL scripts to query and load data from other sources. Therefore, not surprisingly when we use Windows PowerShell we want to run a command, capture the output and then store the data in a database. After the data is in a database, we can use Transact-SQL to do additional reporting and analysis. We might even use SQL Server Reporting Services to provide Web-based access to the data.

Extract Data

SQL Server 2008 and 2008 R2 provide the Invoke-SqlCmd cmdlet, but only on computers where the sqlps (the SQL Server mini-shell) is installed. As an alternative solution, you can implement your own function which does not require loading external snap-ins. I have copied the invoke-sqlcmd2 function to the Scripting Guys Script Repository. Save the code as invoke-sqlcmd2.ps1 and then dot source our new function into your Windows PowerShell console by using the following command:

. ./invoke-sqlcmd2.ps1

The following example dot sources the invoke-sqlcmd2.ps1 script that contains the invoke-sqlcmd2 function, connects to the pubs database on a SQL server named SQL1 and runs a basic Transact-SQL query. The last command is a single command, but has wrapped to the third line in the output.

PS C:\> . C:\data\ScriptingGuys\2010\HSG_11_1_10\invoke-Sqlcmd2.ps1

PS C:\> Invoke-Sqlcmd2 -ServerInstance sql1 -Database pubs -Query "Select * from auth

ors"

 

 

au_id    : 172-32-1176

au_lname : White

au_fname : Johnson

phone    : 408 496-7223

address  : 10932 Bigge Rd.

city     : Menlo Park

state    : CA

zip      : 94025

contract : True

 

<...OUTPUT Truncated>

 

This example reads a file that contains T-SQL statements, runs the file, and writes the output to another file.

Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"

 

This example uses the Windows PowerShell -Verbose parameter to return the message output of the PRINT command.

Invoke-Sqlcmd2  -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose

VERBOSE: hello world

 

Getting a Server List

When you have to run a Windows PowerShell command across multiple servers, you will frequently see examples in which the list of servers are stored in a text file and read by using the Get-Content cmdlet. However, most database professionals maintain a list of SQL Servers they manage in either a table they create or they may use Central Management Server (CMS) introduced in SQL Server 2008.  Instead of using a text file, for database professionals, it makes more sense to read a SQL table. Let's take a look at an example. For the purposes of this demonstration we'll create a table. However, you could just as easily substitute the msdb.dbo.sysmanagement_shared_registered_servers view from your CMS server.

From SQL Server Management Studio, create a SQL table.

CREATE TABLE server_instance

(server_name varchar(255) NOT NULL);

Next populate the table with a list of SQL Servers:

INSERT server_instance VALUES('Z001');

INSERT server_instance VALUES('Z002\SQ2K8');

INSERT server_instance VALUES('Z003\R2');

 

With our server_instance table populated, we can use our Invoke-SqlCmd2 function to retrieve a list of servers and then call Windows PowerShell command for each. The following example retrieves the version information.

Invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database dbautility -Query "Select server_name FROM server_instance" | foreach-object {Invoke-SqlCmd2 -ServerInstance $_.server_name -Database master -Query "SELECT @@version"}

 

Extract and Load data

A common task for a database professional is collecting and loading data from multiple servers into central utility database. Using the invoke-sqlcmd2 function, we can extract data from a SQL Server data source, but to load data we have to introduce a new function called Write-DataTable. I have uploaded the Write-DataTable Windows PowerShell function to the Scripting Guys Script Repository. Save the Windows PowerShell function from the Script Repository as write-datatable.ps1 and then dot source our new function into the Windows PowerShell console by using the following command.

. ./write-datatable.ps1

The Write-DataTable function uses the .NET Data.SqlClient.SqlBulkCopy class to load an in-memory DataTable or DataRow array into a SQL Server table. This works out well because the cmdlet invoke-sqlcmd or our function invoke-sqlcmd2 returns a datatable object. Let us examine an example database named space usage collection for all databases in your environment to use in forecasting growth.

You have to create a SQL Server table using SQL Server Management Studio. To do this, run the following Transact-SQL script:

CREATE TABLE [dbo].[db_space](

      [server_name] [varchar](128) NOT NULL,

      [dbname] [varchar](128) NOT NULL,

      [physical_name] [varchar](260) NOT NULL,

      [dt] [datetime] NOT NULL,

      [file_group_name] [varchar](128) NOT NULL,

      [size_mb] [int] NULL,

      [free_mb] [int] NULL,

 CONSTRAINT [PK_db_space] PRIMARY KEY CLUSTERED

(

      [server_name] ASC,

      [dbname] ASC,

      [physical_name] ASC,

      [dt] ASC

)

)

 

The T script and management studio are seen in the following figure.

  

 

Next we'll use a Transact-SQL query to collect the server name, database name and file information. We'll save the query to a plain old .sql file that is named get-dbspace.sql. This is the same kind of SQL script that you would execute in SQL Server Management Studio. However, we will call the script from the Invoke-SqlCmd2 function:

$dt = invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database "master" -InputFile ./get-dbspace.sql  -As 'DataTable'

 

Examining the type information about $dt variable we can see the type is of a DataTable as shown in the following figure.

 

 

To load the DataTable into our SQL Server table we'll call the Write-DataTable function:

Write-DataTable -ServerInstance "Z003\R2" -Database "dbutility" -TableName "db_space" -Data $dt

 

Using invoke-sqlcmd2 and piping the output to Out-GridView we can see the data has in fact been loaded.

invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database "dbutility" -Query "SELECT * FROM db_space" |  Out-GridView

The results from this command are shown in the figure below.

 

Extract, Transform, and Load Data

As we've seen, by using a couple of simple functions - invoke-sqlcmd2 and write-datatable - we can easily load data from any SQL Server data source, but what about any Windows PowerShell command? As an example, we want to collect disk space utilization by using Get-WMIObject Win32_LogicalDisk across a group of SQL Servers into a central database for reporting trending. If we can convert the output of our WMI call into a DataTable then we can use our Write-DataTable function. Using a function called Out-DataTable adapted from a script by Marc van Orsouw (Blog|Twitter) we can do just that. I saved the modified script to the Scripting Guys Script Repository.

Save the following code as Out-DataTable.ps1 and source our new function

. ./ Out-DataTable.ps1

 

To load convert the output of a WMI call to a DataTable, we'll pipe to our newly created out-datatable function and assign the output to a $dt.

$dt = Get-WmiObject Win32_LogicalDisk -filter "DriveType=3" | Select @{n='UsageDT';e={get-date -Format "yyyy-MM-dd"}}, '

SystemName, DeviceID, VolumeName, '

@{n='Size';e={$([math]::round(($_.Size/1GB),2))}}, @{n='FreeSpace';e={$([math]::round(($_.FreeSpace/1GB),2))}} | out-datatable

 

Having assigned the output to a datatable you can call the write-datatable to load the collected data into a SQL Server table. This makes out-datatable very powerful in that the output of any Windows PowerShell command can be easily loaded into a SQL Server table. Using write-datatable requires a SQL Server destination table to exist, but instead of manually creating a SQL Server table, we'll use a new function called Add-SqlTable. The Add-SqlTable.ps1 script is also uploaded to the Scripting Guys Script Repository. Save the code as Add-SqlTable.ps1 and source the new function.

The following command will create a new empty SQL Server table named diskspace based on the structure of our DataTable variable $dt:

Add-SqlTable -ServerInstance "Z003\R2" -Database dbutility -TableName diskspace -DataTable $dt

 

Note: Using this technique to create a SQL Server table is not as precise as manually creating a table as certain elements including defining primary keys and data types are not handled. Add-SqlTable works best for a quick data dump of Windows PowerShell data to a SQL Server table.

Finally, with the destination SQL Server table created, we can call our write-datatable function to load the collected data:

Write-DataTable -ServerInstance "Z003\R2" -Database "dbutility" -TableName "diskspace" -Data $dt

 

Using invoke-sqlcmd2 we can see the data was loaded into the SQL Server table:

invoke-sqlcmd2 -ServerInstance "Z003\R2" -Database "dbutility" -Query "SELECT * FROM diskspace" | Out-GridView

 

 

Summary

This post demonstrated how to query and load the output of any Windows PowerShell command into a SQL Server table. The functions invoke-sqlcmd2, write-datatable, out-datatable and add-sqltable can be used as building blocks for many of your Windows PowerShell-based data loading needs.

MC, that is all there is to using Windows PowerShell and SQL Server. SQL week will continue tomorrow when SQL guest blogger Aaron Nelson will talk about how to work with SQL snap-ins.  Thank you Chad, for sharing your time and knowledge with us.

 

I invite you to follow me on Twitter or Facebook. If you have any questions, send email to me at scripter@microsoft.com or post them on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

 

Ed Wilson, Microsoft Scripting Guy

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • I want to run my PS script that dumps all details of all WEBs, Lists, Libraries, Folders, Documents, ITems and Attachments from SharePoint and send that output to a SQL Server database table. Q: Can do? Q: Am I dreaming too big? Imquiring minds need to know jeannine.menger@recommind    (dot)    com

  • wheresjeannine, yes, that's definitely possible.  I've cobbled together Chad's code into what I've dubbed the Extensible Powershell Repository (EPR).  (thebakingdba.blogspot.com/.../servers-extensible-powershell.html).  

    But for yours, all you should have to do is:

    #command is your script filename

    # dot-source the write-datatable.  This assumes the table already exists; otherwise use add-sqltable to create

    # fill out the variables with your info, or just hard-code

    $quer = invoke-expression $command | out-datatable

    Write-DataTable -ServerInstance $server_repository -Database $database_repository -TableName $tablename -Data $quer

  • Hello is this possible

    $quer=Invoke-sqlcmd2 -ServerInstance "test" -Database dba -Query "Select InstanceName FROM SQLInstances1" | foreach-object {Invoke-SqlCmd2 -ServerInstance $_.server_name -Database master -Query "SELECT @@version"}

    Add-SqlTable -ServerInstance "test" -Database dba -TableName version1 -DataTable $dt

    i get an error as below, .my goal is to use a query and load data to a table. from a list of servers in a table.

    Invoke-Sqlcmd2 : Cannot bind argument to parameter 'ServerInstance' because it is an empty string.

    At line:1 char:165

  • Never mind i figured it out

    modified another method to get it to work.

    $serverlist =Invoke-sqlcmd2 -ServerInstance "test" -Database dba -Query "TRUNCATE TABLE db_space;Select InstanceName FROM SQLInstances1"

    $serverlist|Split-Job {%{

    #because the scope is separate in here, you need to re-dot-source and reinit your parameters.

       . D:\sql_tools\invoke-sqlcmd2.ps1;

       . D:\sql_tools\split-job.ps1;

       . D:\sql_tools\write-datatable.ps1;

       $server2 = $_.InstanceName

       write-host $server2

        $quer = Invoke-SqlCmd2 -ServerInstance  $server2 -Database "master" -InputFile D:\sql_tools\get-dbspace.sql  -As 'DataTable'

    Write-DataTable -ServerInstance "test" -Database "dba" -TableName "db_space" -Data $quer

    }}

  • I am curious of how you would manipulate this into a SQL Agent job so it can run on a scheduled basis?

  • Hi i am using the following code. Import-Module -Name 'D:\satheesh\Windows Event Log\invoke-sqlcmd2.ps1' Import-Module -Name 'D:\satheesh\Windows Event Log\write-datatable.ps1' Import-Module -Name 'D:\satheesh\Windows Event Log\Add-SqlTable.ps1' Import-Module -Name 'D:\satheesh\Windows Event Log\Out-DataTable.ps1' $dt = Get-WinEvent -ComputerName 'fe0vm333.de.bosch.com' -LogName 'System' -MaxEvents 10 | out-datatable Add-SqlTable -ServerInstance "XXXXX" -Database "YYYY" -TableName tTestEventLog -DataTable $dt Write-DataTable -ServerInstance "XXXXX" -Database "YYYY" -TableName "tTestEventLog" -Data $dt when i execute this i am getting error as ( I am using SQL Server 2012) do i need to change anything in Add-SQLTable.ps1 file. kindly help me to fix this issue. Add-SqlTable : Cannot read property AnsiPaddingEnabled.This property is not available on SQL Server 7.0. At D:\satheesh\Windows Event Log\AuditCheck\Test.ps1:8 char:1 + Add-SqlTable -ServerInstance "SI0DBP25.de.bosch.com,56554" -Database DB_DIRSVC_M ... because of this i could not get the data table structure to create table. kindly help me to fix this issue.

  • Hi great scripts! with the invoke2 i can export a file to a disk. But how about importing it?

    I'm trying to get a file from the server and use your write-datable function.

    $d = Get-Content C:\_dm-rm\Interfaces\inicial\OINV3.dat
    Write-DataTable -ServerInstance localhost -Database DM-RM-LATAM -TableName dbo.OINV -Data $d

    My guess is that i'm failing to to assigned the file to a datatable i can call the write function.

    Thanks