Powershell & Extended Events

I have been doing a lot of work with SQL Server lately. Over on www.sqlskills.com Jonathan Kehayias has written a series of very good blog posts regarding working with Extended Events. I could not however find very much information around Powershell and Extended Events. After some searching on MSDN and TechNet I was able to piece together how to make a stand alone script to create an extended event session. This link was invaluable: https://technet.microsoft.com/en-us/library/ff877887.aspx

 

#$Instance = "SQLALL\NOGISQL2012"
$InstServiceName = "NOGISQL2012"
#$NodeName = "SQLALL"

Function CreateXEventSession()
{
    # PATH  SQLSERVER:\XEvent\SQLALL\NOGISQL2012
   
    Set-Location SQLSERVER:\XEvent
    $h = hostname
    cd $h
    $store = dir | where {$_.DisplayName -ieq $InstServiceName}
    $session = new-object Microsoft.SqlServer.Management.XEvent.Session -argumentlist $store, "TestSession"
    $event = $session.AddEvent("sqlserver.sql_statement_completed")
    $event.AddAction("sqlserver.sql_text")
    $session.AddTarget("package0.ring_buffer")
    $session.Create()
}

write-host "--<>------  start  ------<>--"
Import-Module -Name SQLPS
CreateXEventSession
write-host "--<>------  end  ------<>--"
# todo --> start the session

 

good luck and have fun,

Norman