Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Aggregate comparisons??

    I have implemented a login audit on a particular system which catches the users login details, including their application logon name and NT username.

    What I want to do is report on users who have logged on to the software using someone else's workstation (i.e. logged on to more than one workstation).

    Here's some sample stuff to play with
    Code:
    DECLARE @logins table (
        loginName  char(20)
      , ntUsername char(25)
      , loginDate  datetime
    )
    
    --Insert test data. Please note that loginName and ntUsername are rarely the same
    INSERT INTO @logins (loginName, ntUsername, loginDate)
    SELECT 'Amy', 'Amy', '20070101' UNION
    SELECT 'Amy', 'Amy', '20070102' UNION
    SELECT 'Amy', 'Amy', '20070103' UNION
    SELECT 'Bob', 'Bob', '20070101' UNION
    SELECT 'Bob', 'Bob', '20070102' UNION
    SELECT 'Bob', 'Amy', '20070103' UNION --Bob has logged on using 2 different NT accounts
    SELECT 'Cal', 'Cal', '20070102' UNION
    SELECT 'Cal', 'Amy', '20070102' UNION --So has cal
    SELECT 'Dom', 'Dom', '20070102' UNION
    SELECT 'Dom', 'Dom', '20070102'
    Any ideas? I just can't think of the logic needed to get what I want.

    Any extra info needed - just ask!
    Cheers
    George
    Home | Blog

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    The keyword you're looking for is HAVING.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I hope that the purpose of this is to work out who gets a b*llocking for sharing\ using unauthorised usernames.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ivon
    The keyword you're looking for is HAVING.
    In conjunction with BLAH BLAH
    Last edited by pootle flump; 12-12-07 at 09:41.

  5. #5
    Join Date
    Nov 2002
    Posts
    272
    Yeah. I didn't think he'd need *that* much help

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ivon
    Yeah. I didn't think he'd need *that* much help
    You are quite correct - George is a notorious free loader on this site and it is about time he did some work for himself.

    Thanks for the heads up

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I know all about HAVING thanks... but the only way I've got my result is completely nacky...

    I'm having a bad SQL day - send me home
    Code:
    SELECT z.loginName
         , z.ntUsername
    FROM (  
      SELECT  loginName
      FROM (
        SELECT loginName
             , ntUsername
        FROM   @logins
        GROUP
            BY loginName
             , ntUsername
        ) As x
      GROUP
          BY loginName
      HAVING Count(loginName) > 1
      ) As y
    INNER
     JOIN @logins As z
       ON z.loginName = y.loginName
    GROUP
        BY z.loginName
         , z.ntUsername
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    I hope that the purpose of this is to work out who gets a b*llocking for sharing\ using unauthorised usernames.
    Correct.


    too short
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Check out the COUNT() function in BoL. You are missing something.

    Sorry Ivon

  10. #10
    Join Date
    Nov 2002
    Posts
    272

    George, the other keyword you're looking for is DISTINCT.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I've posted a solution above, but I'm not happy with it.
    Why would I use DISTINCT?
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The very inner select can be changed from
    Code:
            SELECT loginName
                 , ntUsername
            FROM   @logins
            GROUP
                BY loginName
                 , ntUsername
    To
    Code:
            SELECT DISTINCT
                   loginName
                 , ntUsername
            FROM   @logins
    point taken.
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh:
    Code:
    SELECT    *
    FROM    @logins
    WHERE    loginname <> ntusername

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Git.




    EDIT: I would have added a winky but it would have highlighted my response
    Last edited by gvee; 12-12-07 at 10:25.
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT    DISTINCT nt.loginname
            , nt.ntusername
    FROM    @logins AS nt
    INNER JOIN 
            (SELECT    loginname    
            FROM    @logins
            GROUP BY loginname
            HAVING    COUNT(DISTINCT ntusername) > 1) AS logs
    ON    logs.loginname = nt.loginname

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •