This is to show a Proof-of-Concept on how SQL Server tools can be use with PowerShell V2.
In this case, I like the way I can use SQL Server 2008 PS cmdlet “ Invoke-SQLCmd “ to collect information off my SQL engine giving me the ability to transform the results in different ways. Keep in mind, when you save the result of your query to a variable, it will contain a snapshot of the data at that point in time. My only purpose in creating a customized version of SP_Who2, is to be able to run my script from my desktop and get the information quickly displayed to my screen and/or generate some kind of reports if necessary.
Getting started:
Here’s brief example of saving the results of a SQL Stored-Procedure to a variable and displaying the results on the console:
The following line “ $dbs | gm –MemberType property ”, will give you a list of the property holding the data you have access to generate the output. Because of the T-SQL“SP_Databases” stored-procedure generates a three column results, we just display all values by typing “ $dbs ” and pressing enter. If you want to exclude the ‘REMARK” column then you use the following one-liner: “ $dbs | Select Database_Name, Database_Size “.
Now that you have the variable $dbs containing your results, this data can be queried and displayed in many other ways. Here’s some PowerShell V2 cmdlets:
1. Out-file
2. ConvertTo-Csv
3. ConvertTo-Html
4. ConvertTo-Xml
5. Out-Gridview
Here’s a One-liner example of displaying the $dbs variable results to an HTML page:
My customized SP_Who2 Stored-Procedure:
In order to use “Invoke-SQLCmd” cmdlet in PowerShell V2, you need to load the SQL Server 2008 snapins and providers into either your PowerShell and/or ISE profile.
Here’s two links that can provide you information on how-to accomplish updating these files:
1. http://blogs.msdn.com/b/mwories/archive/2008/06/14/sql2008_5f00_powershell.aspx
2. http://msdn.microsoft.com/en-us/library/cc281962.aspx
Now, I was very excited that I can execute T-SQL queries and manipulate the results in many different ways. So, I tackle the SP_Who2 stored-procedure so I can build some PowerShell functions with it. But, I was surprise to find an unexpected error when executing it using Invoke-SQLCmd.
Here’s the error:
Error: Invoke-Sqlcmd : A column named ‘SPID’ already belongs to this DataTable.
My cmdlet is complaining there’s already a property column named ‘SPID’ and it can’t generate the datatable. So, I went back to use my SSMS (SQL Server Management Studio) to run the SP_Who2 and verify the result sets to find the two identical columns:
So, been a hacker-by-need, I looked into the SP_Who2 T-SQL code (both SQL2K5/2K8) and identify the lines I causing the duplicate name ‘SPID’, changed it to ‘SPID2’. Then, I can save this code inside my PowerShell script in a string variable using the Here-String ( @” .text_here. “@). Beside, changing the lines I realized that there might be some differences between SP_Who2 SQL 2K5 and 2K8.
Here’s the differences, only one line:
1. In SQL Server 2005 SP_Who2, line #118: “ from master.dbo.sysprocesses with (nolock) ”.
2. In SQL Server 2008 SP_Who2, line #118: “ from sys.sysprocesses_ex with (nolock) “.
I kept the SP_Who2 code from SQL Server 2008 and finish with my custom changes inside a Here-String variable $SQLqry, then we add three more lines to get this Scripts going and Let’s call it *“get-SQLActivity.ps1”.
*Note: This script is using SQL Server 2008 PowerShell “Invoke-SQLCmd” and assuming Windows Authentication.
# ==============================================================================================
# Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2009
# NAME: get-sqlActivity.ps1
# AUTHOR: Max Trinidad , PutItTogether.net
# DATE : 6/25/2010
#
# COMMENT: This is a customized version of SP_Who2 use in a PowerShell script to assist in
# monitoring users activities. When using the $Global:varname will allow you to work with the
# values pull from the custmomized T-SQL SP_Who2 script.
# ==============================================================================================
# - Create Global variable so you can have access to the data after the PSscripts executes
$Global:Sp_Who2 = $null;
# - Customized T-SQL SP_Who2
$SQLqry = @"
--======================================================================
-- SQL Source File -- Created with SAPIEN Technologies PrimalScript 2009
-- NAME: SP_Who2_Custom.sql
-- AUTHOR: Max Trinidad , PutItTogether
-- DATE : 6/24/2010
--
-- COMMENT: Customized SP_WHO2 but the onli change was to rename the dup
-- 'SPID' column to 'SPID2'.
--======================================================================
USE [Master]
GO
/****** Object: StoredProcedure [sys].[sp_who2] Script Date: 06/24/2010 09:46:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Alter procedure [sys].[sp_who2] --- 1995/11/03 10:16
Declare
@loginame nvarchar(128) --sysname = NULL
--as
set nocount on
declare
@retcode int
declare
@sidlow varbinary(85)
,@sidhigh varbinary(85)
,@sid1 varbinary(85)
,@spidlow int
,@spidhigh int
declare
@charMaxLenLoginName varchar(6)
,@charMaxLenDBName varchar(6)
,@charMaxLenCPUTime varchar(10)
,@charMaxLenDiskIO varchar(10)
,@charMaxLenHostName varchar(10)
,@charMaxLenProgramName varchar(10)
,@charMaxLenLastBatch varchar(10)
,@charMaxLenCommand varchar(10)
declare
@charsidlow varchar(85)
,@charsidhigh varchar(85)
,@charspidlow varchar(11)
,@charspidhigh varchar(11)
-- defaults
select @retcode = 0 -- 0=good ,1=bad.
select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))
select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
select
@spidlow = 0
,@spidhigh = 32767
--------------------------------------------------------------
IF (@loginame IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
-- select @sid1 = suser_sid(@loginame)
select @sid1 = null
if exists(select * from sys.syslogins where loginname = @loginame)
select @sid1 = sid from sys.syslogins where loginname = @loginame
IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
begin
select @sidlow = suser_sid(@loginame)
,@sidhigh = suser_sid(@loginame)
GOTO LABEL_17PARM1EDITED
end
--------
IF (lower(@loginame collate Latin1_General_CI_AS) IN ('active')) --Special action, not sleeping.
begin
select @loginame = lower(@loginame collate Latin1_General_CI_AS)
GOTO LABEL_17PARM1EDITED
end
--------
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.
begin
select
@spidlow = convert(int, @loginame)
,@spidhigh = convert(int, @loginame)
GOTO LABEL_17PARM1EDITED
end
--------
raiserror(15007,-1,-1,@loginame)
select @retcode = 1
GOTO LABEL_86RETURN
LABEL_17PARM1EDITED:
-------------------- Capture consistent sysprocesses. -------------------
select
spid
,status
,sid
,hostname
,program_name
,cmd
,cpu
,physical_io
,blocked
,dbid
,convert(sysname, rtrim(loginame))
as loginname
,spid as 'spid_sort'
, substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '
+ substring( convert(varchar,last_batch,113) ,13 ,8 )
as 'last_batch_char'
,request_id
into #tb1_sysprocesses
--from sys.sysprocesses_ex with (nolock)
from sys.sysprocesses with (nolock)
if @@error <> 0
begin
select @retcode = @@error
GOTO LABEL_86RETURN
end
--------Screen out any rows?
if (@loginame in ('active'))
delete #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) in (
'AWAITING COMMAND'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
)
and blocked = 0
--------Prepare to dynamically optimize column widths.
select
@charsidlow = convert(varchar(85),@sidlow)
,@charsidhigh = convert(varchar(85),@sidhigh)
,@charspidlow = convert(varchar,@spidlow)
,@charspidhigh = convert(varchar,@spidhigh)
select
@charMaxLenLoginName =
convert( varchar
,isnull( max( datalength(loginname)) ,5)
)
,@charMaxLenDBName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)
)
,@charMaxLenCPUTime =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)
)
,@charMaxLenDiskIO =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)
)
,@charMaxLenCommand =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)
)
,@charMaxLenHostName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)
)
,@charMaxLenProgramName =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)
)
,@charMaxLenLastBatch =
convert( varchar
,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)
)
from
#tb1_sysprocesses
where
spid >= @spidlow
and spid <= @spidhigh
--------Output the report.
EXEC(
'
SET nocount off
select
SPID --= convert(char(5),spid)
,Status =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else upper(status)
END
,Login = substring(loginname,1,' + @charMaxLenLoginName + ')
,HostName =
CASE hostname
When Null Then '' .''
When '' '' Then '' .''
Else substring(hostname,1,' + @charMaxLenHostName + ')
END
,BlkBy =
CASE isnull(convert(char(5),blocked),''0'')
When ''0'' Then '' .''
Else isnull(convert(char(5),blocked),''0'')
END
,DBName = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,'
+ @charMaxLenDBName + ')
,Command = substring(cmd,1,' + @charMaxLenCommand + ')
,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')
,SPID2 = convert(char(5),spid) --Handy extra for right-scrolling users.
,REQUESTID = convert(char(5),request_id)
from
#tb1_sysprocesses --Usually DB qualification is needed in exec().
where
spid >= ' + @charspidlow + '
and spid <= ' + @charspidhigh + '
-- (Seems always auto sorted.) order by spid_sort
SET nocount on
'
)
LABEL_86RETURN:
if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses
--return @retcode -- sp_who2
--Select @retcode
"@
# - Enter the SQL Server or SQL Server\Instancename
$Svrname = Read-Host "Enter Servername: ";
# - Building the User Activity variable
$Global:SP_Who2 = Invoke-SQLcmd -ServerInstance $Svrname -database master -query $SQLqry
# - Display results to Out-Gridview
$Global:SP_Who2 | Out-GridView -Title $Svrname
|
At the end, this script will display the result in a grid view windows box. Let’s test this script:
Now we have a PowerShell SQL Server Activity Monitor script that you can run from your desktop against any of your SQL Server on the network.
AND!!!!, because I’m using the Global scope on my output variable (“ $Global:SP_Who2 “), after you execute the script, you can get to this variable to generate new custom results.
Check this out!! Let’s run the script. So, after the script have created the gridview, we are going back and get a different results from our variable selecting only want the first 10 lines and with the SPID >= 50:
$SP_Who2 | ? {$_.SPID -GE ’50’} | select -first 10 SPID, Status, Login, HostName, BlkBy, DBName, ProgramName, LastBatch | ft -auto
So, the variable is still available if you want to save the results in a different format.
Well, that it for now. AGAIN, this is Proof-Of-Concept on what you can do with such a powerful tool like PowerShell in combination with SQL Server Administration.
I love PowerShell and SQL Server working together!!
Disclaimer: Use this code at your own risk
don’t use yellow color it is hard to read.
Great idea!
I made this a module to be loaded at powershell startup
Thank you
Wow! This is great. Thank you!