topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Tuesday March 19, 2024, 2:56 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: DONE: Search a folder of text files for an expression, copy the line of text  (Read 10227 times)

ohfine

  • Charter Member
  • Joined in 2006
  • ***
  • default avatar
  • Posts: 5
    • View Profile
    • Donate to Member
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

  • Global Moderator
  • Joined in 2005
  • *****
  • Posts: 5,286
    • View Profile
    • Donate to Member
Can you provide some samples of your log files and the resulting spreadsheets you create?

Ath

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 3,610
    • View Profile
    • Donate to Member
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

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
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?)
« Last Edit: June 14, 2011, 10:48 AM by AndyM »

MilesAhead

  • Supporting Member
  • Joined in 2009
  • **
  • Posts: 7,736
    • View Profile
    • Donate to Member
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

  • Charter Member
  • Joined in 2006
  • ***
  • default avatar
  • Posts: 5
    • View Profile
    • Donate to Member
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

  • Supporting Member
  • Joined in 2009
  • **
  • Posts: 7,736
    • View Profile
    • Donate to Member
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

  • Charter Member
  • Joined in 2006
  • ***
  • Posts: 616
    • View Profile
    • Donate to Member
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 (*.*)
« Last Edit: June 15, 2011, 09:32 PM by AndyM »

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
this little AHK script should do what you want

#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

  • Friend of the Site
  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 4,776
    • View Profile
    • Donate to Member
connect_the_nots.ahk (0.65 KB - downloaded 1 times.)
-Target

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

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
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 :Thmbsup:

ohfine

  • Charter Member
  • Joined in 2006
  • ***
  • default avatar
  • Posts: 5
    • View Profile
    • Donate to Member
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

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
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?

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

  • N.A.N.Y. Organizer
  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 3,200
    • View Profile
    • Donate to Member
Looks like this thread could be marked "Finished".  Nice work, Target  :up: