Application statistics

**Derrick Livingston** · 02-09-2012, 06:22 PM

Hi All,

Thanks in advance for any help offered !

I am new to programming and need to gather some applications statistics. I basically need to read a log file and search on some strings to gather the required data.

Sample of Log file below:
GatewayLogFilePic.jpg

I have the created code below which outputs some information but I don't know what should be my next steps. Please advise.
---------------------------------------------------------------------------------------------------------------------------------------------------
[SIZE=4]Dim infilename As String 'name of the .txt file in the log
Dim filestring As String
Dim TextLine As String ' line of text
Dim filetoopen

Sub auto_open2()
'If IsEmpty(ActiveSheet.Range("a1").Value) = True Then
MsgBox ("Please select a Gateway log file")

' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
' ChDir should point to the location of your log files path located in quotation marks
ChDir "\\Nycs00057034\SQMPLUS\data\logs\"
' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
filetoopen = Application.GetOpenFilename("Text Files (*.*), *.*")
If filetoopen = False Then
Exit Sub
End If

filestring = filetoopen
filestring = leftspace(filestring)
If MsgBox("Are you sure you want to open " & filestring & "?", vbYesNo) = vbNo Then Run "auto_open"
Run "GET_RECORD2"
'Run "SAVE"
'End If
End Sub
'==========================================================================
'- WORKSHEET 1 ROUTINE
'==========================================================================
Private Sub GET_RECORD2()
Application.ScreenUpdating = True
ActiveSheet.Name = "PB Reports"
Range("a1").Select

Open filetoopen For Input As #1
Do Until EOF(1)
Line Input #1, TextLine

If TextLine Like "Opened Input Files: Directory *" Then
ActiveCell.Value = Mid(TextLine, 32)
ActiveCell.Offset(0, 1).Select
End If
If TextLine Like "Totals: Pages *" Then
ActiveCell.Value = TextLine
ActiveCell.Offset(1, -1).Select
End If
If TextLine Like "*ScriptSetError: ErrorNumber *" Then
ActiveCell.Offset(1, -1).Select
End If

Loop

Close #1

Range("a1").Select

MsgBox "Complete"
End Sub
'============ END OF MAIN ROUTINE =========================================

Public Function leftspace(str As String) As String
Dim i As Long
Dim j As Long

i = 1
j = 1

Do
j = InStr(j, str, "\", vbBinaryCompare)

If j <= 0 Then
Exit Do
Else
i = j
j = j + 1
End If

Loop

leftspace = Right(str, Len(str) - i)
'returns the number of pages of the report as a string
End Function
---------------------------------------------------------------------------------------------------------------------------------------------------

**rylo** · 02-09-2012, 06:58 PM

Hi

What do you want to do next? Process more records? Strip some data?

How about you add a part of the log file (not a picture) and an example workbook of what you want to return from this log file.

rylo

**Derrick Livingston** · 02-10-2012, 11:42 AM

Here is the log file:
=====================================================================================================
[2012\02\09:00:04:01.424]-LogEntry:Script[SiteScripts\JCLDecoder_SIDE] ******** Job Begin ********
[2012\02\09:00:04:01.440]-LogEntry:Script[SiteScripts\JCLDecoder_SIDE]
Opened Input Files: Directory [D:\Sqmplus\Download\SIDE]
File Name Timestamp Size (KBytes)
SID_CA_TXSID001_Fintrac_2012-02-09T00:01:23 20
----------------------------------------------
Totals: Files [ 1] KBytes [ 20]

[2012\02\09:00:04:01.456]-LogEntry:Script[SiteScripts\JCLDecoder_SIDE] Created Report File: 12040000
[2012\02\09:00:04:01.456]-LogEntry:Script[SiteScripts\JCLDecoder_SIDE] Open Input File: SID_CA_TXSID001_Fintrac_
[2012\02\09:00:04:01.471]-LogEntry:Script[SiteScripts\JCLDecoder_SIDE] Report Title: [SID_CA_TXSID001_FINTRAC_FOREIGN_PAYMENT] Pages [4]
[2012\02\09:00:04:01.487]-LogEntry:Script[SiteScripts\JCLDecoder_SIDE]
Report File Titles: File [D:\SQMPLUS\STAGING\12040000]
Report Titles ReportID New Pages Lines Cols
SID_CA_TXSID001_FINTRAC_FOREIGN_PAYMENT 29451 F 4 254 119

----------------------------------------------

Totals: Pages [4] Reports [1] New [0]
[2012\02\09:00:04:01.503]-LogEntry:Script[SiteScripts\JCLDecoder_SIDE]
Report File Added: File [D:\SQMPLUS\STAGING\12040000.RPT]
Date Pages Lines Cols Reports New Size
2012\02\08 4 254 119 1 0 9
[2012\02\09:00:04:01.518]-LogEntry:Script[SiteScripts\JCLDecoder_SIDE]
Job Summary:
Start Time: [2012\02\09:00:04:01.440]
End Time : [2012\02\09:00:04:01.440]
Elapsed Seconds : [ 0]
Input Files : Count [ 1] Size [ 20] (KBytes)
Report Files : Count [ 1] Size [ 9] (KBytes)
Data Content : Pages [ 4] Reports [ 1] New [ 0] Unknown [ 0]
Processing Speed: Pages/Sec [ -1] Bytes/Sec [ -1024]
Size Statistics : Bytes/Page [ 2304] Comp Ratio [ 55]
[2012\02\09:00:04:01.518]-LogEntry:Script[SiteScripts\JCLDecoder_SIDE] ********* Job End *********

**Derrick Livingston** · 02-10-2012, 12:15 PM

Application_statistics.xls attach is the workbook. If you cant see it I have an example below.
----------------------------------------------------------------------------------------
Application # of reports size in (kbytes)

SIDE 1 20
Another application 10 500
Another application 34 1090
------------------------------------------------------------------

There are jobs which are repeated in my log. I would like the program to keep track of how many files and the size per unique job. So if the SIDE application ran 10 times the # of files and size would be the sum of all the SIDE jobs (not showing the SIDE job 10 times in my table.)

**Derrick Livingston** · 02-10-2012, 11:49 AM

I would like to collect the Directory of where the file is being put. This is indicated by Opened Input Files: Directory [D:\Sqmplus\Download\SIDE]. However I would need on the word SIDE to indicate the application. I would also like to collect the number of reports indicated by Totals: Pages [4] Reports [1] New [0]. I would only need the actually value in this case would be '1'. I would also like the Input Files : Count [ 1] Size [ 20] (KBytes). Out of this I would only need the size of the file. In this case it would be '20 kbytes'.

**royUK** · 02-10-2012, 12:15 PM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**Derrick Livingston** · 02-10-2012, 12:32 PM

Thanks for the reply,
I have re-posted the code to make sure I am compliant, however I dont see where I can click the # sound at the top of the window.

[SIZE=4]Dim infilename As String 'name of the .txt file in the log
Dim filestring As String
Dim TextLine As String ' line of text
Dim filetoopen

Sub auto_open2()
'If IsEmpty(ActiveSheet.Range("a1").Value) = True Then
MsgBox ("Please select a Gateway log file")

' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
' ChDir should point to the location of your log files path located in quotation marks
ChDir "\\Nycs00057034\SQMPLUS\data\logs\"
' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
filetoopen = Application.GetOpenFilename("Text Files (*.*), *.*")
If filetoopen = False Then
Exit Sub
End If

filestring = filetoopen
filestring = leftspace(filestring)
If MsgBox("Are you sure you want to open " & filestring & "?", vbYesNo) = vbNo Then Run "auto_open"
Run "GET_RECORD2"
'Run "SAVE"
'End If
End Sub
'==========================================================================
'- WORKSHEET 1 ROUTINE
'==========================================================================
Private Sub GET_RECORD2()
Application.ScreenUpdating = True
ActiveSheet.Name = "PB Reports"
Range("a1").Select

Open filetoopen For Input As #1
Do Until EOF(1)
Line Input #1, TextLine

If TextLine Like "Opened Input Files: Directory *" Then
ActiveCell.Value = Mid(TextLine, 32)
ActiveCell.Offset(0, 1).Select
End If
If TextLine Like "Totals: Pages *" Then
ActiveCell.Value = TextLine
ActiveCell.Offset(1, -1).Select
End If
If TextLine Like "*ScriptSetError: ErrorNumber *" Then
ActiveCell.Offset(1, -1).Select
End If

Loop

Close #1

Range("a1").Select

MsgBox "Complete"
End Sub
'============ END OF MAIN ROUTINE =========================================



Public Function leftspace(str As String) As String
Dim i As Long
Dim j As Long

i = 1
j = 1

Do
j = InStr(j, str, "\", vbBinaryCompare)

If j <= 0 Then
Exit Do
Else
i = j
j = j + 1
End If

Loop

leftspace = Right(str, Len(str) - i)
'returns the number of pages of the report as a string
End Function

**royUK** · 02-10-2012, 12:51 PM

That icon is in the go advanced reply/edit

**Derrick Livingston** · 02-10-2012, 01:01 PM

OK got it. thanks for the update.

**rylo** · 02-10-2012, 05:15 PM

Hi

See how this goes

Private Sub GET_RECORD2()
  Application.ScreenUpdating = True
  ActiveSheet.Name = "PB Reports"
  Range("A2:C2").Value = Array("Application", "# of reports", "Size in (kbytes)")
  outrow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  Open filetoopen For Input As #1
  Do Until EOF(1)
    Line Input #1, TextLine

    If TextLine Like "Opened Input Files: Directory *" Then
      holder = WorksheetFunction.Substitute(Mid(TextLine, 32), "]", "")
      placer = Len(holder) - Len(WorksheetFunction.Substitute(holder, "\", ""))
      holder = WorksheetFunction.Substitute(holder, "\", "|", placer)
      holder = Right(holder, Len(holder) - InStr(1, holder, "|"))
      Cells(outrow, 1).Value = holder
      
    End If
    If TextLine Like "Totals: Pages *" Then
      arr = Split(TextLine, "]")
      holder = arr(1)
      holder = Right(holder, Len(holder) - InStr(1, holder, "["))
      Cells(outrow, 2).Value = holder
    End If
    If TextLine Like "Input Files : Count *" Then
      arr = Split(TextLine, "[")
      holder = arr(2)
      holder = Left(holder, InStr(1, holder, "]") - 1)
      Cells(outrow, 3).Value = holder
    End If

  Loop

  Close #1

  Range("a1").Select

  MsgBox "Complete"
End Sub

rylo

**Derrick Livingston** · 02-10-2012, 07:20 PM

Thanks for your input greatly appreciated. A few issues encountered are that the Application, and # of report fields get overlayed with each subsequent entry. Also the size in Kybtes information is not populated. This definitely looks close to what is needed. It also helps me to understand what I need to review over the weekend to understand your approach and code. I guess I will be studying arrays over the weekend. In addition to the cell function.

**rylo** · 02-10-2012, 10:19 PM

Hi

Which workbook is the macro stored in? Is it the same place as the output? If so, then how are you going to handle the multiple sheet name changes?

I'm not sure why it is overwriting anything as each instance should have its own output row.

Can you attach 2 copies of the log file? Really attach the log file, not a snip onto the post.

rylo

**Derrick Livingston** · 02-13-2012, 10:42 AM

The Log file is attached. However I couldn't attache two copies. The other log files are to big.

**Derrick Livingston** · 02-13-2012, 10:45 AM

In case you meant two copies of the same file...

**rylo** · 02-13-2012, 05:39 PM

Hi

how about answers to the other questions I asked in post #12.

rylo

**Derrick Livingston** · 02-13-2012, 05:59 PM

How do I find out which book the macro is in ? The output is always on the first sheet labeled PB Reports.

**rylo** · 02-13-2012, 06:44 PM

Hi

Something that wasn't obvious from the log file on the post (before attachment) was that there were multiple items to process in the same file. See how this goes.

Dim infilename As String 'name of the .txt file in the log
Dim filestring As String
Dim TextLine As String ' line of text
Dim filetoopen

Sub auto_open2()
'If IsEmpty(ActiveSheet.Range("a1").Value) = True Then
MsgBox ("Please select a Gateway log file")

' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
' ChDir should point to the location of your log files path located in quotation marks
'ChDir "\\Nycs00057034\SQMPLUS\data\logs\"
' >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
filetoopen = Application.GetOpenFilename("Text Files (*.*), *.*")
If filetoopen = False Then
Exit Sub
End If

filestring = filetoopen
filestring = leftspace(filestring)
If MsgBox("Are you sure you want to open " & filestring & "?", vbYesNo) = vbNo Then Run "auto_open"
Run "GET_RECORD2"
'Run "SAVE"
'End If
End Sub
'==========================================================================
'- WORKSHEET 1 ROUTINE
'==========================================================================
Private Sub GET_RECORD2()
  Application.ScreenUpdating = True
  'ActiveSheet.Name = "PB Reports"
  Range("A2:C2").Value = Array("Application", "# of reports", "Size in (kbytes)")
  
  Open filetoopen For Input As #1
  Do Until EOF(1)
    Line Input #1, TextLine

    If TextLine Like "Opened Input Files: Directory *" Then
      outrow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      holder = WorksheetFunction.Substitute(Mid(TextLine, 32), "]", "")
      placer = Len(holder) - Len(WorksheetFunction.Substitute(holder, "\", ""))
      holder = WorksheetFunction.Substitute(holder, "\", "|", placer)
      holder = Right(holder, Len(holder) - InStr(1, holder, "|"))
      Cells(outrow, 1).Value = holder
      
    End If
    If TextLine Like "Totals: Pages *" Then
      arr = Split(TextLine, "]")
      holder = arr(1)
      holder = Right(holder, Len(holder) - InStr(1, holder, "["))
      Cells(outrow, 2).Value = holder
    End If
    If TextLine Like "Input Files  : Count*" Then
      arr = Split(TextLine, "[")
      holder = arr(2)
      holder = Left(holder, InStr(1, holder, "]") - 1)
      Cells(outrow, 3).Value = holder
    End If

  Loop

  Close #1

  Range("a1").Select

  MsgBox "Complete"
End Sub

'============ END OF MAIN ROUTINE =========================================



Public Function leftspace(str As String) As String
Dim i As Long
Dim j As Long

i = 1
j = 1

Do
j = InStr(j, str, "\", vbBinaryCompare)

If j <= 0 Then
Exit Do
Else
i = j
j = j + 1
End If

Loop

leftspace = Right(str, Len(str) - i)
'returns the number of pages of the report as a string
End Function

rylo

**Derrick Livingston** · 02-14-2012, 01:25 PM

Hi Rylo,

This is awesome.

This is close to what I am looking for. A couple of things to note. The total pages and input files are fine and are reporting the correct stats. However, There are instances in the log where The Opened Input Files: Directory has a string of [D:\sqmplus\download\Broadridge\Intellimatch\PB_GPB\Text or Opened Input Files: Directory [D:\sqmplus\PDFdownload\PDF_Fxt\PDFText]. The program would then report multiple instances of the application name being Text, or PDFText. I actually need the name PB_GPB or PDF_Fxt to be reported in the application name. Also the results are usable as it is as I can filter the results and calculate my stats that way. Ultimately I would like to report the application name only once and then have the program take the sum of all the reports and size(kbytes), and report those stats. This way in the end I could create a clean graph representing the statistics. I hope that make sense.

**rylo** · 02-14-2012, 05:49 PM

Hi

So what would the general business rule for extraction be? If there is a PB_GPB or PDF_FXT in the directory string, then pre append that to the file name, but only put the file name if they don't exist?

Or do you want to always take the directory immediately prior to the file name in all instances?

rylo

**Derrick Livingston** · 02-14-2012, 06:26 PM

The key here is where you search for

"Opened Input Files: Directory"

and you see

"\TEXT]"

or

"\PDFTEXT] "

at the end of that string you should use the name preceeding that as the application name. Only in those instances. Everything else would remain the same.

An example would be Opened Input Files: Directory [D:\sqmplus\download\Broadridge\Intellimatch\PB_GPB\Text]. I would use PB_GPB as the application name. So I guess I would search the string in reverse until i see the \symbol.

**rylo** · 02-14-2012, 06:47 PM

Hi

Ok try this one.

Private Sub GET_RECORD2()
  Application.ScreenUpdating = True
  'ActiveSheet.Name = "PB Reports"
  Range("A2:C2").Value = Array("Application", "# of reports", "Size in (kbytes)")
  
  Open filetoopen For Input As #1
  Do Until EOF(1)
    Line Input #1, TextLine

    If TextLine Like "Opened Input Files: Directory *" Then
      outrow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      arr = Split(TextLine, "\")
      If Right(arr(UBound(arr)), 5) = "Text]" Then
        holder = arr(UBound(arr) - 1)
      Else
        holder = WorksheetFunction.Substitute(arr(UBound(arr)), "]", "")
      End If
      'holder = WorksheetFunction.Substitute(Mid(TextLine, 32), "]", "")
      'placer = Len(holder) - Len(WorksheetFunction.Substitute(holder, "\", ""))
      'holder = WorksheetFunction.Substitute(holder, "\", "|", placer)
      'holder = Right(holder, Len(holder) - InStr(1, holder, "|"))
      Cells(outrow, 1).Value = holder
      
    End If
    If TextLine Like "Totals: Pages *" Then
      arr = Split(TextLine, "]")
      holder = arr(1)
      holder = Right(holder, Len(holder) - InStr(1, holder, "["))
      Cells(outrow, 2).Value = holder
    End If
    If TextLine Like "Input Files  : Count*" Then
      arr = Split(TextLine, "[")
      holder = arr(2)
      holder = Left(holder, InStr(1, holder, "]") - 1)
      Cells(outrow, 3).Value = holder
    End If

  Loop

  Close #1

  Range("a1").Select

  MsgBox "Complete"
End Sub

rylo

**Derrick Livingston** · 02-15-2012, 05:16 PM

AWESOME !!!!

You gave me a lot to look up tonight to understand what you did. This was a great help to someone who was clueless.

That extracted what I needed. Is there any way to only have the application listed once while maintaining the total count? An example would be application job DTCC ran 10 times but its only listed once with the total # of reports and size for the 10 jobs.

**rylo** · 02-15-2012, 06:00 PM

Hi

Pretty sure it could be done. Attach a copy of the relevant log file so we can review and make the necessary code changes.

rylo

**Derrick Livingston** · 02-15-2012, 06:47 PM

you could use the log files that I sent previously. I am limited by the file limitation on the forum. I can only attach the file that I gave you.

**rylo** · 02-15-2012, 07:01 PM

Hi

OK gotcha. I was looking for DTCC in the existing log file, and couldn't find it then realised that the existing one had a duplicate anyway.

Try this

Private Sub GET_RECORD2()
  Application.ScreenUpdating = True
  'ActiveSheet.Name = "PB Reports"
  Range("A2:C2").Value = Array("Application", "# of reports", "Size in (kbytes)")
  
  Open filetoopen For Input As #1
  Do Until EOF(1)
    Line Input #1, TextLine

    If TextLine Like "Opened Input Files: Directory *" Then
      arr = Split(TextLine, "\")
      If Right(arr(UBound(arr)), 5) = "Text]" Then
        holder = arr(UBound(arr) - 1)
        
      Else
        holder = WorksheetFunction.Substitute(arr(UBound(arr)), "]", "")
      End If
      'holder = WorksheetFunction.Substitute(Mid(TextLine, 32), "]", "")
      'placer = Len(holder) - Len(WorksheetFunction.Substitute(holder, "\", ""))
      'holder = WorksheetFunction.Substitute(holder, "\", "|", placer)
      'holder = Right(holder, Len(holder) - InStr(1, holder, "|"))
      Set findit = Range("A:A").Find(what:=holder)
      If findit Is Nothing Then
        outrow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      Else
        outrow = findit.Row
      End If
      Cells(outrow, 1).Value = holder
      
    End If
    If TextLine Like "Totals: Pages *" Then
      arr = Split(TextLine, "]")
      holder = arr(1)
      holder = Right(holder, Len(holder) - InStr(1, holder, "["))
      Cells(outrow, 2).Value = Cells(outrow, 2).Value + holder
    End If
    If TextLine Like "Input Files  : Count*" Then
      arr = Split(TextLine, "[")
      holder = arr(2)
      holder = Left(holder, InStr(1, holder, "]") - 1)
      Cells(outrow, 3).Value = Cells(outrow, 3).Value + holder
    End If

  Loop

  Close #1

  Range("a1").Select

  MsgBox "Complete"
End Sub

rylo

Application statistics

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Application statistics

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: New to Programming - I need to gather some application statistics - HELP !!

Re: Application statistics

Re: Application statistics

Re: Application statistics

Re: Application statistics

Re: Application statistics

Re: Application statistics

Re: Application statistics

Re: Application statistics

Re: Application statistics

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions