DONE: Search a folder of text files for an expression, copy the line of text

[ohfine]

I am monitoring my internet connection (cable TV, phone, and internet) due to previous issues with the service frequently dropping off line.

I am using "EasyNetMonitor" which sends out a ping to Google.com every minute and then makes a one line log entry of “online” or “lost connection”. Each day has it's own text log file in the "Log" folder. This gives me an idea of how many times the service drops out and something to show the cable provider.

I have been copying each text log file, importing it into a spreadsheet, sorting for "lost connection", copy the results to another tab, delete the old tab, and then format the cell backgrounds based on date using alternating colors. The end result is monthly columns showing each lost connection with the days set off by alternating colors.

It works, but I was hoping somebody could help me develop a better way to get the job done. I am not a coder and only know enough about spreadsheets, macros, and batch files to get into trouble.

Any assistance would be appreciated.

[skwire]

Can you provide some samples of your log files and the resulting spreadsheets you create?

[Ath]

You could search google for 'logfile analyzer' (as I did), most top 10 results are free/open source tools with lots of features and options. I'd give one of them a go.

[AndyM]

from a command prompt, the Find command will do what you ask

find "expression" Filename

It will output each line that includes 'expression'.  Standard output is to the monitor, but you can redirect the output to a file:

find "lost connection" LogfileName > OutputFilename

The Output text file will be a list of lines, each one including 'lost connection'.  You can then load this file into a spreadsheet and add your colors (conditional formatting perhaps, odd dates one color, even dates another?)

[MilesAhead]

Also I'd look at some of the Linux utilities ported to Windows. Usually there are many more built in features than similar Windows command line tools. Grep and Find are good examples.  They may take some set up if they need  a bash shell environment.  But as example, find can search for a pattern and execute a command on every match.

Plus they tend to be stable rather than changing behavior with every minor OS version update.

[ohfine]

There is a text file for each day. So after a month there are 30 text files in the log folder.

Text file:
3/1/2011 19:22:16      www.google.com      Online!      26 msec.  IP:
3/1/2011 19:23:16      www.google.com      Online!      24 msec.  IP:
3/1/2011 19:24:21      www.google.com      Lost connection!      
3/1/2011 19:25:16      www.google.com      Online!      21 msec.  IP:
3/1/2011 19:26:16      www.google.com      Online!      28 msec.  IP:
3/1/2011 19:27:16      www.google.com      Online!      40 msec.  IP:
3/1/2011 19:28:16      www.google.com      Online!      38 msec.  IP:
3/1/2011 19:29:16      www.google.com      Online!      33 msec.  IP:
3/1/2011 19:30:21      www.google.com      Lost connection!      
3/1/2011 19:31:16      www.google.com      Online!      37 msec.  IP:
3/1/2011 19:32:16      www.google.com      Online!      49 msec.  IP:
3/1/2011 19:33:16      www.google.com      Online!      23 msec.  IP:


The end product in the spreadsheet is two columns. Each line is then colored grouping the dates together: all 03-01-11 would be yellow, all 03-02-11 would be blue, all 03-03-11 would be yellow, etc

Spreadsheet:
3/1/2011 21:33:23      Lost connection!
3/1/2011 21:41:23      Lost connection!
3/1/2011 23:14:24      Lost connection!
3/3/2011 16:55:02      Lost connection!
3/3/2011 17:16:02      Lost connection!

My biggest problem is processing each text file one by one. I would like to process multiple text files at the same time. If I can get all the "lost connection" lines into an output file, inserting them into a spreadsheet would not be a problem for me.

'logfile analyzers'
I checked and they appear to be over my head and overkill for what I am trying to do.

Using the Find command at the command prompt sounds like it might work if it would process all the text files in the folder at the same time.

conditional formatting perhaps, odd dates one color, even dates another?
Great idea! I never thought of that. I'll have to learn how to do that.

Thanks to all for taking time to help out.

Could someone point me to the help file on posting screenshots for this forum?

[MilesAhead]

to search all .txt files in a folder would be trivial with Grep.  The only thing you have to watch for with Windows versions of Grep is often they want to make a gui out of it. It's really more effective as a command line utility.

I believe this one is a stand-alone windows binary. I'd take a look at it first:

http://gnuwin32.sour...et/packages/grep.htm

[AndyM]

The Find command will work on multiple files.

At a command prompt, make your Log folder the current directory.

Then type:

     find "Lost connection!" *.* > lc.txt

The lc.txt file would have all the dates from all the files in your Log folder.  Then load the lc.txt file into Excel, which will then have each Lost Connection line in it's own row.  Is it important to delete the "www.google.com" ?

The conditional formatting will be a little tricky because of the way your dates are displayed - not a consistant mm/dd/yyyy, but single and double digits for the month and day.

Edit:  it just occured to me that you might have to put the lc.txt file in a different directory so it isn't processed by the Find command along with the rest of the files (*.*)

[Target]

this little AHK script should do what you want

[Select]

#singleinstance
setbatchlines, -1

path = 20d04fe0-3aea-1069-a2d8-08002b30309d

iniread, PATH, CTN.INI, LOGS,Folder

fileselectfolder, PATH, *%path%

iniwrite, %path%, CTN.INI, logs, folder

msgbox, 4,, Delete the Log Files?

loop, %path%/*.txt
{
    loop, read, %a_loopfilefullpath%
    {
        if a_loopreadline contains Lost connection
        {
            stringleft,f1, a_loopreadline,20
            F1 = %F1%
            stringright, F2, a_loopreadline,22
            f2 = %f2%
            fileappend,%f1%`,%f2%`n, %path%/LogOuts_%a_yyyy%-%a_MM%.csv
        }
    }

    ifmsgbox yes
        filedelete, %a_loopfilefullpath%
}

assumes your log files are TXT files (you can edit this, though you'll need to recompile if you want to run it as an executable), outputs the relevant lines to a CSV file.

prompts for the path the the logs (path is stored in an ini file for future runs)

Option to delete the log files as you go...

[cranioscopical]

connect_the_nots.ahk (0.65 KB - downloaded 1 times.)

-Target

Good name, quite a few good points and never a cross word.

[Target]

connect_the_nots.ahk (0.65 KB - downloaded 1 times.)

-Target

Good name, quite a few good points and never a cross word.

-cranioscopical (June 16, 2011, 12:45 PM)

better than a kick in the colon

[ohfine]

MilesAhead,

I installed Grep and took a look at it. It is a bit confusing to me, but I am going to keep it and try to learn more about using it.

AndyM,

I followed your example for the command line, output to a different folder, and it worked. I didn't mess it up. It even separated the output by dates!

I don't think I need to put it into a spreadsheet unless I want to try to colorize it. If I do, it would be easy enough for me to do it manually.

Target,

I love the name you came up with! I ran the .exe and the .ini file worked, but I can't find the output .csv file. What did I do wrong?


Be proud people! This is one of the few places where one does not have to be afraid of asking questions or asking for help.

To all of you, Thank You for your time and patience.

[Target]

I love the name you came up with! I ran the .exe and the .ini file worked, but I can't find the output .csv file. What did I do wrong?

-ohfine (June 16, 2011, 08:17 PM)

sorry, my bad, I should have mentioned that in my original post - output is to the log file directory.  File naming format is LogOuts_YYYY-MM.CSV

note also that if you run it more than once a month it will just append to any pre-existing output file for that month (unless you've renamed said file...)

[kyrathaba]

Looks like this thread could be marked "Finished".  Nice work, Target