' *********************************************************************************
' Last updated: 2014-04-24
' This script uses the Log DSC to query log files
' It also runs some standard queries
' The standard queries depend on if the logs are Web or Interview tier logs
' It is expected that the LOG_FILES_FOLDER is the top level of a directory structure similar to the following
' LOG_FILES_FOLDER
' --- Engine machine 1
' --- Engine machine 2
' --- Engine machine 3
#define DELIMITER ","
' Specify the tier type as Web or Interview
#define TIER_TYPE "App"
' Specify the folder for the logs
#define LOG_FILES_FOLDER "C:\Logs\App"
' *********************************************************************************
' Open the logs using the Log DSC
' Specify SearchSubFolders=True to load logs from all sub folders
Dim ConnectionString, adoConnection
ConnectionString = "Provider=mrOleDB.Provider.2;" + _
"Initial Catalog=" + LOG_FILES_FOLDER + ";" + _
"MR Init MDSC=mrLogDsc;" + _
"MR Init Category Names=1;" + _
"MR Init Custom=SearchSubFolders=True"
Set adoConnection = CreateObject("ADODB.Connection")
adoConnection.Open(ConnectionString)
Dim OutputPath, ReportName, SQLQuery, HeaderString
OutputPath = LOG_FILES_FOLDER + "\"
' Find the start and length of the machine name
' Should be a subdirectory of the LOG_FILES_FOLDER
Dim MachineIndex, MachineLength, MachineExpression
MachineIndex = Len(OutputPath)
MachineLength = GetSubFolderLength(LOG_FILES_FOLDER)
MachineExpression = "Mid(LogFile," + CText(MachineIndex) + "," + CText(MachineLength) + ")"
' First run a query to get the log file overlap
ReportName = "LogOverlap"
SQLQuery = "SELECT Min(DateTime), Max(DateTime), __MachineFromLogFile__ " + _
"FROM VDATA " + _
"GROUP BY __MachineFromLogFile__"
HeaderString = "StartDateTime, EndDateTime, Machine"
ExportSQLOutput(adoConnection, ReportName, HeaderString, SQLQuery, MachineExpression, OutputPath)
' Execute a set of standard SQL queries based on the tier type
If (UCase(TIER_TYPE) = "WEB") Then
' Get the number of IsActive timeouts in 15 minute intervals
ReportName = "IsActiveTimeoutsIn15MinuteIntervals"
SQLQuery = "SELECT DateTime.DateOnly() As Day, " + _
"CText(DateTime.DatePart('h'))+':'+CText(DateTime.DatePart('n')/15 * 15) As QuarterHour, " + _
"COUNT(*) As IsActiveErrors " + _
"FROM VDATA " + _
"WHERE LogEntry.Find('IsActive') >= 0 AND LogLevel = {warning} " + _
"GROUP BY " + _
"DateTime.DateOnly(), " + _
"DateTime.DatePart('h'), " + _
"DateTime.DatePart('n')/15 " + _
"ORDER BY " + _
"DateTime.DateOnly(), " + _
"DateTime.DatePart('h'), " + _
"DateTime.DatePart('n')/15"
HeaderString = "Day,QuarterHour,IsActiveErrors"
ExportSQLOutput(adoConnection, ReportName, HeaderString, SQLQuery, MachineExpression, OutputPath)

' Get the number of timeouts per question
ReportName = "TimeoutsPerProjectAndQuestion"
SQLQuery = "SELECT LogEntry.Mid(LogEntry.Find('I.Project') + 10, 8) As Project, " + _
"LogEntry.Mid(LogEntry.Find('HTTP Client')+ 12, 20) As Error, " + _
"IIf(LogEntry.Find('CreateInterview') = -1, IIf(LogEntry.Find('I.SavePoint') >= 0, LogEntry.Mid(LogEntry.Find('I.SavePoint') + 12, LogEntry.Find('&', LogEntry.Find('I.SavePoint')) - LogEntry.Find('I.SavePoint')-12), '[Post]'), '[CreateInterview]') As SavePoint, " + _
"COUNT(*) " + _
"FROM VDATA WHERE LogEntry.Find('http') = 0 and LogLevel = {error} " + _
"GROUP BY " + _
"LogEntry.Mid(LogEntry.Find('I.Project') + 10, 8), " + _
"LogEntry.Mid(LogEntry.Find('HTTP Client')+ 12), " + _
"IIf(LogEntry.find('CreateInterview') = -1, IIf(LogEntry.Find('I.SavePoint') >= 0, LogEntry.Mid(LogEntry.Find('I.SavePoint') + 12, LogEntry.Find('&', LogEntry.find('I.SavePoint')) - LogEntry.Find('I.SavePoint')-12), '[Post]'), '[CreateInterview]')"
HeaderString = "Project,Error,SavePoint,Count"
ExportSQLOutput(adoConnection, ReportName, HeaderString, SQLQuery, MachineExpression, OutputPath)

