Author Topic: Utilities for extracting data from logs?  (Read 8201 times)

0 Members and 1 Guest are viewing this topic.

Offline StonentTopic starter

  • Super Contributor
  • ***
  • Posts: 3824
  • Country: us
Utilities for extracting data from logs?
« on: October 20, 2014, 04:15:44 am »
I'm not even sure what you'd call it.  I'm looking for some kind of gui utility (preferrably free) that can be used to extract data from raw text files or logs and then possibly turn it into XML or CSV

What I'd like to be able to do is define things like "when you see Sun/Mon/Tues/Wed/Thu/Fri/Sat" this indicates a new record. Read that entire line and file it under the column "Date"
When you see the phrase "blah" read everything after that and file it under the column "blah1"
If you see "blah" again before a new record starts, read everything after that and file it under the column "blah2"
If you encounter "xyz" but haven't already encountered "abc" store a null under the field abc and read the data associated with xyz.
If you encounter "foo" jump to the next line and read that entire line and file it under the "foo" column.

Certainly something like that exists, I'm just not sure what you call it.

The larger the government, the smaller the citizen.
 

n45048

  • Guest
Re: Utilities for extracting data from logs?
« Reply #1 on: October 20, 2014, 04:43:17 am »
Could you provide a sample of the log data?
You might be able to use Microsoft Excel.
 

Offline Richard Crowley

  • Super Contributor
  • ***
  • Posts: 4317
  • Country: us
  • KJ7YLK
Re: Utilities for extracting data from logs?
« Reply #2 on: October 20, 2014, 04:50:33 am »
Can you reveal the SOURCE of the log files?  That could help us with your question.
For example, are they from a web server?  Which one?
 

Offline StonentTopic starter

  • Super Contributor
  • ***
  • Posts: 3824
  • Country: us
Re: Utilities for extracting data from logs?
« Reply #3 on: October 20, 2014, 05:24:14 am »
It's just data dumped using ipconfig, and tcpdump along with a WMI query. It's just old raw data I've collected over time that I want to move into a better format.

The first part is something like this:

Code: [Select]
echo %date% %time% >> \\networkpath\to\logs\%computername%
ipconfig /all | find /i "Host Name" >> \\networkpath\to\logs\%computername%
ipconfig /all | find /i "Description" >> \\networkpath\to\logs\%computername%
ipconfig /all | find /i "IPv4 Address >> \\networkpath\to\logs\%computername%
ipconfig /all | find /i "Default Gateway" >> \\networkpath\to\logs\%computername%
ipconfig /all | find /i "Physical Address" >> \\networkpath\to\logs\%computername%

That generates something like this:

Code: [Select]
Sun 10/19/2014 20:48:46.61
 
   Host Name . . . . . . . . . . . . : COMPUTERABCD123
   Description . . . . . . . . . . . : Microsoft Virtual WiFi Miniport Adapter
   Description . . . . . . . . . . . : Intel(R) Centrino(R) Advanced-N 6205
   Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller
   IPv4 Address. . . . . . . . . . . : 123.213.123.213(Preferred)
   Default Gateway . . . . . . . . . : 123.213.213.123
   Physical Address. . . . . . . . . : EE-88-B4-31-A5-83
   Physical Address. . . . . . . . . : EE-88-B4-31-A5-82
   Physical Address. . . . . . . . . : EE-AC-6F-CA-DF-80

Because of the way it was filtered you have to line up the first description with the first physical address and so on.

Then it runs a tcpdump command that waits for a cisco CDP packet and filters for the lines that have information on the switch

Since some of the computers have ethernet, wifi, and something else, this portion of the log is sometimes empty unless I change the parameters to check on nic 2, nic 3 etc.

If it works correctly, it will dump something like this:
Code: [Select]
Device-ID (0x01), length: 10 bytes: 'SWITCH3B'
Port-ID (0x03), length: 15 bytes: 'FastEthernet4/1'
Platform (0x06), length: 14 bytes: 'cisco WS-C4506'
Address (0x02), length: 13 bytes: IPv4 (1) 213.123.121.211
Native VLAN ID (0x0a), length: 2 bytes: 244
Duplex (0x0b), length: 1 byte: full
ATA-186 VoIP VLAN request (0x0e), length: 3 bytes: app 1, vlan 344

And the final line of the program is

Code: [Select]
wmic bios get serialnumber
That generates a line like this:
Code: [Select]
SerialNumber 
XYZABC123

It's all raw like that because I had a specific need at the time to trace down a computer's location on the network when it couldn't be located.  It ended up being useful so I decided to just keep gathering the data.  I keep capturing the serial number and name because sometimes computers get renamed but the serial number is permanent, so it would make a good primary key in a database.

Once I get my legacy data stored I'll work on rewriting the data collection script to wrap XML tags around individual items.
The larger the government, the smaller the citizen.
 

Offline StonentTopic starter

  • Super Contributor
  • ***
  • Posts: 3824
  • Country: us
Re: Utilities for extracting data from logs?
« Reply #4 on: October 20, 2014, 05:26:18 am »
I know about grep and have tried to figure out regexp, but never get it to work right. Especially stuff like "when you see this, ignore it, but display everything after it"
The larger the government, the smaller the citizen.
 

n45048

  • Guest
Re: Utilities for extracting data from logs?
« Reply #5 on: October 20, 2014, 05:33:18 am »
It's fiddly but I did something similar with a bunch of nested IF statements, VLOOKUP/HLOOKUP and the use of LEFT/RIGHT functions in Excel. Correct me if I'm wrong but wouldn't Active Directory on the domain controller give you the same info?
« Last Edit: October 20, 2014, 05:35:35 am by Halon »
 

Offline nowlan

  • Frequent Contributor
  • **
  • Posts: 649
  • Country: au
Re: Utilities for extracting data from logs?
« Reply #6 on: October 20, 2014, 05:55:46 am »
Perl would be able to do what you want. But I wouldnt recommend learning it.
Possibly python if you want to learn proper scripting.

Im sure there are inventory management tools around, rather than reinvent this.

In fact, SNMP/WMI would fast way to poll your network.

btw, mac addresses *should* be unique to each pc.
 

Offline halexa

  • Regular Contributor
  • *
  • Posts: 156
  • Country: se
Re: Utilities for extracting data from logs?
« Reply #7 on: October 20, 2014, 06:23:17 am »
Hi,

I would recommend Python for this task. Easy to learn and script.
 

Offline Rufus

  • Super Contributor
  • ***
  • Posts: 2095
Re: Utilities for extracting data from logs?
« Reply #8 on: October 20, 2014, 06:33:28 am »
"AWK is an interpreted programming language designed for text processing and typically used as a data extraction and reporting tool."

http://en.wikipedia.org/wiki/AWK

GNU awk is a good implementation

http://gnuwin32.sourceforge.net/packages/gawk.htm

Syntax is C like so if you know C you are half way there.
 

Online PlainName

  • Super Contributor
  • ***
  • Posts: 6796
  • Country: va
Re: Utilities for extracting data from logs?
« Reply #9 on: October 20, 2014, 11:59:37 am »
I wonder if Log File Viewer might be useful:

http://www.log-file-tools.com/product/log-file-viewer-standard/index.html

It is a tricky one to describe, but essentially you tell it how to parse the log file and off it goes, displaying whatever fields you select in columnar format. Filtering is reasonably simple and you can export the results to CSV, etc. Things like awk might be more capable but you need to be a programmer or command line guru to get the best from them, and this one is more appropriate to a lazy GUI button pusher :)
 

Offline Smokey

  • Super Contributor
  • ***
  • Posts: 2540
  • Country: us
  • Not An Expert
Re: Utilities for extracting data from logs?
« Reply #10 on: October 20, 2014, 09:52:05 pm »
Here are my recommendations in no particular order...

1) Python....
2) Python....
3) Python....
....
n) Python....

Once you write your first script to handle something like this, you'll be hooked and find uses for tools like it all over the place.  Its' time very well spent.  Take a shot, and post up when you get stuck.  We can help you out... but only if it's python :)
 

Offline Bored@Work

  • Super Contributor
  • ***
  • Posts: 3932
  • Country: 00
Re: Utilities for extracting data from logs?
« Reply #11 on: October 20, 2014, 11:39:37 pm »
grep alone is probably too simple for the job. So either grep plus other basic Unix text processing tools (there are many), or a language with some more ompf. E.g. the already mentioned awk, or maybe even perl.

It's just data dumped using ipconfig, and tcpdump along with a WMI query. It's just old raw data I've collected over time that I want to move into a better format.

The first part is something like this:

Code: [Select]
echo %date% %time% >> \\networkpath\to\logs\%computername%
ipconfig /all | find /i "Host Name" >> \\networkpath\to\logs\%computername%
ipconfig /all | find /i "Description" >> \\networkpath\to\logs\%computername%
ipconfig /all | find /i "IPv4 Address >> \\networkpath\to\logs\%computername%
ipconfig /all | find /i "Default Gateway" >> \\networkpath\to\logs\%computername%
ipconfig /all | find /i "Physical Address" >> \\networkpath\to\logs\%computername%

That generates something like this:

Code: [Select]
Sun 10/19/2014 20:48:46.61
 
   Host Name . . . . . . . . . . . . : COMPUTERABCD123
   Description . . . . . . . . . . . : Microsoft Virtual WiFi Miniport Adapter
   Description . . . . . . . . . . . : Intel(R) Centrino(R) Advanced-N 6205
   Description . . . . . . . . . . . : Broadcom NetXtreme 57xx Gigabit Controller
   IPv4 Address. . . . . . . . . . . : 123.213.123.213(Preferred)
   Default Gateway . . . . . . . . . : 123.213.213.123
   Physical Address. . . . . . . . . : EE-88-B4-31-A5-83
   Physical Address. . . . . . . . . : EE-88-B4-31-A5-82
   Physical Address. . . . . . . . . : EE-AC-6F-CA-DF-80

Because of the way it was filtered you have to line up the first description with the first physical address and so on.

Here is a rough example how to do that kind of line up in awk. From memory, I didn't run it:

Code: [Select]
#
# Check if line starts with the name of a day.
# If yes, reset counters, because a new record starts
#
/^(Mon|Tue|Wed|Thu|Fri|Sat|Sun) / {
      # remember date information (not used later on in this example)
      d = $0 

      # reset  counters
      ndesc = 0
      nphys = 0

      # empty array
      delete desc

      next
}

/Description/ {
   sub(/.*: /, "")   # get rid of the dotted line and anything before it in the line
   desc[ndesc++] = $0   # Store the remaining description
   next
}

/Physical Address/ {
   sub(/.*: /, "")   # get rid of the dotted line and anything before it in the line
   print $0 ": " desc[nphys++] # print MAC address and corresponding description
   next
}
I delete PMs unread. If you have something to say, say it in public.
For all else: Profile->[Modify Profile]Buddies/Ignore List->Edit Ignore List
 

Offline dave_k

  • Frequent Contributor
  • **
  • Posts: 285
  • Country: au
Re: Utilities for extracting data from logs?
« Reply #12 on: October 21, 2014, 09:57:23 am »
PERL = Practical Extraction & Reporting Language

That's what I'd be using ...
 

Offline atferrari

  • Frequent Contributor
  • **
  • Posts: 314
  • Country: ar
Re: Utilities for extracting data from logs?
« Reply #13 on: October 21, 2014, 11:56:24 am »
I recall Don Lancaster (search tinaja, guru, liar) explaining how he retrieved data of accesses to his site.
Agustín Tomás
In theory, there is no difference between theory and practice. In practice, however, there is.
 

Offline Leiothrix

  • Regular Contributor
  • *
  • Posts: 104
  • Country: au
Re: Utilities for extracting data from logs?
« Reply #14 on: October 21, 2014, 09:25:25 pm »
It sounds like you need to write a script -- Perl, PHP, Python, Ruby, whatever, to parse that data and store it in a database.

If you're really keen, write a web frontend to the DB so you can query it easily from any machine.

You can get all the GNU tools (grep, awk, etc), as well as the various scripting languages on Windows too if that's your thing.

 

Offline grumpydoc

  • Super Contributor
  • ***
  • Posts: 2905
  • Country: gb
Re: Utilities for extracting data from logs?
« Reply #15 on: October 21, 2014, 10:35:29 pm »
PERL = Practical Extraction & Reporting Language

That's what I'd be using ...
According to the documentation Larry Wall admitted it is actually  Pathologically Eclectic Rubbish Lister :)

I'd probably use Perl as well.


« Last Edit: October 22, 2014, 06:20:31 am by grumpydoc »
 

Offline ve7xen

  • Super Contributor
  • ***
  • Posts: 1192
  • Country: ca
    • VE7XEN Blog
Re: Utilities for extracting data from logs?
« Reply #16 on: October 22, 2014, 03:51:38 am »
To continue the tangent, it's Perl or perl and is a proper name, not an acronym. Many amusing backronyms have been devised, but the name of the language is just Perl.

As far as the problem at hand, I would address this with a better tool on the machines that outputs a more sensible log than trying to parse that mess after the fact. Write some PowerShell or VBS to pull the data you need and write it to XML or submit it to a web service as JSON or whatever. Logs that are not either a) single line with leading timestamp and machine/process ID or b) structured format with well-defined grammar (ie. XML, JSON etc.) are useless and a nightmare to deal with.
73 de VE7XEN
He/Him
 

Offline ivaylo

  • Frequent Contributor
  • **
  • Posts: 661
  • Country: us
Re: Utilities for extracting data from logs?
« Reply #17 on: October 22, 2014, 07:27:45 am »
There are even busineses nowadays around this need - https://www.loggly.com
But you do need log files in "standard" format...
 

Online Jeroen3

  • Super Contributor
  • ***
  • Posts: 4068
  • Country: nl
  • Embedded Engineer
    • jeroen3.nl
Re: Utilities for extracting data from logs?
« Reply #18 on: October 22, 2014, 10:54:04 am »
Some time ago I constructed (quick&dirty) a QT C++ tool that could read ARM Linker MAP files to sort-able tables.
It was easy using regexps and known strings in the file.
https://github.com/Jeroen6/MapViewer
 

Offline grumpydoc

  • Super Contributor
  • ***
  • Posts: 2905
  • Country: gb
Re: Utilities for extracting data from logs?
« Reply #19 on: October 22, 2014, 11:35:11 am »
Quote from: ve7xen
To continue the tangent, it's Perl or perl and is a proper name, not an acronym.
It's Perl - to quote perl.org:

"Perl" is the name of the language. Only the "P" is capitalized. Traditionally, the name of the program which runs the Perl script is "perl" with a lowercase "p". Most references to the language should use "Perl"

(I know I put PERL in my post, it was left over from editing the post I was quoting and I forgot to change it).

Quote
As far as the problem at hand, I would address this with a better tool on the machines that outputs a more sensible log than trying to parse that mess after the fact. Write some PowerShell or VBS to pull the data you need and write it to XML or submit it to a web service as JSON or whatever. Logs that are not either a) single line with leading timestamp and machine/process ID or b) structured format with well-defined grammar (ie. XML, JSON etc.) are useless and a nightmare to deal with.

To be fair Stonent  did say he wanted the output to be in XML but if he already has "that mess" to parse I'd say a language which has powerful and flexible regular expression matching is going to be a good bet - I'd tend to go with Perl, expecially on a unix/linux platform.

Given this is on Windows - dunno. Never used PowerShell so can't say. VB(S) in all its forms sucks so I'm not sure it would be my first choice for anything where it is not the only choice.

The nice thing about Perl is there are so many ways of doing any given task.

The awkward thing about Perl is that there are so many ways of doing any given task. :)
 

Offline ve7xen

  • Super Contributor
  • ***
  • Posts: 1192
  • Country: ca
    • VE7XEN Blog
Re: Utilities for extracting data from logs?
« Reply #20 on: October 22, 2014, 05:00:43 pm »
To be fair Stonent  did say he wanted the output to be in XML but if he already has "that mess" to parse I'd say a language which has powerful and flexible regular expression matching is going to be a good bet - I'd tend to go with Perl, expecially on a unix/linux platform.
Yes, I would too. My point was that it's probably easier to write a better script for the machines that outputs a sensible format than it is to parse an un-structured block of text like that. And a nicer solution to the problem, too. The relevant tools to parse the mess he has have already been mentioned, for the most part, no need to keep rehashing.

Quote
Given this is on Windows - dunno. Never used PowerShell so can't say. VB(S) in all its forms sucks so I'm not sure it would be my first choice for anything where it is not the only choice.
They were suggested because they are both installed by default on modern Windows and can be used for purposes such as this, widely deployed management scripts to extract information about the machine. Either has much better (and more parametric) access to system data like IP addresses and so on that he wants to extract than does a batch file. If all the machines are network accessible, it probably makes even more sense to use WMI or PowerShell remoting to do it. I think someone already mentioned there are commercial tools available to do this kind of thing in an automated, remote fashion.
73 de VE7XEN
He/Him
 

Offline Leiothrix

  • Regular Contributor
  • *
  • Posts: 104
  • Country: au
Re: Utilities for extracting data from logs?
« Reply #21 on: October 22, 2014, 09:16:33 pm »
If all the machines are network accessible, it probably makes even more sense to use WMI or PowerShell remoting to do it.

We use a VBS file as part of the logon script (GPO would be fine too).

The VBS script dumps a bunch of WMI & registry stuff to a text file in a standard format, then moves that file to a network file share for processing.

A parser runs on the server which reads these text files and updates the database.

There is a intranet frontend for the DB so our helpdesk/desktop guys can query it, and some people have direct access (read only) to the DB for ad-hoc reporting.

We've tried a bunch of different methods, and this has worked the best in our environment.
 

Offline djacobow

  • Super Contributor
  • ***
  • Posts: 1151
  • Country: us
  • takin' it apart since the 70's
Re: Utilities for extracting data from logs?
« Reply #22 on: October 22, 2014, 09:25:40 pm »
I'd probably use Perl as well.

Not to beat a dead horse, but me, too. An added benefit for Perl is that you'll find modules ready to go for dumping a native data structure to <$format_of_your_choice>, be it XML (eh), JSON (yay), YAML (double yay!) or whatever. So really, you'll only have to write the parsing half of your script. The dumping part is done for you.

OK, probably the same sitch in Python, but I have to give love to Perl since so many people hate on it these days.

 


Share me

Digg  Facebook  SlashDot  Delicious  Technorati  Twitter  Google  Yahoo
Smf