Mining Application Logs – 2015/Feb

Background

A few days a 3rd party web Application that I am working on started getting this error message:

PleaseSelectACity

 

Diagnostic Steps

Vendor

Spoke to the onsite Vendor Support person and he blamed it on customization we are doing on our side.

And, so again, we are on our own.

As the Application is Microsoft; .Net, Classic ASP, C#, and Classic ASP, I am on safe grounds.

Launched Command Shell

Launched Command Shell and changed folder to application’s Inetpub folder.

Find Text

Tool

While one can use GUI tools to search, I personally find the built-in Windows Search tool hard to navigate.  And, so we will use findstr for our exercise.

For our current needs, here are the relevant parameters:

Argument Description  Usage
 /m Prints only the file name if a file contains a match. This allows us to print out matches and not worry about their contextual usage in corresponding file
 /p Skips files with non-printable characters. In our case, we have exes and dlls.  These are binary files and their contents is not available via normal textual parsing means nor editors.And, so for now we will skip them.
 /s Searches for matching files in the current directory and all sub-directories. Delve into sub-folders \ sub-directories, as well.
 /c Uses specified text as a literal search string. Findstr supports both literal and regular expression (RE) searches.In our case, we will edge towards literal as it is a bit simpler and we will avoid the relative complexity of RE; especially as we do not need it for our current simple need.

 

 

 

Let us use findstr to look for the text.

   findstr /p /s /c:"Please select a city"

 

Output

Textual:


   _translate\english.inc:t5078 = "Please select a country"
   _translate\englishus.inc:t5078 = "Please select a country"

 

pleaseSelectACountry

 

Match ID

From the matches above, we appear to be dealing with an App that has a bit of Internationalization.  That is, it supports various languages through IDs and “culture” specific resource files.

This time, I do not want to print out the specific matching lines, just the actual file names.

   findstr /M /p /s /c:"t5078" *.* | more

 

Output

Textual:


   GridCache\js\jQuery.VC.js
   GridCache\js\jQuery.VC.min.js
   FuncLinks\metaAdmin.asp
   _translate\dutch.inc
   _translate\english.inc
   _translate\englishus.inc
   _translate\french.inc
   _translate\german.inc
   _translate\hungarian.inc
   _translate\indonesian.inc
   _translate\italian.inc
   _translate\norwegian.inc
   _translate\portuguese.inc
   _translate\portuguesebr.inc
   _translate\spanish.inc
   _translate\swedish.inc

 

Review HTML\jQuery File

In the GridCache\js\jQuery.VC.js file, we find matches for t5078 and t5079.

 


     var cz = 0;
	e.find("#selCountry option:selected").each(function() {
		if (cz == 0) {
			cz = b(this).attr("value")
		}

	if (cz <= 0) {
		alert(getTextValue("t5078"));
		e.find("#selCountry").focus();
		return
	}

	var y = 0;
	e.find("#selRegion option:selected").each(function() {
		if (y == 0) {
			y = b(this).attr("value")
		}
	});
	if (y <= 0) {
		alert(getTextValue("t5079"));
		e.find("#selRegion").focus();
		return
	}

 

 

Review ASP File

Next was to review some of the Server-side ASP files:

                     

  <strong><%= t1 %>:</strong>
  <span id="summary_location">
    <% if isArray(arr_locationDetails) and int_serviceOnly = 0 then response.write arr_locationDetails(1,0) end if %>
  </span>

 

Invoke Stored Procedure

Let us invoked our Stored Procedure and pass in the arguments we are most interested in.

  

  set oRS_getForm = server.createObject("ADODB.recordset")
  oRS_getForm.CursorLocation = 3


  on error resume next

  oRS_getForm.open "{call dbo.sp_displayForm ("&userDefinedLocation&", "&int_roomID&","&Session("LanguageID")&", "&Session("UserID")&", "&userDefinedBusinessUnit&", "&Session("countryID")&", "&int_bookingID&", '"&userDefinedFloor&"', "&session("filterGrid")&", "&int_managedBooking&", "& int_editSeries&", '"& dt_currentDateAndTime &"', "&session("outlook")&", "&int_serviceOnly&", "&int_deliveryPoint&")}", oConn

  set colErrs = oConn.Errors

  if oConn.Errors.Count <> 0 then 

    response.Write t1499
    For Each objError In colErrs 
        call logErrorData(objError.Number, objError.Description, "Form", "sp_displayForm")
    Next
    oConn.Errors.Clear
    oConn.close
    set oConn = nothing
    response.end

 end if

 

Retrieve Database Record Set

In the code below, we can see that on the third getRows we bring back the recordset that we are most interested in (arr_locationDetails).

                     

 if oRS_getForm.recordcount <> 0 then arr_userDetails = oRS_getForm.getRows() end if
 set oRS_getForm = oRS_getForm.nextRecordset()

 if oRS_getForm.recordcount <> 0 then arr_siteSetup = oRS_getForm.getRows() end if
 set oRS_getForm = oRS_getForm.nextRecordset()

 if oRS_getForm.recordcount <> 0 then arr_locationDetails = oRS_getForm.getRows() end if
 set oRS_getForm = oRS_getForm.nextRecordset()

 if oRS_getForm.recordcount <> 0 then arr_resourceNamingDetails = oRS_getForm.getRows() end if
 set oRS_getForm = oRS_getForm.nextRecordset()

 if oRS_getForm.recordcount <> 0 then arr_colours = oRS_getForm.getRows() end if
 set oRS_getForm = oRS_getForm.nextRecordset()

 if oRS_getForm.recordcount <> 0 then arr_messages = oRS_getForm.getRows() end if

 

Stored Procedure Code

Get Location & Business Unit


   SELECT 
           l.pkLocationID, l.LocationName, l.fkCountryID, isNull(l.att, 0), isNull(CostCentreMandatory, 1) AS CostCentreMandatory    
         , l.fkRegionID as regionID ,l.CCPaymentCostCodeEnabled CCPaymentCostCodeEnabled 
  FROM dbo.tblLocationBusinessUnit lbu     
        INNER JOIN dbo.tblLocation l 
         ON l.pkLocationID = lbu.fkLocationID    
  WHERE fkBusinessUnitID = @businessUnit    
  

 

Database Error Log

We went through some more files and finally reviewed the Application’s Error Log Tables.

 


set nocount on
go

set dateformat dmy
go

declare @dateCutoff datetime
declare @VENDOR_TAG varchar(60)

set @VENDOR_TAG = 'BR'
set @dateCutoff = '19/3/2015'

declare @errorListIgnore TABLE
(
    [errorDescription] varchar(60)
)

declare @errorListInclude TABLE
(
    [errorDescription] varchar(60)
)

insert into @errorListIgnore
([errorDescription])
values
      ('Invalid')
    , ('GridCache:InsertCache')
    , ('Cache removed reason: Removed')
    , ('found file')
    , ('Host')

insert into @errorListInclude
([errorDescription])
values
      ('Sequence contains no elements')

select top 1500
          'using ignore list' as 
        , replace(tblCE.errorNumber, @VENDOR_TAG, '') as [errorNumber]
        , errorDescription
        , replace(tblCE.[filename], @VENDOR_TAG, '') as [filename]
        , errorDescription
        , storedProcedure
        , replace(tblCE.datetime, @VENDOR_TAG, '') as [datetime]
from   dbo.tblBRError tblCE
where  errorDescription not in ( select tblL.[errorDescription] from @errorListIgnore tblL)
and    [datetime] < @dateCutoff
order by pkErrorID desc

select top 1500
          'using include list' as 
        , replace(tblCE.errorNumber, @VENDOR_TAG, '') as [errorNumber]
        , errorDescription
        , replace(tblCE.[filename], @VENDOR_TAG, '') as [filename]
        , errorDescription
        , storedProcedure
        , replace(tblCE.datetime, @VENDOR_TAG, '') as [datetime]
from   dbo.tblBRError tblCE
where  errorDescription in ( select tblL.[errorDescription] from @errorListInclude tblL)
and    [datetime] < @dateCutoff
order by pkErrorID desc

 

Thank goodness for Application’s Logging, we found entries such as:

 

Textual:

Error Entries
   at RNM..DAL.User.UserDAL.GetUserdefaultSettingsDB(Int32 userID)
at RNM..DAL.User.UserDAL.GetUserDefaultSettingsByID(Int32 userID)
at RNM..BLL.User.UserBLL.GetUserDefaultSettingsByID(Int32 userID)
  at RNM..Utilities.ExceptionManager.HandleException(Exception hExp, String dataToLog, Boolean throwException)
at RNM..BLL.User.UserBLL.GetUserDefaultSettingsByID(Int32 userID)
at RNM..BLL.User.UserBLL.GetUserDefaultSettingsByResourceID(Int32 userID, EnumResource resource)
 Timezone name not found

 

 

 Image:

errorTable

 

Fixes

There are a couple of Database support tables that are pertinent to us.

  • dbo.tblBusinessUnit
  • dbo.tblLocationBusinessUnit
  • dbo.tblLocation
  • dbo.tblUserdefaultView

Summary

I have intentionally obfuscated error messages and simplified our problem solving passageway for several reasons:

  • The Application is only used by a few companies
  • The Vendor does not have a broad support web site
  • I am too old to have the Vendor close some of our tool-sets and have to rely on them ever more; or worse still resort to more rudimentary techniques

 

Listening

It has been a taxing week.  And, so I have to listen and share

Patrick Anthony – No Pretense (Official Music Video)
https://www.youtube.com/watch?v=7g4_6Y_gCqw

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