' Template errors by project
ReportName = "TemplateErrorsByProject"
SQLQuery = "SELECT Project, MIN(DateTime) AS FirstTime, MAX(DateTime) AS LastTime, COUNT(*) AS Number, LogEntry FROM VDATA " + _
"WHERE (LogLevel * {warning, error, fatal}) AND LogEntry LIKE '%Template%' " + _
"GROUP BY Project, LogEntry " + _
"ORDER BY Project, COUNT(*) DESC"
HeaderString = "Project,FirstTime,LastTime,Count,LogEntry"
ExportSQLOutput(adoConnection, ReportName, HeaderString, SQLQuery, MachineExpression, OutputPath)
Else
' List the engine shutdowns and starts
ReportName = "EngineShutdownsAndStarts"
SQLQuery = "SELECT __MachineFromLogFile__, DateTime, Milliseconds, LogScope, LogEntry " + _
"FROM VDATA " + _
"WHERE LogEntry.Find('Session Engine s') >= 0"
HeaderString = "Machine,DateTime,Milliseconds,LogScope,LogEntry"
ExportSQLOutput(adoConnection, ReportName, HeaderString, SQLQuery, MachineExpression, OutputPath)

' Count the interview starts and stops over all logs, hopefully the entire cluster
ReportName = "InterviewStartsAndStops"
SQLQuery = "SELECT " + _
"DateTime.DateOnly() As Day, " + _
"CText(DateTime.DatePart('h'))+':'+CText(DateTime.DatePart('n')/15 * 15) As QuarterHour, " + _
"SUM(LogEntry.Find('Interview start') = 0) As InterviewStarts, " + _
"SUM(LogEntry.Find('Interview restart') = 0) As InterviewRestarts, " + _
"SUM(LogEntry.Find('Interview terminated') = 0) As InterviewTerminates, " + _
"SUM(LogEntry.Find('Interview complete') = 0) As InterviewCompletes, " + _
"SUM(LogEntry = 'Interview timeout') As InterviewTimeouts, " + _
"SUM(LogEntry.Find('Abandoning') >= 0) As InterviewTimeoutAbandons, " + _
"SUM(LogEntry.Find('Interview stopped') = 0) As InterviewStops, " + _
"SUM(LogEntry.Find('Interview shutdown') = 0) As InterviewShutdowns, " + _
"SUM(LogEntry.Find('Interview rejected') = 0) As InterviewRejects " + _
"FROM VDATA " + _
"WHERE LogLevel = {metric} " + _
"GROUP BY " + _
"DateTime.DateOnly(), " + _
"DateTime.DatePart('h'), " + _
"DateTime.DatePart('n')/15 " + _
"ORDER BY " + _
"DateTime.DateOnly(), " + _
"DateTime.DatePart('h'), " + _
"DateTime.DatePart('n')/15"
HeaderString = "Day,QuarterHour,Start,Restart,Terminate,Complete,Timeout,TimeoutAbandon,Stop,Shutdown,Reject"
ExportSQLOutput(adoConnection, ReportName, HeaderString, SQLQuery, MachineExpression, OutputPath)

' Count the interview starts and stops per logscope/engine
ReportName = "InterviewStartsAndStops_PerEngine"
SQLQuery = "SELECT " + _
"LogScope, " + _
"DateTime.DateOnly() As Day, " + _
"CText(DateTime.DatePart('h'))+':'+CText(DateTime.DatePart('n')/15 * 15) As QuarterHour, " + _
"SUM(LogEntry.Find('Interview start') = 0) As InterviewStarts, " + _
"SUM(LogEntry.Find('Interview restart') = 0) As InterviewRestarts, " + _
"SUM(LogEntry.Find('Interview terminated') = 0) As InterviewTerminates, " + _
"SUM(LogEntry.Find('Interview complete') = 0) As InterviewCompletes, " + _
"SUM(LogEntry = 'Interview timeout') As InterviewTimeouts, " + _
"SUM(LogEntry.Find('Abandoning') >= 0) As InterviewTimeoutAbandons, " + _
"SUM(LogEntry.Find('Interview stopped') = 0) As InterviewStops, " + _
"SUM(LogEntry.Find('Interview shutdown') = 0) As InterviewShutdowns, " + _
"SUM(LogEntry.Find('Interview rejected') = 0) As InterviewRejects " + _
"FROM VDATA " + _
"WHERE LogLevel = {metric} " + _
"GROUP BY " + _
"LogScope, " + _
"DateTime.DateOnly(), " + _
"DateTime.DatePart('h'), " + _
"DateTime.DatePart('n')/15 " + _
"ORDER BY " + _
"DateTime.DateOnly(), " + _
"DateTime.DatePart('h'), " + _
"DateTime.DatePart('n')/15"
HeaderString = "LogScope,Day,QuarterHour,Start,Restart,Terminate,Complete,Timeout,TimeoutAbandon,Stop,Shutdown,Reject"
ExportSQLOutput(adoConnection, ReportName, HeaderString, SQLQuery, MachineExpression, OutputPath)

