Monitoring SQL Server Service with WMI Events and ShowUI


Ok. if you have not heard of ShowUI, certainly you are outdated about cool stuffs in PowerShell.

“ShowUI is a PowerShell module to help build WPF user interfaces in script. ShowUI makes the complicated world of WPF easy to use in PowerShell. You can use ShowUI to write simple WPF gadgets, quick front ends for your scripts, components, and full applications. “

I started playing with ShowUI because I wanted a GUI for displaying some monitoring. Then with the help of Jaykul and Doug Finke (big names behind the creation of ShowUI) I could get it. let’s see?

First we build the WQL query to monitor the stop and start events in the SQL Server service.

Stop Service :

$query = "select * from __InstanceModificationEvent within 5 where TargetInstance ISA 'Win32_Service' and TargetInstance.Name='MSSQLSERVER' and TargetInstance.State='Stopped'"

Start Service :

$query = "select * from __InstanceModificationEvent within 5 where TargetInstance ISA 'Win32_Service' and TargetInstance.Name='MSSQLSERVER' and TargetInstance.State='Running'"

Why  am I doing two queries if I can join the status in a “and” operator ?

$query = "select * from __InstanceModificationEvent within 5 where TargetInstance ISA 'Win32_Service' and TargetInstance.Name='MSSQLSERVER' and (TargetInstance.State='Stopped' or TargetInstance.State='Running')"

(For more info take a look at my S-T article Using PowerShell and WMI Events Queries for Powerful Notifications)

Exactly, this way I can get the value of the variable $event and  do not have to use two queries (and in consequence two events as discussed below), just get using  $event.SourceEventArgs.NewEvent.TargetInstance.State(PowerShell creates an automatic variable called $event and stores the last event received in That variable.)

But for some reason, I’m not getting the return value of this variable in the Update-WPFJob. I will send this issue to the Jedi Jaykul and Doug l, but for now let’s play this way

Ok , with these querys, lets create a listview :

New-ListView -Name MonitorSQLService -ItemsSource {

   

   New-Object System.Collections.ObjectModel.ObservableCollection[PSObject](,([PSObject[]](&{

      New-Object PSObject -Prop @{ Name="SQL Server";          State=(Get-Service "MSSQLSERVER").status}

      New-Object PSObject -Prop @{ Name="SQL Server Agent";    State=(Get-Service "MSSQLSERVER").status}

   })))

} -View {

   New-GridView -Columns {

      New-GridViewColumn -Header Name -DisplayMember { Binding Name }

      New-GridViewColumn -Header State -DisplayMember { Binding State }

 

   }

} -AsJob

As you can see, the State of the Services is returned using get-service. For now lets play just with the SQL Server service. (at this point I want to Thanks Boe Prox (@proxb) with the get-service tip Alegre)

And a ListView is displayed :

image

Now the code to Register WMI event to Stop the Service

Register-WMIEvent -Query $query  -Action {

    Get-Job MonitorSQLService | Update-WPFJob {

        $Window.Content.ItemsSource | Where { $_.Name -eq "SQL Server" } | % {

            $_.State = "Stopped"

        } 

    

        [System.Windows.Data.CollectionViewSource]::GetDefaultView( $Window.Content.ItemsSource ).Refresh();

    }

    

    Get-Job MonitorSQLService | Update-WPFJob {

        foreach($item in $Window.Content.ItemsSource) { 

            if($item.State -eq "Stopped") {

                $ViewItem = $Window.Content.ItemContainerGenerator.ContainerFromItem( $item )

                $ViewItem.Background = "Pink"

            } 

          

       }

    }

 

}

The first Update-WPFJob switch the value when the event is triggered and the second Update-WPF change tha background color to RED.

The The code when Start the service :

$query = "select * from __InstanceModificationEvent within 5 where TargetInstance ISA 'Win32_Service' and TargetInstance.Name='MSSQLSERVER' and TargetInstance.State='Running'"

 

Register-WMIEvent -Query $query  -Action {

    Get-Job MonitorSQLService | Update-WPFJob {

        $Window.Content.ItemsSource | Where { $_.Name -eq "SQL Server" } | % {

            $_.State = "Running"

        } 

    

        [System.Windows.Data.CollectionViewSource]::GetDefaultView( $Window.Content.ItemsSource ).Refresh();

    }

    

    Get-Job MonitorSQLService | Update-WPFJob {

        foreach($item in $Window.Content.ItemsSource) { 

            if($item.State -eq "Running") {

                $ViewItem = $Window.Content.ItemContainerGenerator.ContainerFromItem( $item )

                $ViewItem.Background = "White"

            } 

          

       }

    }

 

}

Now it is just stop and start the service and watch the magic Alegre

Download

I did a small video to show this :

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s