by salsafreakpr at 2013-04-08 13:45:57
I’ve run into an issue attempting to set up PowerShell WMI permanent eventing using PowerEvents to capture SQL Server blocking that I’m hoping this forum can help resolve. Basically, I am not able to retrieve event details via the WMI tokens that are returned using a CommandLineConsumer. (Or, another way of wording this, how do I consume WMI event details passed via %TargetInstance% variable in a CommandLineConsumer which executes a PowerShell command?)by DonJ at 2013-04-10 03:14:06
Here are some links which provide some background as to how I got to where I’m at:
PowerEvents for Windows PowerShell: http://powerevents.codeplex.com/discussions/267812
Monitor and Respond to Windows Power Events with PowerShell: http://blogs.technet.com/b/heyscripting … shell.aspx
PowerEvents: WMI Event Consumers: http://www.youtube.com/watch?v=r5LRAmRWEb4
PowerShell and SQL Server Events–Changes in Database Options: http://shellyourexperience.com/2011/09/ … e-options/
The PoSh DBA: Solutions using PowerShell and SQL Server: https://www.simple-talk.com/sql/databas … ql-server/
Using PowerShell and WMI Events Queries for Powerful Notifications: https://www.simple-talk.com/sql/databas … fications/
PowerShell and SQL Server Events–BLOCKED PROCESS REPORT and SP_WHOISACTIVE: http://shellyourexperience.com/2011/09/ … oisactive/
I’m using Trevor Sullivan’s PowerEvents modules (which I discovered thanks to a colleague - yo Chris!). These are all excelent examples and have helped tremendously to get me going in the right direction. Yet, I’m not able to get my relatively simple example to display the event details as I would have expected it to.
According to the PowerEvents documentation, when using the CommandLineConsumer one should be able to obtain event details via the special WMI %targetinstance% variable (would prefer not to have to write VBScript as that seems like a step backwards). In my test I set up a blocking condition in SQL Server which I’d like to have the permanent WMI event get triggered such that I can capture output of sp_whoisactive to persist to tables in an Admin database. The link above by Junior Laerte was excellent in that it showed how to use sp_whoisactive to do this using a PowerShell console window… and I’ve been successful in getting that to work (with some minor changes). In that link Junior suggests (and I would prefer) to accomplish this via more permanent eventing (ie, using the PowerEvents filter/consumer/binding model) versus the not-so-permanent method of the PowerShell console.
However, before attempting to do the CommandLineConsumer to fire off PowerShell and capture sp_whoisactive (see last link above), I wanted to ensure that I can get to the point where in a dev/test environment I could set up a SQL Server blocking event to fire off the WMI permanent event that at least captures the details of the event. In other words, first get a simple test working to then move on to actually doing the sp_whoisactive output capture.
Steps taken:
•Created a simple PowerShell script to be executed by the event consumer named test.ps1 located at C:\scripts folder
[code2=powershell]"Made it to test.ps1 at $(get-date)"
$args
$args | gm[/code2]
•Successfully tested this from both PowerShell and DOS command prompt. NOTE: The %CD% will be replaced by %TargetInstance% WMI token once this works.
C:\windows\System32\WindowsPowerShell\v1.0\powershell.exe -command "C:\Scripts\Test.ps1"%CD%" >>C:\Scripts\Test.out"
Made it to test.ps1 () at 04/05/2013 15:08:20
Z:<br>
TypeName: System.String
Name MemberType Definition
---- ---------- ----------
Clone Method System.Object Clone()
CompareTo Method int CompareTo(System.Object value), int CompareTo(string strB)
Contains Method bool Contains(string value)
CopyTo Method System.Void CopyTo(int sourceIndex, char destination, int destinationIndex, int count)
EndsWith Method bool EndsWith(string value), bool EndsWith(string value, System.StringComparison comparisonType), bool EndsWith(string value, bool ignore...
Equals Method bool Equals(System.Object obj), bool Equals(string value), bool Equals(string value, System.StringComparison comparisonType)
GetEnumerator Method System.CharEnumerator GetEnumerator()
GetHashCode Method int GetHashCode()
GetType Method type GetType()
GetTypeCode Method System.TypeCode GetTypeCode()
IndexOf Method int IndexOf(char value), int IndexOf(char value, int startIndex), int IndexOf(char value, int startIndex, int count), int IndexOf(string ...
IndexOfAny Method int IndexOfAny(char anyOf), int IndexOfAny(char anyOf, int startIndex), int IndexOfAny(char anyOf, int startIndex, int count)
Insert Method string Insert(int startIndex, string value)
IsNormalized Method bool IsNormalized(), bool IsNormalized(System.Text.NormalizationForm normalizationForm)
LastIndexOf Method int LastIndexOf(char value), int LastIndexOf(char value, int startIndex), int LastIndexOf(char value, int startIndex, int count), int Las...
LastIndexOfAny Method int LastIndexOfAny(char anyOf), int LastIndexOfAny(char anyOf, int startIndex), int LastIndexOfAny(char anyOf, int startIndex, int ...
Normalize Method string Normalize(), string Normalize(System.Text.NormalizationForm normalizationForm)
PadLeft Method string PadLeft(int totalWidth), string PadLeft(int totalWidth, char paddingChar)
PadRight Method string PadRight(int totalWidth), string PadRight(int totalWidth, char paddingChar)
Remove Method string Remove(int startIndex, int count), string Remove(int startIndex)
Replace Method string Replace(char oldChar, char newChar), string Replace(string oldValue, string newValue)
Split Method string Split(Params char separator), string Split(char separator, int count), string Split(char separator, System.StringSplit...
StartsWith Method bool StartsWith(string value), bool StartsWith(string value, System.StringComparison comparisonType), bool StartsWith(string value, bool ...
Substring Method string Substring(int startIndex), string Substring(int startIndex, int length)
ToCharArray Method char ToCharArray(), char ToCharArray(int startIndex, int length)
ToLower Method string ToLower(), string ToLower(System.Globalization.CultureInfo culture)
ToLowerInvariant Method string ToLowerInvariant()
ToString Method string ToString(), string ToString(System.IFormatProvider provider)
ToUpper Method string ToUpper(), string ToUpper(System.Globalization.CultureInfo culture)
ToUpperInvariant Method string ToUpperInvariant()
Trim Method string Trim(Params char trimChars), string Trim()
TrimEnd Method string TrimEnd(Params char trimChars)
TrimStart Method string TrimStart(Params char trimChars)
Chars ParameterizedProperty char Chars(int index) {get;}
Length Property System.Int32 Length {get;}
•Created WMI event filter
[code2=powershell]ipmo powerevents
$DurationMilliSecs = 20000 #Duration of blocking in milliseconds; the WMI events gets kicked off at this interval
$WQLEventQuery = "select * from BLOCKED_PROCESS_REPORT where duration > $DurationMilliSecs"
#Set the filter based on the blocked processes query
$Filter = New-WmiEventFilter -Name "SQLSERVER: Blocked Processes Duration" -Query $WQLEventQuery[/code2]
•Created WMI event consumer and bound the filter to it
[code2=powershell]$Consumer = New-WmiEventConsumer -Name 'SQLServerblocking' -ConsumerType CommandLine<br>-ExecutablePath C:\windows\System32\WindowsPowerShell\v1.0\powershell.exe -CommandLineTemplate " -command"C:\Scripts\Test.ps1 %TargetInstance% >>C:\Scripts\test.out`""
#Bind filter to the consumer
New-WmiFilterToConsumerBinding -Filter $Filter -Consumer $Consumer –Verbose[/code2]
•Created simple blocking condition within SQL Server using the following two scripts (in separate SSMS windows of course)… (note: if you’re following this, it can be easily duplicated using whatever database/table you like)
[code2=sql]–Execute this in 1st window (this creates exclusive lock on table given that it's within a transaction)begin tran
update tbl set textdata = ''
from [dbo].[RIC1PDWSWSQL01_20120227] tbl
where textdata is null
–To stop testing… rollback
–rollback[/code2]
[code2=sql]–Execute this in 2nd window (this session will be blocked by the update statement within an open tran)
select * from [dbo].[RIC1PDWSWSQL01_20120227] tbl[/code2]
•Confirmed via wmieventhelper.exe that the permanent event was configured
•Confirmed blocking condition was triggering the WMI event via using wbemtest.exe tool
The test.ps1 script apparently never fires as I see nothing in WMIExplorer.exe nor an update to the test.out file. I’m not sure if there’s an error (don’t see any via any of the WMI exploration tools mentioned) and, again, I tested it to ensure there would be no issues. I feared that the issue may be the reference to the %TargetInstance% WMI token but that shouldn’t keep test.ps1 from running and at least outputting an error (or a null parameter passed).
Any help would be appreciated.
I pinged Junior on Twitter - he said he had some problems with these he wasn’t ever able to resolve but he’s roped in a couple other guys to have a look.by salsafreakpr at 2013-04-10 05:25:29
Thanks Don! I’ve immensely enjoyed learning from you and other MVPs and appreciate all your contributions to the PowerShell community… eg, PowerShell In Depth is always nearby.by laertejunior at 2013-04-10 18:59:09
Hi guys,by salsafreakpr at 2013-04-11 06:37:17
Yes, I had some problems exactly to your problem, trying to pass parameters to a. ps1 in a permanente event (even run the .ps1). As in the time was only for testing, I did not go deeper into the issue. I already pinged Trevor to see if he has any idea, but I think over the weekend I will be able to have more time and do some teste more deeper to check I find anything else.
Thanks
Thanks Junior (saludos hermano! and much thanks for all your contributions as well - I’ve learned a lot from you too!). Until this can be figured out, wanted to share the following with everyone as it’s an interim solution I’m using towards my stated goal of capturing blocking stats via sp_whoisactive to a persisted table. Adam’s wonderful proc has an option to place the output of calling the SP to a table via the @destination_table parameter (great forethought as one cannot do INSERT-EXEC given that it’ll trigger "cannot be nested" error 8164). The code below takes advantage of the parameter and should work for most so long as the destination admin table is in a database on the same instance (or, at least accessible to the same instance). Polling for blocking stats delay time is a settable parameter (@DelayTime) and you can set the schedule for the job however you like so configure according to your tastes for your particular environment.by laertejunior at 2013-04-21 07:08:21
[code2=sql]SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
–Guts of job that runs Mon-Fri 6am to 6pm to capture blocking stats during business peak hours
DECLARE
@StartTime DATETIME = GETDATE()
,@DT VARCHAR(30)
,@HoursToRun INT = 12 – Number of hours to run this script
,@blocked INT
,@blocker INT
,@waittime INT
,@ID INT
,@MSG VARCHAR(255)
,@DelayTime VARCHAR(10) = '00:00:15' – Delay time in-between captures
WHILE DATEDIFF(HOUR, @StartTime, GETDATE()) < @HoursToRun
BEGIN
SET @DT = CONVERT(VARCHAR,GETDATE(),121)
IF OBJECT_ID('tempdb…#tmpBlockedProcesses') IS NOT NULL
DROP TABLE #tmpBlockedProcesses;
SELECT
ID = IDENTITY(INT,1,1)
, spid
, blocked
, waittime = MAX(waittime)
INTO #tmpBlockedProcesses
FROM master…sysprocesses
WHERE blocked > 0
GROUP BY spid
, blocked
ORDER BY 3 DESC
SELECT @ID = 1
WHILE @ID IS NOT NULL
BEGIN
SELECT @blocker=spid, @blocked=blocked, @waittime=waittime
FROM #tmpBlockedProcesses WHERE ID = @ID
SET @MSG = 'Blocked Processes at %s - Iteration %d - Leadblocker: %d, Blocked: %d, Wait Time: %d'
RAISERROR(@MSG, 1, 0, @DT, @ID, @blocker, @blocked, @waittime) WITH NOWAIT
EXEC dbo.sp_whoisactive @blocked, @find_block_leaders=1, @get_plans=2, @get_full_inner_text=1, @destination_table='DBAdmin.dbo.tblBlockedProcessReport'
SELECT @ID = MIN(ID) FROM #tmpBlockedProcesses WHERE ID > @ID
END
WAITFOR DELAY @DelayTime
END[/code2]
Here’s the DDL for the admin table tblBlockedProcessReport:
[code2=sql]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblBlockedProcessReport]') AND type in (N'U'))
DROP TABLE [dbo].[tblBlockedProcessReport]
GO
CREATE TABLE [dbo].[tblBlockedProcessReport](
[bprid] [int] IDENTITY(1,1) NOT NULL,
[dd hh] [varchar](15) NULL,
[session_id] [varchar](30) NULL,
[sql_text] [xml] NULL,
[login_name] [sysname] NOT NULL,
[wait_info] [nvarchar](4000) NULL,
[CPU] [varchar](30) NULL,
[tempdb_allocations] [varchar](30) NULL,
[tempdb_current] [varchar](30) NULL,
[blocking_session_id] [varchar](30) NULL,
[blocking_session_count] [varchar](30) NULL,
[reads] [varchar](30) NULL,
[writes] [varchar](30) NULL,
[physical_reads] [varchar](30) NULL,
[query_plan] [xml] NULL,
[used_memory] [varchar](30) NULL,
[Status] [varchar](30) NULL,
[open_tran_count] [varchar](30) NULL,
[percent_complete] [varchar](30) NULL,
[host_name] [varchar](100) NULL,
[Database_name] [varchar](100) NULL,
[program_name] [varchar](500) NULL,
[start_time] [datetime] NULL,
[request_id] [varchar](30) NULL,
[collection_time] [datetime] NOT NULL,
CONSTRAINT [PK_BlockedProcessReport] PRIMARY KEY CLUSTERED (
[collection_time] ASC
,[bprid]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO[/code2]
I’ve tested this in a dev environment and will be deploying to production. I figure whether one polls via a WAITFOR DELAY/WHILE loop or a scheduled job or a WMI event poll… polling is still polling and not true event-based triggering… whatever gets the job done… so implement according to whichever seems cleaner to you. Hope this helps.
Thanks a LOT for the kind words man. I still not had time to check this out mode deeper, but your solution is nice. You may want to setup a SQL WMI alert and start a JOB with the T-SQL that you did.
In this post I show how to setup a SQL WMI alert -> https://www.simple-talk.com/sql/databas … ql-server/
Triggering PowerShell jobs with SQL WMI Alerts
You will just need to change
@wmi_namespace=N’\.\root\CIMV2’,
@wmi_query=N’Select * from __InstanceCreationEvent WITHIN 300 WHERE TargetInstance ISA ‘‘CIM_DataFile’’ AND TargetInstance.Drive=‘‘C:’‘and TargetInstance.path=’’\ftpdownload\‘’ and TargetInstance.Name = ‘‘c:\ftpdownload\FileImport.csv’’‘,
@job_id=N’990ef94a-a96d-41f2-809d-323c5e60d375’
to @wmi_namespace= N’\.\Root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER (or to your InstanceName)
and @wmi_query=N"select * from BLOCKED_PROCESS_REPORT where duration > 99"(change to the time you want)
and execute the job
