How to aggregate raw time sampled data into blocks ot 5 min intervals 2

[rleyba828]

We have a network monitoring and logging facility that logs data access to our servers,and the system logs thousands of records per day. I would like to aggregate the data in 5 minute "blocks" so we can do a line graph showing peak and lean times during the day. Sample raw data is below (ip addresses changed for security). How can we define a group "on change of 5 min interval?".

Thanks very much



StartTime,Flgs,Proto,SrcAddr,Sport,Dir,DstAddr,Dport,SrcPkts,DstPkts,SrcBytes,DstBytes,State
01-11-08 09:17:04, e ,6,10.9.32.24,41123,<?>,150.101.201.36,443,2,2,132,132,FIN
01-11-08 09:17:04, e ,6,10.9.32.24,41325,->,150.188.201.36,443,16,12,4479,4318,CON
01-11-08 09:17:04, e s ,6,10.9.32.213,56225,->,10.2.96.228,21,2,0,124,0,REQ
01-11-08 09:17:04, e ,6,64.15.121.112,80,<?>,10.2.32.24,41024,46,46,69644,2760,CON
01-11-08 09:17:04, e ,17,10.2.32.25,1062,<-,10.5.32.215,53,0,2,0,592,RSP

 

[Madawc]

find the difference in minutes between a time and the start of the day, in minutes.

DateDiff ("n", {date1},  {date2})

Divide @FindMins by 20, in a formula field. Truncate to get rid if fractions. Group using this field.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Similar to Madawc's suggestion. Create a formula like the following and group on it:

truncate(DateDiff ("n", datetime({table.datetime}, time(0,0,0)),{table.datetime})/5)

-LB

 

[rleyba828]

Hi lbass (and Madawc too!),

Thanks for your replies...I've got the first part of the solution working, i.e., creating a datediff variable to measure blocks of 5 mins intervals. The only problem now is that when I try to sort on this formula field, it does not show up as a field available for sorting. When I do "Insert --> Group", the only ones I can sort are the actual database fields.

I am using Crystal Reports version 8.0.

Thanks again.

 

[lbass]

You will have to show us the exact formula you used.

-LB

 

[rleyba828]

The actual formula used is:


truncate((Datediff("n", minimum({argustest_csv.STARTTIME}),({argustest_csv.STARTTIME}))/5))

argustest_csv_STARTTIME is a field in the report,

My @sampling-time formula now prints on every record and changes on the 5 minute mark of the "STARTTIME" field which is what I want. I just couldn't group and create summaries based ... "on change of @sampling-time."

(We are using Crystal Reports 8).

Thanks again

 

[lbass]

Why are you using the minimum of the start time? The formula should be the following (I forgot to add date() in my earlier post):

truncate(DateDiff ("n", datetime(date({argustest_csv.STARTTIME}), time(0,0,0)),{argustest_csv.STARTTIME})/5)

-LB

 

[lbass]

PS. This assumes that you have an outer group on date. Then to display the hours and minutes represented by the inner group based on the above formula (let's call it {@formula}, go to report->group expert->{@formula}->customize group name->use a formula->x+2 and enter:

totext(dateadd("n",{@formula}*5,datetime(date({argustest_csv.STARTTIME),time(0,0,0)))

-LB

 

[rleyba828]

Hi LB,

Yes, sorry about that....I was trying to pre-empt your formula thinking that by my adding the minimum (STARTTIME), I could get it to sort properly. But it didn't But now, all I did was cut and paste your text, put it in the formula field, and I got exactly the resulting grouping that I wanted (sorted by date and within each day, sorted by 5 minute interval, starting with the earlier time first...e.g. 5:00 then 5:05, then 5:10). This is great! How do I give you two stars for this?

The only problem I have now is that the line chart which I want to generate (total bytes per 5 minute interval)sorted by day and then within each day, sorted by hour (5 min increments, doesn't appear. It keeps on wanting to list by day only and stacks up the hours per day....so I only get 3 data points instead of 576 or so (for a two day period). This is the last step in the report I have to submit. Every thing else after this is cosmetic.....and I very much appreciate your help on this.

-robert

 

[lbass]

It sounds like you have the wrong chart type chosen if it is stacking--or maybe it appears to be stacking because of the large number of datapoints? Are yo using a very wide printer so that the chart can spread out? How exactly do you have the chart set up? On change of fields, show value field, etc.?

-LB

 

[rleyba828]

Hi LB,

I am quite confident I used the correct line chart but it was the horizontal axis data that was causing it to behave that way. What I did to fix it was to create a new formula field containing the sample points of Date + time (using the technique you showed me above).I was able to sort it out by creating a formula like this --> dateadd ("h",{@sampling-time},datetime(date({argustest_csv.STARTTIME}),time (0,0,0))), then I grouped on this field. This way, we now have a horizontal axis that shows us the network traffic on an hour by hour basis over the # of days we wanted to plot. I changed the sampling time from 5 minutes to 60 minutes to make the chart more readable.

Thanks again for all the help LB