IIS Logs / Log Parser Studio – Aggregated Hits per Server

Background

Our monitoring team has developed and rolled out scripts for monitoring our web farm.

And, we are getting alerts through email.

Quite a lot of emails are coming across and wanted to see if they are coming from same host or a combination of hosts.

 

Emails

Looked at the emails and they happen to be coming from same host.

And, so will have to engage our Network team and see how the Load Balancer is configured.

Is there a prospect that more traffic is being directed at the failing node?

Network Load Balancer

As we prepared to go to the Network Load Balancer team took the opportunity to take gather and query the IIS Logs, as well.

 

TroubleShooting

Log Parser Studio

Query


SELECT 
            To_String(date, 'yyyy-MM-dd') as dated

          , sc-status as status

          , sum (
                    case s-ip
                        when '10.0.4.25' then 1
                        else 0
                   end
               ) as S1

          , sum (
                    case s-ip
                        when '10.0.4.26' then 1
                        else 0
                   end
               ) as S2


          , sum (
                    case s-ip
                        when '10.0.4.27' then 1
                        else 0
                   end
               ) as S3

         , sum (
                    case s-ip
                        when '10.0.4.28' then 1
                        else 0
                   end
               ) as S4

          , min(TO_TIMESTAMP(date, time)) as tsRecordedMin


          , max(TO_TIMESTAMP(date, time)) as tsRecordedMax


FROM '[LOGFILEPATH]' 


where   (


           (

             TO_TIMESTAMP(date, time) 
                     between timestamp('2017/08/02 10:30:00', 'yyyy/MM/dd hh:mm:ss')  
                          and timestamp('2017/08/02 17:20:00', 'yyyy/MM/dd hh:mm:ss')
           )

       )

/*

	and  c-ip not in ('10.0.4.141')
	
*/

group by
         date
       , sc-status


order by
           dated 
         , status



Output

Time Range – 1 ( August 2nd 10:30 AM – 5:20 PM )

Results

Explanation
  1. It is difficult to make case that traffic is exhaustively being waded into a specific host

Time Range – 2 ( August 8th 5:13 PM – 8:40 PM )

Results

Explanation
  1. In our second time slot, 4700 records bearing HTTP 200 is right around average

Summary

At this time it is likely that the sufferance we are seeing with this specific host is not due to outside pressure, but internal to the host itself.

 

IIS – Review IISLog to track traffic within time period

Issue

We have been receiving a bunch of alerts from our monitoring tool.

Came through email, but as a loud mouth I asked the monitoring group to please send us  a tabulated summary.

 

Alert Report

Image

Explanation

  1. Again, I am like what happened for 2 hours on a specific web server.
    • On the second data row
      • How did we stay gone from 6:20 and 8:20 AM

TroubleShooting

Setup

Collected IIS Logs and trained Log Parser Studio against them.

 

Query


/*  New Query  */

SELECT TOP 10000 
            TO_TIMESTAMP(date, time) as ts
          , c-ip as ipAddress
          , cs-username as username
          , cs-uri-stem as URL
          , cs-uri-query as query
          , sc-status as status
          , time-taken as timeTaken
          , cs(User-Agent) as userAgent
          , cs(Referer) as referer

FROM '[LOGFILEPATH]'

where  TO_TIMESTAMP(date, time)
             between timestamp('2017/07/30 06:00:00', 'yyyy/MM/dd hh:mm:ss')  
             and timestamp('2017/07/30 12:00:00', 'yyyy/MM/dd hh:mm:ss')


Output

Explanation

  1. On 2017-June-30th between 6 AM and 6:13 AM, we recorded HTTP requests which came in twos
    • The first request was targeted to the home page
      • IIS returned 302
        • Redirection
    • The second request is to the /Account/LogOn page
      • Returned 200
        • 200 is OK
  2. We did not get another request till 8:18 AM
    • Again two HTTP requests
      • The first was 302
        • Re-direct
      • The redirection lead as to /Account/Logon
        • Returned 200
        • But, took a lot longer 18156 ms or 18 seconds
          • Need to come back upon validating actual measurement
  3. Things returned back to normal
    • 8:28 AM, 8:33 AM, 8:38 AM, 8:43 AM, 8:48 AM, 8:53 AM, 8:58 AM, 9:03 AM, 9:08 AM, and 9:13 AM

