Creating a Syslog report tool with CF/SQL 2000 - fun

[GregVM]

I'm creating a web report page for our Syslog database and want to provide user stats for VPN usage. I had to really wrangle the table to get the message detail into seperated, managable fields: username, duration, time, date, server hostname, data transmitted and data received. On any given day we might see about 75 VPN sessions logged to the database, in which case some users may have repeat sessions.

I want to output the session and duration data into an HTML table as follows (and I attached the html format code):
username ------------------ Today's sessions -------------Week sesions ------------Month sesions ------- Total hours

The duration data type is varchar with format "01:14:35" (hours:minutes:seconds). I tried to convert to numeric but SQL 2000 threw errors, which I suppose were because of the colons in the data. My hangup is the SQL statement: Can someone give me an idea of the SQL statement necessary to pull this data and group the report by username per each time period??

Here are some queries I'm already using for overall company weekly/monthly VPN stats:

<!---- This query grabs only VPN entries for this week ---->
<CFQUERY NAME=&quot;Kiwi_VPN_Week&quot; DATASOURCE=&quot;kiwi&quot;>
select COUNT(msg_ID) as WeekVPN
From syslogd
WHERE (msghostname = '192.168.24.108') AND datepart(ww,msgdate) = datepart(ww,getdate());
</CFQUERY>


<!---- This query grabs only VPN entries for this month ---->
<CFQUERY NAME=&quot;Kiwi_VPN_Month&quot; DATASOURCE=&quot;kiwi&quot;>
select COUNT(msg_ID) as MonthVPN
From syslogd
WHERE (msghostname = '192.168.24.108') AND datepart(m,msgdate) = datepart(m,getdate());
</CFQUERY>

thanks
-Greg