Review Services Start and Stop times via Windows Event Log And Powershell

Background

Weeks ago, one of our critical applications experienced a down time because SQL Server did not auto-start post application of Windows Patches.

Over the weekend, new patches were applied and unfortunately I am just now reviewing the SQL Instances to make sure everything is good this time.

Better late than never is a silly excuse.

But, I really do have to close this Service Now ticket that requests the DBA validation; we have already exceeded our SLA.

Issue

MS Windows Event Log is not so flexible for text searches.  I am able to filter on specific categories, but forget about it when it comes to looking for just specific services start and down times.

It is easy enough to do if I issue Transact SQL Queries or just looking at datetime stamps of sql server errorlog files.

But, I wanted an excuse to script services start and stop times

I really looked old and sluggish and it took a lot longer than it should.

But, here is what we have thus far.

 

Events

Image

Here are images of the data

Detail

Detail – XML

Explanation

  1. EventData
    • EventData – param1
      • SQL Server (MSSQLServer)
    • EventData – param2
      • running

Code



# define parameters
$CHAR_WILDCARD="*";

# log system to target
$logName="System"

# provider name
$providerName="Service Control Manager"

#event ID
$eventID=7036

# only return entries that have sql in their name
$message='SQL';

# use wilcard *message*
$messageWildcard=[string]::Concat($CHAR_WILDCARD, $message, $CHAR_WILDCARD);

# gete events use wilcard *message*
$events = Get-WinEvent -FilterHashtable @{LogName=$logName; ProviderName=$providerName; ID=$eventID} | 
			where {$_.Message -like $messageWildcard} |
			Sort-Object -Property TimeCreated -Descending 

#alternate filtering mechanism
$eventSQL = $events | where {$_.Message -like $messageWildcard};

#if data returned
if ($eventSQL) {

	#Add Service Name to object property list
	$eventSQL | Add-Member -Name 'serviceName' -Type NoteProperty -Value "";
	
	#Add Service Status to object property list	
	$eventSQL | Add-Member -Name 'serviceStatus' -Type NoteProperty -Value "";
				

	# Parse out the event message data            
	ForEach ($eventObj in $eventSQL) {  
	
		# Convert the event to XML            
		$eventObjXML = [xml]$eventObj.ToXml()            
		
		# Iterate through each one of the event Data fields
		# get handle to event // eventData // Data	
		For ($i=0; $i -lt $eventObjXML.Event.EventData.Data.Count; $i++) {            
		
			# get handle to event // eventData // Data // Name	
			$itemName = $eventObjXML.Event.EventData.Data[$i].name;
			
			# get handle to event // eventData // Data // Text
			$itemValue = $eventObjXML.Event.EventData.Data[$i].'#text';
			
			# if attribute name is param1, we are fetching the service name
			if ($itemName -eq "param1")
			{
				$eventObj.serviceName = $itemValue;
			}
			
			# if attribute name is param2, we are fetching the service state
			elseif ($itemName -eq "param2")
			{
				$eventObj.serviceStatus = $itemValue;
			}					
			
			
		} ## for event data
		
		
	} ## move to next event object           
	
	
	## display data
	$eventSQL | Select-Object TimeCreated, Message, serviceName, serviceStatus | Format-List


	
}


Concession

Nothing original here as can be deduced by the many people listed in the References section.

Having acknowledged so, it still amazes how many people put in so much work and happily package it in such a way that someone else can feel comfortable following behind.

For some of us it takes a lot longer, yet we know we know we will get there someday; as there is proof others did.

 

Source Code Control

GitHub

Availed on GitHub here.

References

  1. Microsoft | Technet
    1. Ashley McGlone
      • PowerShell Get-WinEvent XML Madness: Getting details from event logs
        Link
  2. Microsoft | Developer Network
    • JuanPablo Jofre
  3. Microsoft | Technet
    • The Scripting Guys
      • Use FilterHashTable to Filter Event Log with PowerShell
        Link
  4. PoshCode: PowerShell Code Repository
    • Cameron Wilson
      • Get-LatestEventFromAllLogs
        Link
  5. Mike F Robbins
    • PowerShell: Filter by User when Querying the Security Event Log with Get-WinEvent and the FilterHashTable Parameter
      Link
  6. Josh Ancel
    • Filter Event Log on Message string using PowerShell
      Link
  7. Colleen Morrow
    • Parsing Windows event logs with PowerShell
      Link
  8.  Netwrix
    • Russell Smith
      • Advanced Event Log Filtering Using PowerShell
        Link
  9. Techrepublic
    • Greg Shultz
      • How to extend your event log search capabilities with PowerShell’s Get-EventLog cmdlet
        Link
  10. 4SysOps
    • Luc Fullenwarth
      Link
  11. Rakhesh.com
    • Using Get-WinEvent to look at Windows Event Log
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

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