June 23, 2013 at 2:15 am
I am in need of functionality for getting the Country name from the IP.
I have to show the country in front of every unique visitor I have on my site. (Previously, I asked a question regarding a similar approach, but I changed it now.)
The output I expect would be something as follows:-
Country Visited | No. of Times
_______________________________
United States | 482
Korea | 213
Pakistan | 123
....
The output is showing that 482 Unique visitors have come from US, 213 from Korea, and so on.
To achieve this, I divided this task into two part, first getting all the IPs of the Unique user and the second part is to search two tables with country/IP information to get the country.
We then aggregate data based on the top output table shown. I am going to show the queries I wrote for each part. Below, I have shown the Structure and Sample Data of the tables used.
First Part - Getting Unique Visitors on my site
------------------------------------------------
SELECT a.uid, a.hostname, COUNT( * ) AS times
FROM login_activity a
WHERE a.uid =83
GROUP BY a.hostname
ORDER BY times
This shows the output as below. It means that the USer with ID 83 has 80 unique visits to the site.:-
uid | hostname | times
-------------------------------------
83 |157.191.122.36 | 80
This is wrong as I should be getting the no. of hits for each IP. But somehow I am unable to do so.
Second Part - Finding the country based on IP
---------------------------------------------
SELECT cc, cn
FROM ip
NATURAL JOIN cc
WHERE INET_ATON( "157.191.122.36" )
BETWEEN START AND END
This gives me the output as:-
cc | cn
--------
US | United States
This is correct, as it's giving me the correct Country based on the IP provided.
With the help of these three tables and two queries I've shown on top, I need to show the following result:-
Country Visited | No. of Times
_______________________________
United States | 482
Korea | 213
Pakistan | 123
....
I will be very thankful if you can help me in any way ...
Appendix (Structure and Data of the Tables)
-------------------------------------------
**login_activity**
---
(Structure)
CREATE TABLE IF NOT EXISTS `mslop_login_activity` (
`aid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The primary identifier for an activity (session).',
`uid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The mslop_users.uid corresponding to a session, or 0 for anonymous user.',
`host_user_agent` varchar(256) NOT NULL DEFAULT '' COMMENT '$_SERVER["HOST_USER_AGENT"] string. This can be used with get_browser() in PHP.',
`hostname` varchar(128) NOT NULL DEFAULT '' COMMENT 'The IP address that was used for this session.',
`timestamp` int(11) NOT NULL DEFAULT '0' COMMENT 'The UNIX timestamp when the session was started.',
PRIMARY KEY (`aid`),
KEY `aid` (`aid`),
KEY `uid` (`uid`),
KEY `timestamp` (`timestamp`)
);
(Data)
INSERT INTO `mslop_login_activity` (`aid`, `uid`, `host_user_agent`, `hostname`, `timestamp`) VALUES
(1, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.8; rv:19.0) Gecko/20100101 Firefox/19.0', '172.24.1.143', 1363038356),
(873, 4, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1369773601),
(883, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1369774959),
(893, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.31 (KHTML, like Gecko) Chrome/26.0.1410.64 Safari/537.31', '157.191.122.36', 1369818602),
(903, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB7.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1369838690),
(913, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB7.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0; AskTbORJ/5.15.23.36191)', '157.191.122.36', 1369840224),
(923, 83, 'Mozilla/5.0 (Windows NT 6.1; rv:21.0) Gecko/20100101 Firefox/21.0', '157.191.122.36', 1369841748),
(933, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370255417),
(943, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1370258059),
(953, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1370258060),
(963, 93, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; AskTbORJ/5.15.15.36191)', '157.191.122.36', 1370299827),
(973, 93, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; AskTbORJ/5.15.15.36191)', '157.191.122.36', 1370299852),
(983, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370299899),
(993, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370299920),
(1003, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370299995),
(1013, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370319811),
(1023, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370319835),
(1033, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370319939),
(1043, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370320312),
(1053, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370320640),
(1063, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370320713),
(1073, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370363702),
(1083, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370363987),
(1093, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370364021),
(1103, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370364028),
(1113, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370365896),
(1123, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370366087),
(1133, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370367070),
(1143, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370368567),
(1153, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.93 Safari/537.36', '157.191.122.36', 1370368575),
(1163, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370379345),
(1173, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB7.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370427302),
(1183, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; .NET CLR 1.1.4322)', '157.191.122.36', 1370503422),
(1193, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1370534329),
(1203, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370866207),
(1213, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; .NET CLR 1.1.4322)', '157.191.122.36', 1370871353),
(1223, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; chromeframe/26.0.1410.43; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1370877702),
(1233, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371015245),
(1243, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; Tablet PC 2.0)', '157.191.122.36', 1371069245),
(1253, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1371117509),
(1263, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371137716),
(1273, 83, 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0)', '157.191.122.36', 1371197327),
(1283, 83, 'Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371427531),
(1293, 83, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1371552899),
(1303, 83, 'Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E)', '157.191.122.36', 1371635304),
(1313, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1371770752),
(1323, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1371771394),
(1333, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371771647),
(1343, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '157.191.122.36', 1371772611),
(1353, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '157.191.122.36', 1371772626),
(1363, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.110 Safari/537.36', '157.191.122.36', 1371772931),
(1373, 93, 'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SIMBAR={37EF0ACB-CCB3-11E2-9611-E006E6BAE768}; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET4.0C; .NET4.0E; AskTbORJ/5.15.15.36191)', '157.191.122.36', 1371782438),
(1383, 93, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_6_8) AppleWebKit/534.59.8 (KHTML, like Gecko) Version/5.1.9 Safari/534.59.8', '157.191.122.36', 1371782562),
(1393, 3, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.116 Safari/537.36', '157.191.122.36', 1371790767),
(1403, 83, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_2) AppleWebKit/536.26.17 (KHTML, like Gecko) Version/6.0.2 Safari/536.26.17', '157.191.122.36', 1371834780);
**cc**
-------
(Structure)
CREATE TABLE cc (
ci TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
cc CHAR(2) NOT NULL,
cn VARCHAR(50) NOT NULL
);
(Data)
Since it's large, you can download it through this link :-
http://d.pr/f/N3KT
**ip**
------
(Structure)
CREATE TABLE ip (
start INT UNSIGNED NOT NULL,
end INT UNSIGNED NOT NULL,
ci TINYINT UNSIGNED NOT NULL
);
(Data)
Since it's large, you can download it through this link:-
http://d.pr/f/wcEy.
I apologize for this long post and will be very thankful for your help.
June 23, 2013 at 2:35 am
Hi!
Happy to take a look but before I do can I confirm you are using MS-SQL as, in your sample, you use a natural join which to the best of my knowledge is supported only in PostgreSQL, MySQL and Oracle (also, the format for creating the table is not MS-SQL)
James
June 23, 2013 at 6:49 am
Hi,
I really appreciate your assistance. I am using MySQL with PHP.
SELECT cn As 'Country Visited', count(0) AS 'No. of Times'
FROM mslop_login_activity a
INNER JOIN ip ON ( INET_ATON(a.hostname) BETWEEN START AND END )
NATURAL JOIN CC
GROUP BY cn
I did come up with this query but it's giving me a constant number of Visits.
For e.g., I tried running the script with this IP 157.191.122.36, instead of INET_ATON(a.hostname) (as this IP address is the most common in the login_activity table) and the Query gave me
United States | 208
. I then added sample Netherlands IP of (5.10.88.12) as a test in the login_activity table and the correct response should have been
Netherlands | 3,
but it gave me
Netherlands | 280
. So the number 280 is coming in regardless of the IP I put in.
I'll be very thankful if you could help in any way or may be devise a correct change in this query.
Many Thanks.
June 23, 2013 at 7:51 am
famaash (6/23/2013)
Hi,I really appreciate your assistance. I am using MySQL with PHP..
This forum is Microsoft SQL Server.
here is some code that may give some ideas...remember this code is for Microsoft SQL Server
USE [tempdb]
GO
CREATE TABLE [dbo].[MSA]
(
[id] [int] NULL,
[u_id] [int] NULL,
[shost_name] [varchar](128) NULL,
[time_stamp] [bigint] NULL
)
GO
INSERT INTO MSA ( id,u_id,shost_name,time_stamp)
SELECT '1','3',' 172.24.1.143','1363038356' UNION ALL
SELECT '873','4',' 157.191.122.36','1369773601' UNION ALL
SELECT '883','83',' 157.191.122.36','1369774959' UNION ALL
SELECT '893','83',' 157.191.122.36','1369818602' UNION ALL
SELECT '903','83',' 157.191.122.36','1369838690' UNION ALL
SELECT '913','83',' 157.191.122.36','1369840224' UNION ALL
SELECT '923','83',' 157.191.122.36','1369841748' UNION ALL
SELECT '933','83',' 157.191.122.36','1370255417' UNION ALL
SELECT '943','83',' 157.191.122.36','1370258059' UNION ALL
SELECT '953','83',' 157.191.122.36','1370258060' UNION ALL
SELECT '963','93',' 157.191.122.36','1370299827' UNION ALL
SELECT '973','93',' 157.191.122.36','1370299852' UNION ALL
SELECT '983','93',' 157.191.122.36','1370299899' UNION ALL
SELECT '993','93',' 157.191.122.36','1370299920' UNION ALL
SELECT '1003','93',' 157.191.122.36','1370299995' UNION ALL
SELECT '1013','93',' 157.191.122.36','1370319811' UNION ALL
SELECT '1023','3',' 157.191.122.36','1370319835' UNION ALL
SELECT '1033','93',' 157.191.122.36','1370319939' UNION ALL
SELECT '1043','83',' 157.191.122.36','1370320312' UNION ALL
SELECT '1053','93',' 157.191.122.36','1370320640' UNION ALL
SELECT '1063','3',' 157.191.122.36','1370320713' UNION ALL
SELECT '1073','83',' 157.191.122.36','1370363702' UNION ALL
SELECT '1083','83',' 157.191.122.36','1370363987' UNION ALL
SELECT '1093','3',' 157.191.122.36','1370364021' UNION ALL
SELECT '1103','3',' 157.191.122.36','1370364028' UNION ALL
SELECT '1113','83',' 157.191.122.36','1370365896' UNION ALL
SELECT '1123','93',' 157.191.122.36','1370366087' UNION ALL
SELECT '1133','93',' 157.191.122.36','1370367070' UNION ALL
SELECT '1143','3',' 157.191.122.36','1370368567' UNION ALL
SELECT '1153','3',' 157.191.122.36','1370368575' UNION ALL
SELECT '1163','83',' 157.191.122.36','1370379345' UNION ALL
SELECT '1173','83',' 157.191.122.36','1370427302' UNION ALL
SELECT '1183','83',' 157.191.122.36','1370503422' UNION ALL
SELECT '1193','93',' 157.191.122.36','1370534329' UNION ALL
SELECT '1203','83',' 157.191.122.36','1370866207' UNION ALL
SELECT '1213','83',' 157.191.122.36','1370871353' UNION ALL
SELECT '1223','83',' 157.191.122.36','1370877702' UNION ALL
SELECT '1233','83',' 157.191.122.36','1371015245' UNION ALL
SELECT '1243','83',' 157.191.122.36','1371069245' UNION ALL
SELECT '1253','83',' 157.191.122.36','1371117509' UNION ALL
SELECT '1263','83',' 157.191.122.36','1371137716' UNION ALL
SELECT '1273','83',' 157.191.122.36','1371197327' UNION ALL
SELECT '1283','83',' 157.191.122.36','1371427531' UNION ALL
SELECT '1293','83',' 157.191.122.36','1371552899' UNION ALL
SELECT '1303','83',' 157.191.122.36','1371635304' UNION ALL
SELECT '1313','93',' 157.191.122.36','1371770752' UNION ALL
SELECT '1323','93',' 157.191.122.36','1371771394' UNION ALL
SELECT '1333','3',' 157.191.122.36','1371771647' UNION ALL
SELECT '1343','3',' 157.191.122.36','1371772611' UNION ALL
SELECT '1353','3',' 157.191.122.36','1371772626'
--=== following may get you started
SELECT u_id, shost_name, COUNT(DISTINCT time_stamp) AS visits
FROM MSA
GROUP BY u_id, shost_name
ORDER BY u_id, shost_name
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 23, 2013 at 9:06 am
Thanks a lot Livingston,
I changed your query to
SELECT uid, hostname, COUNT(DISTINCT timestamp) AS visits
FROM mslop_login_activity
GROUP BY uid, hostname
ORDER BY uid, hostname
It gave me following results:-
uid | hostname | visits
1 | 172.24.1.143 | 3
1 | 172.24.1.161 | 1
2 | 172.17.77.253 | 1
2 | 172.24.1.244 | 1
3 | 157.191.122.36 | 20
3 | 172.24.1.143 | 18
3 | 172.24.1.161 | 10
3 | 172.24.1.166 | 8
4 | 157.191.122.36 | 11
4 | 172.17.64.224 | 2
4 | 172.17.64.94 | 2
4 | 172.17.65.129 | 1
4 | 172.17.66.44 | 1
4 | 172.17.71.161 | 1
4 | 172.17.73.253 | 1
4 | 172.17.76.74 | 2
4 | 172.24.1.161 | 4
4 | 172.24.1.163 | 2
4 | 172.24.1.243 | 6
4 | 172.24.1.244 | 1
4 | 172.26.2.179 | 1
35 | 172.24.1.143 | 2
64 | 172.24.1.143 | 1
65 | 172.24.1.161 | 2
73 | 157.191.122.36 | 8
83 | 157.191.122.36 | 77
83 | 5.10.88.12 | 2
83 | 5.10.88.14 | 1
93 | 157.191.122.36 | 15
As you can see that Hostnames, IPs are still being duplicated, do you think we can tweak the query in such a way that all the common Hostnames group together and give the total number of Occurrences of each as a total no of visits?
Thanks.
June 23, 2013 at 10:50 am
do you mean like this (based on my set up code)
SELECT shost_name, COUNT(DISTINCT time_stamp) AS visits
FROM MSA
GROUP BY shost_name
ORDER BY shost_name
if not correct...please alter my insert script to represent what you want...and post back.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 23, 2013 at 11:19 am
Hi Livingston,
Thank you for your reply.
This is what I made uptill now:-
SELECT cc.cn
, a.visits
FROM (
SELECT hostname
, COUNT( DISTINCT TIMESTAMP ) AS visits
FROM mslop_login_activity
GROUP BY hostname
) a
JOIN ip
ON INET_ATON( a.hostname ) BETWEEN ip.START AND ip.END
JOIN cc
ON cc.ci=ip.ci
ORDER BY visits DESC
LIMIT 0 , 30
It is fine, but with one problem.
It lists duplicate countries. It only combines IPs. What this is doing is that it matches all the similar IPs and convert them into countries. But what if there is a different IP which corresponds to the same country? It makes another row for it and lists the same country with a new number.
What I am getting is:-
Country Visited | Total Visits
_______________________________
United States | 482
Korea | 213
Pakistan | 123
Cuba | 83
France | 53
Netherlands | 13
United States | 1
United States | 2
Netherlands | 1
Netherlands | 1
Pakistan | 10
....
What I need is:-
Country Visited | Total Visits
_______________________________
United States | 485
Korea | 213
Pakistan | 123
Cuba | 83
France | 53
Netherlands | 12
Pakistan | 133
....
Any help please?
Thanks.
June 23, 2013 at 11:37 am
have you tried grouping by cc.cn instead of hostname?
if you cant make it work, then we need details (create table / insert data scripts) for the other tables...in MS SQL format.
good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 23, 2013 at 1:25 pm
This is what worked for me:-
SELECT cc.cn
, sum(a.visits) as total_visits
FROM (
SELECT hostname
, COUNT( DISTINCT TIMESTAMP ) AS visits
FROM mslop_login_activity
GROUP BY hostname
) a
JOIN ip
ON INET_ATON( a.hostname ) BETWEEN ip.START AND ip.END
JOIN cc
ON cc.ci=ip.ci
GROUP BY cc.cn
ORDER BY total_visits DESC
LIMIT 0 , 30
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply