My Customized SP_Who2 with PowerShell…

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

About these ads

About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in PowerShell. Bookmark the permalink.

2 Responses to My Customized SP_Who2 with PowerShell…

  1. Anonymous says:

    don’t use yellow color it is hard to read.

  2. guyrodge says:

    Great idea!
    I made this a module to be loaded at powershell startup
    Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s