SQL Query Help

WizZard

I'm not the best SQL guy ever so need help with this one.

I have a table which logs user access to an application

Table Fields: RecId, UserID, LogIn_Time, User_IP

What I want to find is those users that have logged in from the same IP, but using a different UserID.

My brain has ground to a halt today
Anyone got any help?

akari no ryu

WizZard wrote:

I'm not the best SQL guy ever so need help with this one.

I have a table which logs user access to an application

Table Fields: RecId, UserID, LogIn_Time, User_IP

What I want to find is those users that have logged in from the same IP, but using a different UserID.

My brain has ground to a halt today
Anyone got any help?

SELECT UNIQUE(UserID) FROM <tablename> GROUP BY (User_IP)

I think.

WizZard

Doesn't seem to work for me.

Forgot to mention that it's SQL Server 2000 (so T-SQL).

I've tried

SELECT DISTINCT UserID FROM <tablename> ORDER BY User_IP

But that just gives me a list of the unique logins, not the ones who've logged in from the same IP but using a different UserID

blorg

SELECT UserIP, COUNT(UserID)
FROM LoginTable
GROUP BY UserIP
HAVING COUNT (UserID) > 1

...will give you a list of userIPs with a count of the number of different IDs used on each one (where it is more than one). Let me know if you need more.

blorg

Leading on from that, this will give you just the list of users:

SELECT userID
FROM <table>
WHERE UserIP IN (
	SELECT UserIP
	FROM <table>
	GROUP BY UserIP
	HAVING COUNT (userID) > 1)

stevenmu

You could also do

SELECT    distinct(t1.userid)
FROM         <tablename> t1,<tablename> t2
WHERE t1.user_ip = t2.user_ip and t1.userid<> t2.userid

or it's equivelent,

SELECT DISTINCT t1.userid
FROM <tablename> t1 INNER JOIN
<tablename> t2 ON t1.user_ip = t2.user_ip AND t1.userid<> t2.userid

blorg

Having read stevenmu's post, there should also be a distinct in mine (first one culls the recordset, second one may improve performance):

SELECT DISTINCT userID
FROM <table>
WHERE UserIP IN (
SELECT DISTINCT UserIP
FROM <table>
GROUP BY UserIP
HAVING COUNT (userID) > 1)

WizZard

Thanks for your help blorg & stevenmu!

That last query still returns way more than I expected. I'll have a look at the results and see if there's anything I can do to cut them down.

Thanks again.

---edit---
OK, here's my query. What I've done is mapped the user's ID to their company name, which is more important in the context of this query.
If there's any way of speeding this up or making it better(code-wise) it would be great, bearing in mind it is not business critical - more of a report.

SELECT DISTINCT U.CompID, L.UserIP
INTO #tempTable
FROM Logins L LEFT OUTER JOIN Users U
   ON L.UserID = U.UserId
WHERE UserIp <> '<internal test ip>'

SELECT DISTINCT C.CompanyName, U.UserIP
FROM #temptable U
LEFT OUTER JOIN Company C 
   ON C.CompID = U.CompId
WHERE U.UserIP IN (
	SELECT DISTINCT UserIP
	FROM #temptable
	GROUP BY UserIP
	HAVING COUNT (CompId) > 1)
ORDER BY C.CompanyName, U.UserIP 

DROP TABLE #temptable

bonkey

I'd do something like the following.

(Disclaimer: I don't have an MSSQL install to hand, so I can't verify that the syntax is correct)

SELECT DISTINCT a.UserIP
              , a.userID
FROM <mytable> a
INNER JOIN ( SELECT   UserIP
                  ,   COUNT(DISTINCT UserID) AS usercount 
             FROM     <mytable> 
             GROUP BY UserIp 
             HAVING COUNT(DISTINCT UserID) > 1
           ) b
  ON a.userIP = b.userIP
ORDER BY a.userIP
       , a.userID

basically, the nested SELECT gives me all IPs that exist with more than one UserID against them.
By joining this back against the table in question, I can filter to only get the ID/IP combinations
where the IP has been used by more than one ID.

Should do the trick.

Instead of nesting the query, I could also do something like the following :

SELECT DISTINCT a.UserIP
              , a.userID
FROM <mytable> a
WHERE UserIP IN ( SELECT   UserIP
                  FROM     <mytable> 
                  GROUP BY UserIp 
                  HAVING COUNT(DISTINCT UserID) > 1
                ) 
ORDER BY a.userIP
       , a.userID

Here, I'm being a bit sneaky, using a HAVING and GROUP BY without having any aggregate functions in my SELECT, but I believe its valid.

jc

WizZard

bonkey, your code gives me back almost 90% less results than my code posted previously. Should it?

In any case the damn parameters have just been changed. I now have to find different users (well, companies from users ID) that have been logging in from the same IP and where their company is located in different countries (country ID is stored in the Company table). Aaaargh.

I was flummoxed this morning and now am flummoxed again.

blorg

I'd need a schema and preferably a data sample or access, my powers of hurried abstraction only go so far ;-)

amen

Right I'm assuming your table is called user activity and what you want is a list of all ips that have multiple different user ids. I used the query below.
I had 4 data rows as below. As you can see Users 102/104 are both at IP192.168.1.2

RecID     UserID    Login_Time                        User_IP
1	101	2006-03-03 08:39:53.607	192.168.1.1.
1	102	2006-03-03 08:39:53.607	192.168.1.2.
1	103	2006-03-03 08:39:53.607	192.168.1.3.
1	104	2006-03-03 08:39:53.607	192.168.1.2.

So when I run the query I get back two rows

User_IP                UserID
192.168.1.2.	102
192.168.1.2.	104

Heres the query

SELECT U1.USER_IP,U1.USERID
FROM
UserActivity U1 JOIN UserActivity U2
ON U1.User_IP = U2.User_IP
GROUP BY U1.USER_IP,U1.USERID
HAVING COUNT(U2.USER_IP)>1

Bascially I'm joining the table to itself and using the HAVING clause to eliminate the aggregrates that I don't want.

As for the company bit all you need to do is join the table that contains the userid/company assignment to the above query.
( your userid/company assignment table is of course different from your company details table ? ) If you need more help repost or provide some sample data with the what you would expect the results to be and why

When writing this sort of SQL I often find it handy to create a temp table or just use a small sample of data so I know what the results should be before running on a large data set. To do this for you I used the code below

Create Table #UserActivity
(
RecId INT,
UserId INT,
LogIn_Time DATETIME,
User_IP VARCHAR(15)

)


INSERT #UserActivity SELECT 1,101,GETDATE(),'192.168.1.1.'
INSERT #UserActivity SELECT 1,102,GETDATE(),'192.168.1.2.'
INSERT #UserActivity SELECT 1,103,GETDATE(),'192.168.1.3.'
INSERT #UserActivity SELECT 1,104,GETDATE(),'192.168.1.2.'

SELECT U1.USER_IP,U1.USERID
FROM
#UserActivity U1 JOIN #UserActivity U2
ON U1.User_IP = U2.User_IP
GROUP BY U1.USER_IP,U1.USERID
HAVING COUNT(U2.USER_IP)>1

SELECT * FROM #UserActivity

DROP TABLE #UserActivity

bonkey

WizZard wrote:

bonkey, your code gives me back almost 90% less results than my code posted previously. Should it?

I give up. Should it?

One or two posts ago, you were saying that queries were returning way more than you expected. Now you're saying mine produces way less then these earlier ones....so way less than the way more than you expected!!!!

Only you can tell whether or not its right. I believe it should be.

Having said that...I prefer Amen's query to the ones I offered. Logically, it performs the same operation, and it may even be optimised to the same execution plan, but its a lot neater.

In any case the damn parameters have just been changed. I now have to find different users (well, companies from users ID) that have been logging in from the same IP and where their company is located in different countries (country ID is stored in the Company table). Aaaargh.

Why aargh? Once you can get the Users and companies from what you believed you needed yesterday, its only a small step further to add these additional conditions.

jc

WizZard

OK, thanks guys.

Here is sample table schema and some data (I've used amen's nomenclature)

Create Table UserActivity
(
RecId INT IDENTITY (1, 1),
UserId INT,
LogIn_Time DATETIME,
User_IP VARCHAR(15)

)


INSERT INTO UserActivity (UserId, Login_Time, User_Ip) SELECT 101,GETDATE(),'192.168.1.1'
INSERT INTO UserActivity (UserId, Login_Time, User_Ip) SELECT 102,GETDATE(),'192.168.1.2'
INSERT INTO UserActivity (UserId, Login_Time, User_Ip) SELECT 103,GETDATE(),'192.168.1.3'
INSERT INTO UserActivity (UserId, Login_Time, User_Ip) SELECT 104,GETDATE(),'192.168.1.2'
INSERT INTO UserActivity (UserId, Login_Time, User_Ip) SELECT 105,GETDATE(),'192.168.1.2'
INSERT INTO UserActivity (UserId, Login_Time, User_Ip) SELECT 106,GETDATE(),'192.168.1.4'
INSERT INTO UserActivity (UserId, Login_Time, User_Ip) SELECT 107,GETDATE(),'192.168.1.3'
INSERT INTO UserActivity (UserId, Login_Time, User_Ip) SELECT 103,GETDATE(),'192.168.1.4'
INSERT INTO UserActivity (UserId, Login_Time, User_Ip) SELECT 106,GETDATE(),'192.168.1.1'
INSERT INTO UserActivity (UserId, Login_Time, User_Ip) SELECT 102,GETDATE(),'192.168.1.1'


Create Table UserCompany
(

UserId INT,
CompanyId INT

)

INSERT UserCompany SELECT 101, 1
INSERT UserCompany SELECT 102, 1
INSERT UserCompany SELECT 103, 2
INSERT UserCompany SELECT 104, 2
INSERT UserCompany SELECT 105, 3
INSERT UserCompany SELECT 106, 4
INSERT UserCompany SELECT 107, 3


Create Table Company
(
CompanyId INT,
CompanyName VARCHAR(100),
CountryId INT

)

INSERT Company SELECT 1,'Company A', 10
INSERT Company SELECT 2,'Company B', 20
INSERT Company SELECT 3,'Company C', 20
INSERT Company SELECT 4,'Company D', 30

The SQL I am currently using looks like this

SELECT DISTINCT U.CompanyID, C.CountryID, A.User_IP
INTO #tempTable
FROM UserActivity A LEFT OUTER JOIN UserCompany U
   ON A.UserID = U.UserId
LEFT OUTER JOIN Company C 
   ON U.CompanyId = C.CompanyId;


SELECT C.CompanyName, C.CountryID, M.User_IP
FROM #temptable M
LEFT OUTER JOIN Company C 
   ON M.CompanyId = C.CompanyId
WHERE M.User_IP IN (
	SELECT DISTINCT User_IP
	FROM #temptable
	GROUP BY User_IP
	HAVING COUNT (CompanyId) > 1)
ORDER BY M.User_IP, C.CompanyName;


DROP TABLE #temptable;

That should just get the companies whose user have logged in from the same IPs.

What I want now is to get the companies whose users have logged in from the same IP, but where the company is located in a different country (as shown by the CountryId field in the Company table).

stevenmu

I'm too pi**ed off with sql server at the moment to test this, so it probably won't work, but modifying my earlier query to

SELECT    distinct(ua1.userid)
FROM         UserActivity ua1,UserActivity ua2, UserCompany uc1, UserCompany uc2, Company c1, Company c2
WHERE ua1.user_ip = ua2.user_ip and c1.CountryID <> c2.CountryID and uc1.userid = ua1.userid and uc2.userid = ua2.userid and c1.CompanyID = uc1.CompanyID and c2.CompanyID = uc2.CompanyID

or it's equivelent

SELECT DISTINCT ua1.UserId
FROM         Company c2 INNER JOIN
                      Company c1 ON c2.CountryId <> c1.CountryId INNER JOIN
                      UserActivity ua1 INNER JOIN
                      UserActivity ua2 ON ua1.User_IP = ua2.User_IP INNER JOIN
                      UserCompany uc1 ON ua1.UserId = uc1.UserId INNER JOIN
                      UserCompany uc2 ON ua2.UserId = uc2.UserId ON c1.CompanyId = uc1.CompanyId AND c2.CompanyId = uc2.CompanyId

Actually I did check it out, it returns 101,102,103,104,105,106 and not 107, which I think is correct, I won't even guess at performance for a large set of data, but it's quick for that small amount.