Abstract: Draw a PingPlotter-style graph

[mattboy_slim]

I have a table full of records that includes a timestamp. I have a cURL script running every 5 minutes that simply hits a page where I write the current time to the database. I want to have a Ping Plotter style chart that shows, during each 5 minute interval, if a record exists (seeing if it is "Up" so to speak).

Here is what I want to accomplish:
http://i41.tinypic.com/bgqtrp.png

The table contains records at each 5-minute (or 5-minute and one second, or 4-minute and 59 second) interval. Because of this one- or two-second time drift, I cannot simply run a loop that checks if the current record is between +2.5 minutes and -2.5 minutes and write either a green image or red image, but rather I need to come up with (which is why I'm here) a clever way to really determine if the cURL script is working.

Soooo...if we start with now() minus 24 hours, we would have a start for the graph. So if we start a loop that runs and adds 5 minutes to that start time, checks for a record, and writes the appropriate image, is that the best way, or is there a better way by determining the time difference between each record as we loop?

I'm not set on any particular method at all, so please, if you have completely different idea, I'm all ears.

Thanks,
Matt

[Old Pedant]

Not to ask a dumbass question, but....

Why not have your "ping" pass a bit of private information (e.g., in the querystring) that only you know about. And then the page that is being hit will notice that info (heck, maybe it could even be the IP address of the pinger?) and write something special to the DB. It could even, at the same time, check to see if the "ping" occurred within a reasonable time differential from the 5 minute interval.

Also, it does occur to me that the system time on your pinger machine and the system time on the pinged machine may not match. So if you sent the ping time from the pinger to the pingee, the pingee could calculate the expected difference and account for it as part of the "reasonable time differential" info.

Anyway, having the pingee being able to differentiate the pinger from other clients that might hit it seems to me to be the best way to ensure that you are really able to check that both the pinger and pingee are working correctly.

[mattboy_slim]

Thanks for the reply. I do record the time that the "pinger" hits the server, and I record his IP address and his unique ID. Here is a sample of data that I have:

Client ID IP Address Ping Time
DunId1hW2hv0 192.168.1.5 3/15/2009 6:50:06 PM
DunId1hW2hv0 192.168.1.5 3/15/2009 6:55:06 PM
DunId1hW2hv0 192.168.1.5 3/15/2009 7:00:07 PM
DunId1hW2hv0 192.168.1.5 3/15/2009 7:05:06 PM
DunId1hW2hv0 192.168.1.5 3/15/2009 7:10:06 PM
DunId1hW2hv0 192.168.1.5 3/15/2009 7:15:06 PM
DunId1hW2hv0 192.168.1.5 3/15/2009 7:20:07 PM
DunId1hW2hv0 192.168.1.5 3/15/2009 7:25:07 PM
DunId1hW2hv0 192.168.1.5 3/15/2009 7:30:07 PM
DunId1hW2hv0 192.168.1.5 3/15/2009 7:35:07 PM


I like your idea regarding your quote:check to see if the "ping" occurred within a reasonable time differential from the 5 minute interval.


In thinking about this however, say we change the data above to look like this:
Client ID IP Address Ping Time Time Difference
DunId1hW2hv0 192.168.1.5 3/15/2009 6:50:06 PM 5
DunId1hW2hv0 192.168.1.5 3/15/2009 6:55:06 PM 5
DunId1hW2hv0 192.168.1.5 3/15/2009 7:00:07 PM 5
DunId1hW2hv0 192.168.1.5 3/15/2009 7:05:06 PM 5
DunId1hW2hv0 192.168.1.5 3/15/2009 7:10:06 PM 5
DunId1hW2hv0 192.168.1.5 3/15/2009 7:45:06 PM 35
DunId1hW2hv0 192.168.1.5 3/15/2009 7:50:07 PM 5
DunId1hW2hv0 192.168.1.5 3/15/2009 7:55:07 PM 5
DunId1hW2hv0 192.168.1.5 3/15/2009 8:00:07 PM 5
DunId1hW2hv0 192.168.1.5 3/15/2009 8:05:07 PM 5


Now we can loop, and if the time difference equals 5, we can write a green bar, but if not, then do we take the number that we do get, divide it by (5), then write that many red bars? Does that sound like it would work?

[Old Pedant]

Sounds quite reasonable.

Except since you are outputting HTML, you don't "write a green bar", etc. Instead, you simply keep track of the WIDTH of the bar and "stretch" a GIF image to fit.

Example output:

<div
><img src="red.gif" height=100 width=3
><img src="green.gif" height=100 width=104
><img src="red.gif" height=100 width=1
>....
></div>

That shows 3 misses followed by a string of 104 hits followed by one miss, etc.

On a good day, you'd end up having just

<div
><img src="green.gif" height=100 width=288
></div>


Hmmm...probably want to use more than one pixel per period since there are only 288 periods in the day. So maybe 2 or 3 pixels per period? Besides, then you'd be able to see a single strip of red in the midst of lots of green.

[mattboy_slim]

Thanks for the input. I have it collecting data, so I'll report back in 24 hours once I get a solid day's worth of data to play with.

[mattboy_slim]

It looks like we're good, unless there is something that I haven't thought of:

Sample screenshot:
http://i42.tinypic.com/33yhyd2.png

Sample data (24 hours)
http://www.shorttext.com/4ynacqad

ASP Code:

<%
dim var_counter 'start a counter
var_counter = 0
If Not rs_syslog.EOF Or Not rs_syslog.BOF Then 'if the recordset is not empty, then continue
While ((var_repeatsyslog__numRows <> 0) AND (NOT rs_syslog.EOF)) 'start looping through recordset
%>
<%
'=================================================================================================== ============================
'= Determine reporting interval
'= 1) If reporting interval is 5 minutes, then write a 1px "up" image
'= 2) If reporting interval is 1 minute, then start a counter
'= a) When counter equals 5, then write a 1px "up" image
'= 3) If the reporting interval is 1440, then it means it is the first record that is created (1440 minutes equals 24 hours)
'= a) Add 24 hours to the first reporting time, then get the minutes between that and now.
'= b) Divide that number by 5 to give us the width of the "down" image (5 minute reporting interval as standard)
'= 4) If any of the above are not true, then divide the number by 5 and write the width of that as a "down" interval
'= 5) If there is no data, then we write a 288px wide image to show 24 hours of downtime
'=================================================================================================== ============================
if (rs_syslog.Fields.Item("f_timedifference").Value) = 5 then
response.write("<img style=""height:48px;width:1px;"" src=""../_images/bargraph_up.png"">")
'if the reporting interval is 1 minute, then we'll run a counter until we get to 5.
elseif (rs_syslog.Fields.Item("f_timedifference").Value) = 1 then
if isnull(var_counter) or var_counter = 0 then
var_counter = 1
elseif var_counter = 5 then
response.write("<img style=""height:48px;width:1px;"" src=""../_images/bargraph_up.png"">")
var_counter = 0
else
var_counter = var_counter + 1
end if
else
if (rs_syslog.Fields.Item("f_timedifference").Value) = 1440 then
var_downwidth = datediff("n",now(),(dateadd("h",24,rs_syslog.Fields.Item("f_timestamp").Value))) / 5
response.write("<img style=""height:48px;width:" & var_downwidth & "px"" src=""../_images/bargraph_down.png"">")
else
var_downwidth = (rs_syslog.Fields.Item("f_timedifference").Value) / 5
response.write("<img style=""height:48px;width:" & var_downwidth & "px"" src=""../_images/bargraph_down.png"">")
end if
end if
%>

<%
var_repeatsyslog__index=var_repeatsyslog__index+1
var_repeatsyslog__numRows=var_repeatsyslog__numRows-1
rs_syslog.MoveNext()
Wend

else
response.write("<img style=""height:48px;width:288px"" src=""../_images/bargraph_down.png"">")
end if
%>


Thanks for the suggestion O.P., your ideas are much appreciated.

Matt

[Old Pedant]

Did you really apply a gradient to your GIF stripes? Nice touch. Something I'll have to remember! I'm going to be doing some graphing via ASP soon, and that would help the appearance a lot. Thanks, back to you!