DECLARE @timeon smalldatetime, @timeon smalldatetime, @userip char(16)
Select @timeon = "11/9/96 16:00"
Select @timeoff = "11/9/96 22:00"
Select @userip = "204.227.164.2"
Select UserName, Start=DateAdd(Second, (0-AcctSessionTime-AcctDelayTime),
CallDate), Mins=(AcctSessionTime/60),
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 calls.NASIdentifier=servers.ipaddress
and FramedAddress = @userip
Order By Start
GO
BTW, adding an Index to the AcctStatusType and FramedAddress column can
improve performance on these kind of queries, but remember indexes slow
down inserts.
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 listserver@emerald.iea.com
-- Dale E. Reed Jr. (daler@iea.com)_________________________________________________________________ IEA Software, Inc. | RadiusNT, Emerald, and NT FAQs Internet Solutions for Today | http://www.iea-software.com