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

SQL Server – Reporting Services – Connecting Locally – Day 1

Background

Hardening security via applying SSL Certs on a couple of Reporting Services Hosts and wanting to test them on same host, but “No Go“.

 

TroubleShooting

Windows Event Viewer

Checked Windows Event Viewer

Security

Security – Headers

Image

Tabulate
  1. Event ID = 4625
    • Keywords :- Audit Failure
    • Source :- Microsoft Windows Security auditing
    • Event ID :- 4625
    • Task Category :- Logon

Security – Details

Image

 

Tabulate
  1. Event ID = 4625
    • Security ID :- NULL SID
    • Logon Type :- 3
      • Logon Type 3 is Network
    • Status :- 0xC000006D
    • Event ID :- 4625
    • Task Category :- Logon

Summary

Basically, we were prompted thrice to enter our username and password. But, unable to connect.

 

 

Internet Explorer

Checked to make that we are still unable to connect when we run in Administrator Mode.

Task Manager

To verify that IE is running in Administrator mode launched Task Manager and included the “Elevated” attribute.

Select Columns

Results

Image

Explanation

For each IE Session, we are seeing two processes.
Why two processes each time we start a new IE Session?

 

Remediation

Registry

Outline

There are a couple of options and those are:

  1. BackConnectionHostNames
  2. DisableLoopbackCheck

 

BackConnectionHostNames

Worknotes

Launch regedit and access the registry key “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0”.

Search for BackConnectionHostNames

Add all FQDN that the server’s resource will be self referred to.
Each entry should be entered in its own line.

  1. Type :- REG_MULTI_SZ
  2. Data :- ????

Images

Adding Entry

Entry Added

 

DisableLoopbackCheck

Worknotes

Launch regedit and access the registry key “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa“.

Search for DisableLookback.

Make sure it exists as:

  1. Type :- REG_DWORD
  2. Data :- 1

Image

Script

Script – BackConnectionHostNames


@echo off
@echo on

set "_registryBranch=HKLM\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0"
set "_registryItem=BackConnectionHostNames"
set "_registryDataType=REG_MULTI_SZ"

rem ****************************************************************************************
rem please change to match your domain name
rem ****************************************************************************************
set "_domainName=labdomain.org"

set "_registryValue=%COMPUTERNAME%.%_domainName%"

echo "Value - Current"
reg query %_registryBranch% /v %_registryItem%

reg add %_registryBranch% /v %_registryItem% /t %_registryDataType% /d %_registryValue% /f

echo "Value - New"
reg query %_registryBranch% /v %_registryItem%

Script – DisableLoopbackCheck


@echo off
rem set "_registryBranch=HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa"
set "_registryBranch=HKLM\SYSTEM\CurrentControlSet\Control\Lsa"
set "_registryItem=DisableLoopbackCheck"
set "_registryDataType=REG_DWORD"
set "_registryValue=1"

echo "Value - Current"
reg query %_registryBranch% /v %_registryItem%

reg add %_registryBranch% /v %_registryItem% /t %_registryDataType% /d %_registryValue% /f

echo "Value - New"
reg query %_registryBranch% /v %_registryItem%

Summary

This problem is nothing.  It has been in the OS since Windows 2003.

And, so I suppose it is not really a problem, I just wished it was surfaced differently; than having to type my password thrice and still can’t get in.

 

References

  1. Microsoft
    • Microsoft Support
      • You receive error 401.1 when you browse a Web site that uses Integrated Authentication and is hosted on IIS 5.1 or a later version
        Link
  2. Nik Patel
    • Disable the Loopback Check for Specific Host Names on all SharePoint Web and Application Servers
      Link
  3. Michael Hanes
    • Use BackConnectionHostNames instead of DisableLoopbackCheck in production
      Link
  4. Harber.net
    • DisableLoopbackCheck & SharePoint: What every admin and developer should know
      Link

BFGuard – Day 2

Background

In this post we actually start BFGuard and try to connect to the host from other workstations.

 

Target OS

Here are the Windows OS that we will use for this exercise:

  1. Server
    • Windows Server 2012 R2
  2. Client
    • Windows Server 2012 R2
    • Windows 7

What we saw

BF Guard

BF Guard – Application

Scenario – After 1 failed Login

BF Guard – Application – Statistics

Explanation
  1. ip
    • Count :- 1
    • Date :- 2016-06-15 15:34:13
      • Time in GMT, not local time

 

Scenario – After Numerous failed Logins

BF Guard – Application – Statistics

Explanation
  1. ip
    • Count :- 7
    • Date :- 2016-06-15 16:47:18
      • Time in GMT, not local time

 

