Application statistics

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
---------------------------------------------------------------------------------------------------------------------------------------------------

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

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 *********

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'.

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.)

OK got it. thanks for the update.

Hi

See how this goes

Please Login or Register  to view this content.

rylo

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.

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

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

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

Hi

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

rylo

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

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.

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

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.

Hi

Ok try this one.

Please Login or Register  to view this content.

rylo

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.

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

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.

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

Please Login or Register  to view this content.

rylo