Basic Query Help

15 Jul 2005 1:01 PM

TheBook

I'm not a programmer, so I need help with a query.

Table:
IpAddress Char 23
UserName char 20
ComputerName char 20
LoginDate datetime

Many people different people have logged into any one computer many times.

I'm looking to get a Count of the number of times a computer has been logged
into, displayed along with the last UserName that logged into the computer,
the last IPAddress the computer had, and the last LoginDate of the computer.

So results would look like this:

Login Count 33
UserName John Doe (last user to login)
LoginDate 07/15/2005 (most recent logindate)
IPAddress 10.100.100.12 (Last IP associated with LoginDate)

Thanks in advance,
JB

15 Jul 2005 1:16 PM

Alejandro Mesa

Try,

-- supposing there are not more than one login at the same time
select
    t1.ComputerName,
    count(*) as login_count,
    max(IpAddress) as last_IpAddress,
    max(UserName) as last_UserName,
    max(max_LoginDate) as last_LoginDate
from
    t1
    inner join
    (
    select
        ComputerName,
        max(LoginDate) as max_LoginDate
    from
        t1
    group by
        ComputerName
    ) as t2
    on t1.ComputerName = t2.ComputerName
    and t1.LoginDate = t2.max_LoginDate
group by
    t1.ComputerName
go

AMB

Show quote

"TheBook" wrote:

> I'm not a programmer, so I need help with a query.
>
> Table:
> IpAddress Char 23
> UserName char 20
> ComputerName char 20
> LoginDate datetime
>
>
> Many people different people have logged into any one computer many times.
>
> I'm looking to get a Count of the number of times a computer has been logged
> into, displayed along with the last UserName that logged into the computer,
> the last IPAddress the computer had, and the last LoginDate of the computer.
>
> So results would look like this:
>
> Login Count 33
> UserName John Doe (last user to login)
> LoginDate 07/15/2005 (most recent logindate)
> IPAddress 10.100.100.12 (Last IP associated with LoginDate)
>
>
> Thanks in advance,
> JB
>
>
>
>
>
>
>

15 Jul 2005 2:34 PM

TheBook

This one is close.

It gives me the last IP, Username, computername, Logindate correctly,

But the count is way off.

It's showing a count of 1 or 2 when I can see for a fact that the comptuer
has been logged into about 40 times(by many different people)times since I
started the log file.

Here's a sample of the data:
10.180.100.45 Rehab      NURWIRE99                      2005-07-14
10.180.100.45 FernandB NURWIRE99               2005-07-14
10.180.100.45 FernandB   NURWIRE99               2005-07-14
10.180.100.43 blanshaj    NURWIRE99               2005-07-13
10.180.100.43 FernandB   NURWIRE99               2005-07-11

So I'm hoping to get a result that looks like:

Count IPAddress           UserName Computername Logindate
   5       10.180.100.45   Rehab       NurWire99         2005-07-14

So a total of 5 people have logged into this machiine since whenever, and it
shows the last known IPAddress, UserName, Computername, and LoginDate

Thanks again,
JB

Show quote

"Alejandro Mesa" wrote:

> Try,
>
> -- supposing there are not more than one login at the same time
> select
>     t1.ComputerName,
>     count(*) as login_count,
>     max(IpAddress) as last_IpAddress,
>     max(UserName) as last_UserName,
>     max(max_LoginDate) as last_LoginDate
> from
>     t1
>     inner join
>     (
>     select
>         ComputerName,
>         max(LoginDate) as max_LoginDate
>     from
>         t1
>     group by
>         ComputerName
>     ) as t2
>     on t1.ComputerName = t2.ComputerName
>     and t1.LoginDate = t2.max_LoginDate
> group by
>     t1.ComputerName
> go
>
>
> AMB
>
> "TheBook" wrote:
>
> > I'm not a programmer, so I need help with a query.
> >
> > Table:
> > IpAddress Char 23
> > UserName char 20
> > ComputerName char 20
> > LoginDate datetime
> >
> >
> > Many people different people have logged into any one computer many times.
> >
> > I'm looking to get a Count of the number of times a computer has been logged
> > into, displayed along with the last UserName that logged into the computer,
> > the last IPAddress the computer had, and the last LoginDate of the computer.
> >
> > So results would look like this:
> >
> > Login Count 33
> > UserName John Doe (last user to login)
> > LoginDate 07/15/2005 (most recent logindate)
> > IPAddress 10.100.100.12 (Last IP associated with LoginDate)
> >
> >
> > Thanks in advance,
> > JB
> >
> >
> >
> >
> >
> >
> >

15 Jul 2005 2:46 PM

Alejandro Mesa

You are right. Try:

select
    t1.ComputerName,
    count(*) as login_count,
    max(t3.IpAddress) as last_IpAddress,
    max(t3.UserName) as last_UserName,
    max(t3.max_LoginDate) as last_LoginDate
from
    t1
    left outer join
    (
    select
        ComputerName,
        IpAddress,
        UserName,
        LoginDate
    from
        t1
        inner join
        (
        select
            ComputerName,
            max(LoginDate) as max_LoginDate
        from
            t1
        group by
            ComputerName
        ) as t2
        on t1.ComputerName = t2.ComputerName
        and t1.LoginDate = t2.max_LoginDate
    ) as t3
    on t1.ComputerName = t3.ComputerName
    and t1.LoginDate = t3.max_LoginDate
group by
    t1.ComputerName
go

AMB

Show quote

"TheBook" wrote:

> This one is close.
>
> It gives me the last IP, Username, computername, Logindate correctly,
>
> But the count is way off.
>
> It's showing a count of 1 or 2 when I can see for a fact that the comptuer
> has been logged into about 40 times(by many different people)times since I
> started the log file.
>
> Here's a sample of the data:
> 10.180.100.45 Rehab      NURWIRE99                      2005-07-14
> 10.180.100.45 FernandB NURWIRE99               2005-07-14
> 10.180.100.45 FernandB   NURWIRE99               2005-07-14
> 10.180.100.43 blanshaj    NURWIRE99               2005-07-13
> 10.180.100.43 FernandB   NURWIRE99               2005-07-11
>
> So I'm hoping to get a result that looks like:
>
> Count IPAddress           UserName Computername Logindate
>    5       10.180.100.45   Rehab       NurWire99         2005-07-14
>
> So a total of 5 people have logged into this machiine since whenever, and it
> shows the last known IPAddress, UserName, Computername, and LoginDate
>
> Thanks again,
> JB
>
>
>
> "Alejandro Mesa" wrote:
>
> > Try,
> >
> > -- supposing there are not more than one login at the same time
> > select
> >     t1.ComputerName,
> >     count(*) as login_count,
> >     max(IpAddress) as last_IpAddress,
> >     max(UserName) as last_UserName,
> >     max(max_LoginDate) as last_LoginDate
> > from
> >     t1
> >     inner join
> >     (
> >     select
> >         ComputerName,
> >         max(LoginDate) as max_LoginDate
> >     from
> >         t1
> >     group by
> >         ComputerName
> >     ) as t2
> >     on t1.ComputerName = t2.ComputerName
> >     and t1.LoginDate = t2.max_LoginDate
> > group by
> >     t1.ComputerName
> > go
> >
> >
> > AMB
> >
> > "TheBook" wrote:
> >
> > > I'm not a programmer, so I need help with a query.
> > >
> > > Table:
> > > IpAddress Char 23
> > > UserName char 20
> > > ComputerName char 20
> > > LoginDate datetime
> > >
> > >
> > > Many people different people have logged into any one computer many times.
> > >
> > > I'm looking to get a Count of the number of times a computer has been logged
> > > into, displayed along with the last UserName that logged into the computer,
> > > the last IPAddress the computer had, and the last LoginDate of the computer.
> > >
> > > So results would look like this:
> > >
> > > Login Count 33
> > > UserName John Doe (last user to login)
> > > LoginDate 07/15/2005 (most recent logindate)
> > > IPAddress 10.100.100.12 (Last IP associated with LoginDate)
> > >
> > >
> > > Thanks in advance,
> > > JB
> > >
> > >
> > >
> > >
> > >
> > >
> > >

15 Jul 2005 2:49 PM

Alejandro Mesa

Correction,

select
    t1.ComputerName,
    count(*) as login_count,
    max(t3.IpAddress) as last_IpAddress,
    max(t3.UserName) as last_UserName,
    max(t3.max_LoginDate) as last_LoginDate
from
    t1
    left outer join
    (
    select
        t1.ComputerName,
        t1.IpAddress,
        t1.UserName,
        t1.LoginDate
    from
        t1
        inner join
        (
        select
            ComputerName,
            max(LoginDate) as max_LoginDate
        from
            t1
        group by
            ComputerName
        ) as t2
        on t1.ComputerName = t2.ComputerName
        and t1.LoginDate = t2.max_LoginDate
    ) as t3
    on t1.ComputerName = t3.ComputerName
    and t1.LoginDate = t3.max_LoginDate
group by
    t1.ComputerName
go

AMB

Show quote

"Alejandro Mesa" wrote:

> You are right. Try:
>
> select
>     t1.ComputerName,
>     count(*) as login_count,
>     max(t3.IpAddress) as last_IpAddress,
>     max(t3.UserName) as last_UserName,
>     max(t3.max_LoginDate) as last_LoginDate
> from
>     t1
>     left outer join
>     (
>     select
>         ComputerName,
>         IpAddress,
>         UserName,
>         LoginDate
>     from
>         t1
>         inner join
>         (
>         select
>             ComputerName,
>             max(LoginDate) as max_LoginDate
>         from
>             t1
>         group by
>             ComputerName
>         ) as t2
>         on t1.ComputerName = t2.ComputerName
>         and t1.LoginDate = t2.max_LoginDate
>     ) as t3
>     on t1.ComputerName = t3.ComputerName
>     and t1.LoginDate = t3.max_LoginDate
> group by
>     t1.ComputerName
> go
>
>
> AMB
>
>
>
> "TheBook" wrote:
>
> > This one is close.
> >
> > It gives me the last IP, Username, computername, Logindate correctly,
> >
> > But the count is way off.
> >
> > It's showing a count of 1 or 2 when I can see for a fact that the comptuer
> > has been logged into about 40 times(by many different people)times since I
> > started the log file.
> >
> > Here's a sample of the data:
> > 10.180.100.45 Rehab      NURWIRE99                      2005-07-14
> > 10.180.100.45 FernandB NURWIRE99               2005-07-14
> > 10.180.100.45 FernandB   NURWIRE99               2005-07-14
> > 10.180.100.43 blanshaj    NURWIRE99               2005-07-13
> > 10.180.100.43 FernandB   NURWIRE99               2005-07-11
> >
> > So I'm hoping to get a result that looks like:
> >
> > Count IPAddress           UserName Computername Logindate
> >    5       10.180.100.45   Rehab       NurWire99         2005-07-14
> >
> > So a total of 5 people have logged into this machiine since whenever, and it
> > shows the last known IPAddress, UserName, Computername, and LoginDate
> >
> > Thanks again,
> > JB
> >
> >
> >
> > "Alejandro Mesa" wrote:
> >
> > > Try,
> > >
> > > -- supposing there are not more than one login at the same time
> > > select
> > >     t1.ComputerName,
> > >     count(*) as login_count,
> > >     max(IpAddress) as last_IpAddress,
> > >     max(UserName) as last_UserName,
> > >     max(max_LoginDate) as last_LoginDate
> > > from
> > >     t1
> > >     inner join
> > >     (
> > >     select
> > >         ComputerName,
> > >         max(LoginDate) as max_LoginDate
> > >     from
> > >         t1
> > >     group by
> > >         ComputerName
> > >     ) as t2
> > >     on t1.ComputerName = t2.ComputerName
> > >     and t1.LoginDate = t2.max_LoginDate
> > > group by
> > >     t1.ComputerName
> > > go
> > >
> > >
> > > AMB
> > >
> > > "TheBook" wrote:
> > >
> > > > I'm not a programmer, so I need help with a query.
> > > >
> > > > Table:
> > > > IpAddress Char 23
> > > > UserName char 20
> > > > ComputerName char 20
> > > > LoginDate datetime
> > > >
> > > >
> > > > Many people different people have logged into any one computer many times.
> > > >
> > > > I'm looking to get a Count of the number of times a computer has been logged
> > > > into, displayed along with the last UserName that logged into the computer,
> > > > the last IPAddress the computer had, and the last LoginDate of the computer.
> > > >
> > > > So results would look like this:
> > > >
> > > > Login Count 33
> > > > UserName John Doe (last user to login)
> > > > LoginDate 07/15/2005 (most recent logindate)
> > > > IPAddress 10.100.100.12 (Last IP associated with LoginDate)
> > > >
> > > >
> > > > Thanks in advance,
> > > > JB
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >

15 Jul 2005 1:17 PM

Kuido Külm via SQLMonster.com

select top 1 user_name, IpAddress, logindate, ( select count(*) from
searchtable ) as total
from searchtable
order by login_date desc

Kuido

TheBook wrote:
Show quote

>I'm not a programmer, so I need help with a query.
>
>Table:
>IpAddress Char 23
>UserName char 20
>ComputerName char 20
>LoginDate datetime
>
>Many people different people have logged into any one computer many times.
>
>I'm looking to get a Count of the number of times a computer has been logged
>into, displayed along with the last UserName that logged into the computer,
>the last IPAddress the computer had, and the last LoginDate of the computer.
>
>So results would look like this:
>
>Login Count 33
>UserName John Doe (last user to login)
>LoginDate 07/15/2005 (most recent logindate)
>IPAddress 10.100.100.12 (Last IP associated with LoginDate)
>
>Thanks in advance,
>JB

--
Message posted via http://www.sqlmonster.com