BF Guard – Application – Log entrys

Explanation
  1. @ 2017-06-15 09:47:39
    • – Auto blocking IP: for: 54864000 minutes

 

BF Guard – Application – Blocked IP

Explanation
  1. IP Address
    • IP Address :-
    • From :- 2017-06-15 09:47:39
    • To      :- 2017-06-15 10:47:39
    • City    :- Blocked

 

OS – Windows

Windows Logs

Windows Logs – Security

Windows Logs – Security – Filter

Here we filter for “Audit Failure“.

Windows Logs – Security – Logs

And, here are the events captured.

 

Windows Logs – Security – Log – Detailed

Windows Logs – Security – Log – Detailed- Event ID = 4625
Image

 

Explanation
  1. Subject
    • Security SID :- NULL SID
      • Since account that we entered to login in under is not known to the targeted computer or Active Directory, we get “NULL SID
    • Logon ID :-  0x0
      • Again, unknown Logon ID
  2. Logon Type
    • Our Logon Type is 3
      • Logon Type = 3
        • Network
  3. Account for which Logon Failed
    • Security SID :- NULL SID
    • Account Name :- bobsmith
    • Account Domain :- LAB
  4. Failure Information
    • Failure Reason :- unknown username or password
    • Status :- 0xC00006D
    • Sub Status :- 0xC0000064
  5. Process Information
    • Caller Process ID :- 0x0
      • Remote Caller Process is not known
    • Caller Process Name :-
      • Remote Caller Process is not known
  6. Network Information
    • Workstation Name :- ASTSQL01
    • Source Network Address :-
      • Network Address not passed in
    • Source Port :-
      • Network Port not passed in
  7. Detailed Authentication Information
    • Logon Process :- NtlmSsp
    • Authentication Package :- NTLM
  8.  Summary
    • Log Name :- Security
    • Source :- Microsoft Windows security
    • Event ID:- 4625
    • Task Category :- Logon
    • Keywords :- Audit Failure
    • Computer :- Host attempted for logon

 

OS – Windows Firewall

Reviewed server’s Windows Firewall to see how it is configured for “Remote Desktop“.

 

Explanation

  1. Remote Desktop configured as:
    • Domain :- Yes
    • Home/Work :- Yes
    • Public :- No
    • Group Policy – Yes

 

Summary

Unfortunately, Windows was not able to capture the incoming IP Address.

BF Guard was thus unable to read the IP Address from the Windows Logs.

Because of this inability, it is not able to re-configure the local Windows Firewall and have it start blocking the Source IP Address.

 

BFGuard – Day 1

Background

Googled online to identify steps to take for securing MS Windows Terminal Services.

One of the tools mentioned is BFGuard.

BFGuard

What is BFGuard?

BFGuard stands for “Brute Force Guard”.

How does it work?

It principally monitors the local machine’s event log.  The relevant log file in this case is the “Security Log“.

Upon finding entries that indicate failed logins correlative data is gathered.  Obvious correlative data includes username and IP Address.

Once the configured maximum number of failed attempts are reached the specific IP Address is blacklisted.

Download

Please download the free tool from here.

Screen Shots Please

Log Entries

BFGuard_Free_LogEntries_20170615_0629AM

Blocked IP

BFGuard_Free_BlockedIP_20170615_0633PM

WhiteList

BFGuard_Free_WhiteList_20170615_0635AM

Statistics

BFGuard_Free_Statistics_20170615_0638AM

Functionalities

From the screen shots here are the functionalities offered:

  1. A listing of “Blocked IPs
  2. Ability to whitelist specific IP Addresses
  3. Statistics on each connected IP Address

 

Summary

Wanted to introduce the product.

In the days ahead we will revisit and update our post.

Windows – Event Viewer Parsing Through Log Parser Studio

Background

Need to parse MS Windows Event Logs.

One of the ways to do so is to use Log Parser Studio.

 

Event Viewer

Let us save the events unto the File System.

Outline

  1. Launch Event Viewer
  2. Select the Logs you want ( Application / System / Security )
  3. Right click on the Logs and from the drop down menu, choose “Save All Events As …
  4. Choose Folder And Filename
  5. The file is saved with an extension of “Event Files (*.evtx )

 

Images

Launch Save Event As

Choose Filename

 

Log Parser Studio

Outline

  1. Launch Log Parser Studio
  2. Choose Log Type: EVTLOG
  3. Enter Query
  4. Execute Query

 

Choose Log Type : EVTLOG

Sample Queries


/*  Find top 1000 warnings and errors in the Application Log 
    Levels: 1=Error, 2=Warning                                
*/
SELECT TOP 1000 
             TimeGenerated
           , ComputerName
           , EventCategoryName
           , EventTypeName
           , EventID
           , SourceName
           , Message
FROM 'C:\Temp\04_WindowsLogs_Applications_20170518_0403PM.evtx'
WHERE ( EventType = 1 OR EventType = 2 )
AND   (
               (SourceName like 'ASP%' )
            or (SourceName = '.NET Runtime' )
            or (SourceName = 'Application Error' )
      )
ORDER BY TimeGenerated DESC


Click Execute Button

Click on the Execute Button – The Read icon with the exclamation mark!

 

Sample Output

 

Export

Outline

  1. In Log Parser Studio, use menu File \ Export \ Output as .CSV
  2. In the “Choose Location to save CSV File” window, please specify folder and file name

 

Images

File \ Export \ “Output as .CSV”

 

Choose Location to save CSV File

Excel File

 

VSS – Error – “Volume Shadow Copy Service error: Unexpected error DeviceIoControl(\\?\fdc#generic_floppy_drive”

Background

Reviewing errors on a couple of our servers and consistently seeing errors sourced to VSS.

BTW, VSS is a Microsoft Application and the term VSS stands for Volume Shadow Copy.

Error

Error Image

unexpectederror-deviceiocontrol-genericfloppydrive-20170118-0636pm-brushedup

Error Message



Volume Shadow Copy Service error: Unexpected error DeviceIoControl(\\?\fdc#generic_floppy_drive#6&2cb9d9b7&0&0#{53f5630d-b6bf-11d0-94f2-00a0c91efb8b} - 0000000000000474,0x00560000,0000000000000000,0,0000003E699986F0,4096,[0]).  hr = 0x80070001, Incorrect function.
. 

Operation:
   Exposing Recovered Volumes
   Locating shadow-copy LUNs
   PostSnapshot Event
   Executing Asynchronous Operation

Context:
   Device: \\?\fdc#generic_floppy_drive#6&2cb9d9b7&0&0#{53f5630d-b6bf-11d0-94f2-00a0c91efb8b}
   Examining Detected Volume: Existing - \\?\fdc#generic_floppy_drive#6&2cb9d9b7&0&0#{53f5630d-b6bf-11d0-94f2-00a0c91efb8b}
   Execution Context: Provider
   Provider Name: VMware Snapshot Provider
   Provider Version: 1.0.0
   Provider ID: {564d7761-7265-2056-5353-2050726f7669}
   Current State: DoSnapshotSet


Error Explanation

The Event Source is VSS.  And, the Event ID is 12289.

And, the event description reads  “Volume Shadow Copy Service error: Unexpected error DeviceIoControl(\?\fdc#generic_floppy_drive …  hr = 0x80070001, Incorrect function“.

Later on the Device Id is identified again within the Context sub-region.  And, the Device ID is 53f5630d-b6bf-11d0-94f2-00a0c91efb8b.

 

Remediation

NetApp Community

dailyFresh

Link

Did you, by any change, create a VM with a floppy drive, installed Windows and then one day removed the floppy drive from the VM?

In that case disable the floppy drive in the Device Manager of Windows and see if the error goes away.

 

Steps

  1. Launch Control Panel \ Device Manager
  2. Select Floppy disk drives \ Floppy disk drive
    • Right click on your selection
    • From the drop-down menu, please select disable
 Device Manager – Original

devicemanager-floppydiskdrive-20170118-0641pm

 

 

 Device Manager – Disabling the device ….

disablefloppydiskdrive

 

 Device Manager – Disabled Device

devicemanager-floppydiskdrive-20170118-0642pm

 

Knowledge Based

Here is some of the KB available on the Net.

 

Device Id

Removable Drives

smallvoid.com

Link

From smallvoid.com here are the registry keys for the various types of removable storages.

Device Type Registry Key
 CD and DVD Drives  {53f56308-b6bf-11d0-94f2-00a0c91efb8b}
 Floppy Drives  {53f56311-b6bf-11d0-94f2-00a0c91efb8b}
 Removable Disks  {53f5630d-b6bf-11d0-94f2-00a0c91efb8b}
 Tape Drives  {53f5630b-b6bf-11d0-94f2-00a0c91efb8b}

 

 

Error

0x80070001

Error 0x80070001 often means that one is trying to access a device that does not exist.

Please refer to the following KB articles

  1. Windows backup or restore errors 0x80070001, 0x81000037, or 0x80070003
    Link

Giving Credit

Though DailyFresh will like to keep an Air of Anonymity, he still gets credit.