Sum from successive lines following date header to create data for graphing connections

Shell Programming and Scripting
1

Hi,
I have a log file created from a load balancer showing connections to each member of a two member pool with the following format (where first field is source IP, second field is load balanced IP address and third field is destination member. I need to plot a graph by date/time and number of connections to each pool member (third IP address). By about yesterday lunchtime...

Please would someone be able to point me at the best way of parsing this file to get output similar to:
date IP1 numconnections IP2 numconnections? I'm imagining awk, but just can't get my head round how to start at the moment.

Sorry to appear dumb and really, really grateful for any helpful replies! :slight_smile:

Mon Dec 17 15:07:16 GMT 2012
10.96.244.4:4901 <-> 10.18.1.212:32150 <-> 10.18.1.229:32150 tcp
10.96.244.4:4902 <-> 10.18.1.212:32150 <-> 10.18.1.229:32150 tcp.
10.18.0.148:33257 <-> 10.18.1.212:32150 <-> 10.18.1.230:32150 tcp
Mon Dec 17 15:07:46 GMT 2012
10.96.244.4:4903 <-> 10.18.1.212:32150 <-> 10.18.1.229:32150 tcp
10.96.244.4:4904 <-> 10.18.1.212:32150 <-> 10.18.1.229:32150 tcp.
10.18.0.148:33258 <-> 10.18.1.212:32150 <-> 10.18.1.230:32150 tcp
10.18.0.148:33259 <-> 10.18.1.212:32150 <-> 10.18.1.230:32150 tcp
10.18.0.148:33260 <-> 10.18.1.212:32150 <-> 10.18.1.230:32150 tcp
2

You could use bash, with "while read -a FLDS;do ... done", stashing your stats in an associative array until the date changes or EOF. You might want to add a trailer line to the input stream for the latter behavior, perhaps just a fake high date line echo'd in to the stream inside "(...)|".

If you need more speed: C, C++, JAVA, PERL or a different flavor of shell script that pulls out dates, then pulls out each date's IPs using sed to a stream to '|sort|uniq -c'.

One thing that makes such an app cumbersome/odd is stacking many IPs on one line. Usually, you want RDBMS friendly tuples/rows, one per day and IP, on lines.

3

Yes - thanks for the reply. That's pretty much what I did. Very clumsy and slow on a 3MB sized text file but it didn't take too much thought! I'm sure there're more elegant ways of doing it but I'll leave that for when I have a little more time...

The reasons I created it in the form that I did were:

1) It needed to be done by the previous lunchtime
2) I needed to be able to track who the connections were from.

A little more forethought and I would have created a second log file tracking purely the number of connections! :rolleyes:

Thanks again :b:

4

Stick the data in an RDBMS and sum it there in SQL with simple web page reports.

5

:eek:

I had a pretty interesting time trying to use data points in a chart in MS Excel too - I didn't realise it was limited to 255 points!

6

Sometimes it pays to get your graphing tool independent of your storage and loading. Tools that do many things often only do them "well enough". You can hook an RDBMS to Excel and let it graph stuff. You just need to keep your queries/views small. Maybe libreOffice spreadsheets or Google docs will do more? A nice tool would allow drill down on an abstracted graph.