Summary

Traced the error back to the monitoring account being locked out during our blind two hour period.

Windows – Reviewing Services Start and Stop Times via Event Viewer & Log Parser Studio

Background

Received an Incident this morning stating that out Power BI Gateway Service was done.

Spent a bit of time trying to see what brought the service down.

Code


/*  
   Service Start and Stop Time
*/
SELECT 
             TimeGenerated
           , TO_STRING(TimeGenerated, 'yyyy-MMM-dd hh:mm tt') as [Timestamp] 
           , Message as [MessageLogged]
           , Strings as [StringsRaw] 
           , SUBSTR(Strings, 0, LAST_INDEX_OF(Strings,'|'))  as [ServiceName] 
           , SUBSTR(Strings, ADD(LAST_INDEX_OF(Strings,'|'), 1) , Strlen(Strings)) as [ServiceStatus] 

FROM '[LOGFILEPATH]'

where
     (
          ( EventID = 7036 )
          and 
          (
                 ( Message like '%SQL%' )
              or ( Message like '%gateway%' )


          )
  
     )

ORDER BY
      TimeGenerated desc


Output

Internet Information Server (IIS) – Application Pool – Tracking – Day 2

Background

This is the second post on our series on tracking the status of IIS’s Application Pool.

Lineage

Here is our initial post:

  1. Internet Information Server (IIS) – Application Pool – Tracking
    Link

 

TroubleShooting

Event Viewer

Log Parser Studio

Queries

Query – Get All WAS Entries
Query
SELECT TOP 1000

         TO_STRING(TimeGenerated, 'yyyy-MM-dd HH:mm:ss') as TimeGenerated
       , ComputerName
       , EventCategoryName
       , EventTypeName
       , EventID
       , SourceName
       , Message as Message
  
from  '[LOGFILEPATH]'

where ( SourceName = 'WAS' ) 
 
ORDER BY
           ComputerName
         , TO_STRING(TimeGenerated, 'yyyy-MM-dd HH:mm:ss') DESC

Output

 

Query – Get WAS Entries – Application Pool Disabled
Query


SELECT TOP 1000

         TO_STRING(TimeGenerated, 'yyyy-MM-dd HH:mm:ss') as TimeGenerated
       , ComputerName
       , EventCategoryName
       , EventTypeName
       , EventID
       , SourceName
       , Message as Message
  
from  '[LOGFILEPATH]'

where ( SourceName = 'WAS' ) 
 
and ( Message like '%disable%' ) 

ORDER BY
           ComputerName
         , TO_STRING(TimeGenerated, 'yyyy-MM-dd HH:mm:ss') DESC
Output

 

Summary

There are a few entries bearing the Source WAS in Windows System Event Viewer.
Inclusive are :

  1. A process serving application pool ‘DefaultAppPool’ failed to respond to a ping. The process id was ‘6208’.
  2. A process serving application pool ‘DefaultAppPool’ suffered a fatal communication error with the Windows Process Activation Service. The process id was ‘13844’. The data field contains the error number.
  3. A worker process with process id of ‘21412’ serving application pool ‘DefaultAppPool’ has requested a recycle because the worker process reached its allowed processing time limit.
  4. Application pool ‘DefaultAppPool’ is being automatically disabled due to a series of failures in the process(es) serving that application pool.

 

The ones most pernicious is “Application pool ‘DefaultAppPool’ is being automatically disabled due to a series of failures in the process(es) serving that application pool. “

Internet Information (IIS) / Log Parser – Queries – String Pattern Matching

Background

Looking for File I/O Exceptions in the Event Viewer.

 

Query

Sample

Sample 001

Code


SELECT TOP 100 
 
         TimeGenerated
       , ComputerName
       , EventCategoryName
       , EventTypeName
       , EventID
       , SourceName
       , Message as Mesg
       , Strings as Strings
       , EXTRACT_TOKEN(Strings,1,'|') AS AppName
       , EXTRACT_TOKEN(Strings,2,'|') AS AppVersion
       , EXTRACT_TOKEN(Strings,3,'|') AS S3
       , EXTRACT_TOKEN(Strings,4,'|') AS Module
       , INDEX_OF(Message, 'System.IO.IOException') as indexOf
       , case INDEX_OF(Message, 'System.IO.IOException') 
            when 0 then 'N'
            when NULL then 'N'
            else 'Y'
         end as IOE
       , CASE strcnt(Message, 'System.IO.IOException')
             when 0 then 'No'
             else 'Yes'   
         end as IOException
 
from  '[LOGFILEPATH]'
 
WHERE ( EventType = 1 OR EventType = 2 )

and    INDEX_OF(Message, 'System.IO.IOException') > 0

 
ORDER BY
         TimeGenerated DESC


Output

 

Explanation

  1. INDEX_OF
    • We use INDEX_OF to find the position of the sought string in the Message column
      • When the column contains System.IO.IOException the query returns the starting position of the found pattern
      • When not found, null is returned
  2. STRCNT
    • We invoke STRCNT to count number of matches
      • When String not found, 0 return
      • When matched, number of matches

 

References

  1. StackOverflow
    • Log Parser Case Statement
      Link

 

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

 

Microsoft – Log Parser Studio – Will not start

Background

Last week I used Microsoft’s Log Parser Studio to review some IIS Logs, but in the middle of my analysis the Application aborted on me.

I tried restarting the Application a few times, but that proved fruitless.

 

Thankfully

Thankfully it is now Saturday afternoon and we received MS Security updates early Saturday morning.  And, so I was hoping that the reboot that occurs after security patches would have saved me.

But, still no help.

Error Messages

The system itself does not keep an actually log file, but uses MS Windows Event Viewer.

Here is what is logged.

MS Windows Event Viewer

The process was terminated due to an unhandled exception.


Application: LPS.exe
Framework Version: v4.0.30319
Description: The process was terminated due to an unhandled exception.
Exception Info: System.Configuration.ConfigurationErrorsException
   at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean)
   at System.Configuration.BaseConfigurationRecord.ThrowIfParseErrors(System.Configuration.ConfigurationSchemaErrors)
   at System.Configuration.BaseConfigurationRecord.ThrowIfInitErrors()
   at System.Configuration.ClientConfigurationSystem.OnConfigRemoved(System.Object, System.Configuration.Internal.InternalConfigEventArgs)

Exception Info: System.Configuration.ConfigurationErrorsException
   at System.Configuration.ClientConfigurationSystem.OnConfigRemoved(System.Object, System.Configuration.Internal.InternalConfigEventArgs)
   at System.Configuration.Internal.InternalConfigRoot.OnConfigRemoved(System.Configuration.Internal.InternalConfigEventArgs)
   at System.Configuration.Internal.InternalConfigRoot.RemoveConfigImpl(System.String, System.Configuration.BaseConfigurationRecord)
   at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(System.String, Boolean, Boolean, Boolean, Boolean, System.Object ByRef, System.Object ByRef)
   at System.Configuration.BaseConfigurationRecord.GetSection(System.String)
   at System.Configuration.ClientConfigurationSystem.System.Configuration.Internal.IInternalConfigSystem.GetSection(System.String)
   at System.Configuration.ConfigurationManager.GetSection(System.String)
   at System.Configuration.ClientSettingsStore.ReadSettings(System.String, Boolean)
   at System.Configuration.LocalFileSettingsProvider.GetPropertyValues(System.Configuration.SettingsContext, System.Configuration.SettingsPropertyCollection)
   at System.Configuration.SettingsBase.GetPropertiesFromProvider(System.Configuration.SettingsProvider)
   at System.Configuration.SettingsBase.GetPropertyValueByName(System.String)
   at System.Configuration.SettingsBase.get_Item(System.String)
   at System.Configuration.ApplicationSettingsBase.GetPropertyValue(System.String)
   at System.Configuration.ApplicationSettingsBase.get_Item(System.String)
   at ExLPT.Properties.Settings.get_LastLogFolder()
   at ExLPT.MainForm..ctor()
   at ExLPT.Program.Main()


 

Remediation

There are a couple of remediation choices:

  1. Copy everything in the Application’s folder to a new folder and run from the new folder
  2. Run from same folder after renaming the application’s configuration file ( LPSV2Library.XML)

 

FileList