Summarizing time-series data

[cadfael]

All,

I have a worksheet which contains time-stamped firewall data. The dat
can be grouped by matching fields (e.g. all the ones with matchin
source and destination IP address pairs and expected TCP sequenc
numbers can be grouped together as one "conversation" between tw
hosts).

What I want to do is reduce these ENORMOUS lists by only listing th
first and the last endpoint-to-endpoint transaction.

So if you have data like so:

Entry(1), Date(1), Time(1), source(1), dest(1)
Entry(2), Date(1), Time(2), source(1), dest(1),
Entry(3), Date(1), Time(3), source(1), dest(1)
Entry(4), Date(1), Time(4), source(1), dest(1)
Entry(5), Date(1), Time(5), source(1), dest(1)
Entry(6), Date(1), Time(6), source(1), dest(1)
Entry(7), Date(1), Time(7), source(2), dest(1)
Entry(8), Date(1), Time(8), source(2), dest(1),
Entry(9), Date(1), Time(9), source(2), dest(1)
Entry(10), Date(1), Time(10), source(2), dest(1)
Entry(11), Date(1), Time(11), source(2), dest(1)
Entry(12), Date(1), Time(12), source(2), dest(1)

I just want to run some function which would return only entries
1, 6, 7, and 12, or in other words erase all but those functions.

Any ideas?

Thanks in advance,
Pet

 

[Ron Rosenfeld]

All,

I have a worksheet which contains time-stamped firewall data. The data
can be grouped by matching fields (e.g. all the ones with matching
source and destination IP address pairs and expected TCP sequence
numbers can be grouped together as one "conversation" between two
hosts).

What I want to do is reduce these ENORMOUS lists by only listing the
first and the last endpoint-to-endpoint transaction.

So if you have data like so:

Entry(1), Date(1), Time(1), source(1), dest(1)
Entry(2), Date(1), Time(2), source(1), dest(1),
Entry(3), Date(1), Time(3), source(1), dest(1)
Entry(4), Date(1), Time(4), source(1), dest(1)
Entry(5), Date(1), Time(5), source(1), dest(1)
Entry(6), Date(1), Time(6), source(1), dest(1)
Entry(7), Date(1), Time(7), source(2), dest(1)
Entry(8), Date(1), Time(8), source(2), dest(1),
Entry(9), Date(1), Time(9), source(2), dest(1)
Entry(10), Date(1), Time(10), source(2), dest(1)
Entry(11), Date(1), Time(11), source(2), dest(1)
Entry(12), Date(1), Time(12), source(2), dest(1)

I just want to run some function which would return only entries
1, 6, 7, and 12, or in other words erase all but those functions.

Any ideas?

Try this on your actual data (on a backup) and see if it might work.

If your data comes in as above, first use the Data/Text to Columns wizard to
split it into separate columns using the <comma> and <space> as separators.

Your first line should not be Row1. I assume your first line is Row2.

Label the columns in Row 1: A1: Entry B1: Date, etc.

Add a sixth column F:Change.

In F2 enter the following formula: =AND(D2=D3,E2=E3,D2=D1,E2=E1)

Then, with some cell in the data table selected; Data/Filter/AutoFilter.
Little down arrows should appear at each column label. Next to the Change
label in column F, select the Down arrow, then select FALSE from the dropdown
list.

That should filter your data as you specify if my assumptions are correct. You
can then, if you wish, copy only the visible lines.

You could then copy the visible cells, and paste them onto another sheet.


--ron