' Scripting errors by project
ReportName = "ScriptingErrorsByProject"
SQLQuery = "SELECT Project, MIN(DateTime) AS FirstTime, MAX(DateTime) AS LastTime, COUNT(*) AS Number, LogEntry " + _
"FROM VDATA " + _
"WHERE (LogLevel * {error, fatal}) AND LEFT(LogEntry,4) <> 'HTTP' AND LEFT(LogEntry,26) <> 'The requested sample field' " + _
"GROUP BY Project, LogEntry " + _
"ORDER BY Project, COUNT(*) DESC"
HeaderString = "Project,FirstTime,LastTime,Count,LogEntry"
ExportSQLOutput(adoConnection, ReportName, HeaderString, SQLQuery, MachineExpression, OutputPath)

' Sample errors by project
ReportName = "SampleErrorsByProject"
SQLQuery = "SELECT Project, MIN(DateTime) AS FirstTime, MAX(DateTime) AS LastTime, COUNT(*) AS Number, LogLevel, LogEntry " + _
"FROM VDATA " + _
"WHERE (LogLevel * {warning, error, fatal}) AND (LogEntry LIKE 'The requested sample field%') " + _
"GROUP BY Project, LogEntry, LogLevel " + _
"ORDER BY Project, COUNT(*) DESC"
HeaderString = "Project,FirstTime,LastTime,Count,LogLevel,LogEntry"
ExportSQLOutput(adoConnection, ReportName, HeaderString, SQLQuery, MachineExpression, OutputPath)
End If
adoConnection.Close()
' *********************************************************************************
Function ExportSQLOutput(adoConnection, ReportName, HeaderString, SQLQuery, MachineExpression, OutputPath)
Dim fso, FileName, TextStream, OutputLine
Dim adoRecordSet, adoField
FileName = TIER_TYPE + "_" + ReportName + ".csv"
Debug.Log("Creating report " + FileName)

' Substitute the machine index to create a machine name
SQLQuery = Replace(SQLQuery, "__MachineFromLogFile__", MachineExpression)
Set adoRecordSet = adoConnection.Execute(SQLQuery)
If (adoRecordSet.BOF And adoRecordSet.EOF) Then
Debug.Log(ReportName + ": No records")
ExportSQLOutput = False
Exit Function
End If

Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
fso.DeleteFile(OutputPath + FileName, True)
On Error GoTo ExportSQLOutput_ErrorHandler
Set TextStream = fso.OpenTextFile(OutputPath + FileName, 2 '! ForWriting !', True)
TextStream.WriteLine(HeaderString)

adoRecordSet.MoveFirst()
While (Not(adoRecordSet.EOF))
OutputLine = ""
For Each adoField In adoRecordSet.Fields
OutputLine = OutputLine + CText(adoField.Value) + ","
Next
TextStream.WriteLine(OutputLine)
adoRecordSet.MoveNext()
End While
TextStream.Close()

ExportSQLOutput = True

Exit Function

ExportSQLOutput_ErrorHandler:
Debug.Log(ReportName + ": Error at line " + CText(Err.LineNumber) + ": " + Err.Description)
ExportSQLOutput = False
End Function
' *********************************************************************************
Function GetSubFolderLength(FolderPath)
Dim fso, folder, subfolder
Dim SubFolderLength

SubFolderLength = 0

Set fso = CreateObject("Scripting.FileSystemObject")

' TODO - Error
Set folder = fso.GetFolder(FolderPath)
For Each subfolder In folder.SubFolders
If (Len(subfolder.Name) > SubFolderLength) Then
SubFolderLength = Len(subfolder.Name)
End If
Next

GetSubFolderLength = SubFolderLength
End Function