DECLARE @timeon smalldatetime, @timeon smalldatetime, @userip char(16)
Select @timeon = "11/9/96 16:00"
Select @timeoff = "11/9/96 22:00"
Select @userip = "188.8.131.52"
Select UserName, Start=DateAdd(Second, (0-AcctSessionTime-AcctDelayTime),
Stop=DateAdd(Second, (0-AcctDelayTime), CallDate), Server=SubString(Server,
1, 15), Port=Str(NASPort,4), IP=FramedAddress
from calls, servers
where DateAdd(Second, (0-AcctSessionTime-AcctDelayTime), CallDate) >= @timeon
and DateAdd(Second, (0-AcctDelayTime), CallDate) <= @timeoff
and AcctStatusType = 2
and FramedAddress = @userip
Order By Start
BTW, adding an Index to the AcctStatusType and FramedAddress column can
improve performance on these kind of queries, but remember indexes slow
Michael Whisenant wrote:
> I have been working on a script for a few weeks and am so frustrated with
> the inability to make it work I have tried everything. I am overlooking
> something, but what I want to do is query the database for an IP address
> for a specific day and have the report output the username IP, date, time
> of the record. Nothing too hard, or is it? Basic output would be
> 12/11/97 username1 xxx.xxx.xxx.xxx
> 12/11/97 username2 xxx.xxx.xxx.xxx
> 12/11/97 username3 xxx.xxx.xxx.xxx
> What I am using this for is in case I have an abusive user I can backtrack
> the IP address being used to the account in use. Right now the best I can
> do is get all users using the IP address within the month. Whenever I try
> to restrict to a day or a couple of days, I get ZIP. Therefore near the
> end of the month it takes forever, then I filter thru 4 pages of reports
> for the 5-6 lines of text to get what I need.
> Emerald Mailing List email@example.com
-- Dale E. Reed Jr. (firstname.lastname@example.org)_________________________________________________________________ IEA Software, Inc. | RadiusNT, Emerald, and NT FAQs Internet Solutions for Today | http://www.iea-software.com