You are an unregistered user, you can register here
Navigation

Information

Site

Donations
If you wish to make a donation you can by clicking the image below.


 
Go Back   The Unreal Admins Page > Forums > Hosted Forums > UTStats > General Chat

Reply
Thread Tools Display Modes
  #1  
Unread 26th January, 2007, 10:26 PM
[BSC]MasterJohnny [BSC]MasterJohnny is offline
Holy Shit!!
 
Join Date: Apr 2005
Location: Hajdúszoboszló, Hungary
Posts: 823
Default Recalculate Flags After New iptocountry Import?

I have just recently updated my UTStats' iptocountry table. Is there any way to 'recalculate' the flags for all players back? (Except for that that the player should rejoin with the same nick.)
Thanks in advance!

Last edited by [BSC]MasterJohnny : 26th January, 2007 at 10:33 PM.
Reply With Quote
  #2  
Unread 29th January, 2007, 05:36 AM
{BL}GypsyAngel[BLA] {BL}GypsyAngel[BLA] is offline
Rampage
 
Join Date: May 2006
Posts: 59
Default

If you held onto your logs backup, you could just upload them to the UT server then reprocess them.

Say, how hard is it to update the IP table? I need to update mine as well.
Reply With Quote
  #3  
Unread 29th January, 2007, 04:16 PM
[BSC]MasterJohnny [BSC]MasterJohnny is offline
Holy Shit!!
 
Join Date: Apr 2005
Location: Hajdúszoboszló, Hungary
Posts: 823
Default

It's not that hard.
Quote:
Originally Posted by [es]Rush
1) Get the latest ip2country database from:
http://ip-to-country.webhosting.info...ountry.csv.zip
2) Unzip it
3) Run csvtosql(-linux86/.exe) in the same directory as the .csv file
4) ip-to-country.sql should be created now!
5) Import ip-to-country.sql in mysql/phpmyadmin
TIP: You will have to redo these steps once in a while to keep your database up-to-date!

8) Your QueryServer should be up and running now!
The needed files can be found here.

Then I just renamed the fields to "ip_to", "ip_from" and "country" and deleted the 2 other fields + renamed the table to uts_ip2country after backuping the old one.


So without backupped logs, is it impossible to rebuild the associated flags?
Reply With Quote
  #4  
Unread 29th January, 2007, 04:24 PM
iDeFiX iDeFiX is offline
Godlike
 
Join Date: Jun 2004
Location: NL
Posts: 369
Default

Use an update query to update the country flags instead of reimporting logs. Some basic SQL knowledge required. I'll have a look when I'm home.
Reply With Quote
  #5  
Unread 30th January, 2007, 03:17 AM
{BL}GypsyAngel[BLA] {BL}GypsyAngel[BLA] is offline
Rampage
 
Join Date: May 2006
Posts: 59
Default

It's very possible, just a little labor intensive.

I suppose if you start by querying for all the records that don't have a country ID listed in it, then pipe that output through a routine that would look up the country ID based on the IP table and fill in the blank, it should work.


I'm a serious noob when it comes to this .php stuff, but I can block out a program in about 30 seconds (I miss AWK).
Reply With Quote
  #6  
Unread 30th January, 2007, 03:24 AM
Matthew Matthew is offline
Holy Shit!!
 
Join Date: Dec 2004
Posts: 2,382
Default

I had to learn MySQL queries and the associated PHP workings to make a script that took inventory of accesses and other stuff for work. I think I have a good enough understanding to try and make an update script. From what I can tell, this really doesn't look that hard... just check if the flag is right, and if not, update the table...


EDIT: Though I must say that doing this recalculation could be a MASSIVE strain on databases with very large numbers of players (such as my own).

Last edited by Matthew : 30th January, 2007 at 03:36 AM.
Reply With Quote
  #7  
Unread 31st January, 2007, 05:40 PM
[BSC]MasterJohnny [BSC]MasterJohnny is offline
Holy Shit!!
 
Join Date: Apr 2005
Location: Hajdúszoboszló, Hungary
Posts: 823
Default

Thanks for your efforts, good luck with it.
Reply With Quote
  #8  
Unread 18th February, 2007, 05:26 PM
[BSC]MasterJohnny [BSC]MasterJohnny is offline
Holy Shit!!
 
Join Date: Apr 2005
Location: Hajdúszoboszló, Hungary
Posts: 823
Default

Any news on this?
Reply With Quote
  #9  
Unread 18th February, 2007, 07:09 PM
Matthew Matthew is offline
Holy Shit!!
 
Join Date: Dec 2004
Posts: 2,382
Default

It's coming along - just slow

(it's really not that complex, I just don't have a lot of time)
Reply With Quote
  #10  
Unread 25th February, 2007, 12:50 AM
Azazel's Avatar
Azazel Azazel is offline
Administrator
 
Join Date: Jul 2002
Location: UK
Posts: 2,408
Default

Ive not even tried this so if someone wants to test, please do it on a backup.

This should make a list of players who have a country of xx and an IP address (xx = country code of people who could not be matched previously).

It then goes through the list, checks the country code again and if there is one, updates the player record with it.

Once again, I've not even tested this so do this on a test/backup

PHP Code:
<?
//include config & functions from the stats
include_once("includes/config.php");
include_once(
"includes/functions.php");

// Query for list of ids where country = xx and player has an IP
$sql_pipcheck "SELECT id, country, ip FROM uts_pinfo WHERE country = 'xx' AND ip > 0";
$q_pipcheck mysql_query($sql_pipcheck) or die(mysql_error());
while (
$r_pipcheck mysql_fetch_array($q_pipcheck)) {
    
    
$playerid $r_pipcheck[id];
    
$playerip $r_pipcheck[ip];
    
    
// Check the players IP against the country database and update record if a country is found
    
$q_playercountry small_query("SELECT country FROM uts_ip2country WHERE $playerip BETWEEN ip_from AND ip_to;");
    IF(
$q_playercountry) {
        
$playercountry strtolower($q_playercountry[country]);
        
mysql_query("UPDATE uts_pinfo SET country = '$playercountry' WHERE id = $playerid") or die(mysql_error());
    }
}
?>
Copy the above code into a file (say flags.php) and put this file in the root of the utstats folder.
__________________

Phoenix Alliance - The Movie
Revisionistic Movie
The Unreal Admins Page - the resource for unreal admins
Reply With Quote
  #11  
Unread 25th February, 2007, 09:22 AM
[BSC]MasterJohnny [BSC]MasterJohnny is offline
Holy Shit!!
 
Join Date: Apr 2005
Location: Hajdúszoboszló, Hungary
Posts: 823
Default

It says:

Code:
Unknown column 'ip' in 'field list'
Reply With Quote
  #12  
Unread 25th February, 2007, 12:00 PM
Azazel's Avatar
Azazel Azazel is offline
Administrator
 
Join Date: Jul 2002
Location: UK
Posts: 2,408
Default

Indeed, thats what you get for coding after 2 bottles of wine

Try this instead:

PHP Code:
<?
//include config & functions from the stats
include_once("includes/config.php");
include_once(
"includes/functions.php");

// Query for list of ids where country = xx and player has an IP
$sql_pipcheck "SELECT DISTINCT pid, country, ip FROM uts_player WHERE country = 'xx' AND ip > 0";

$q_pipcheck mysql_query($sql_pipcheck) or die(mysql_error());
while (
$r_pipcheck mysql_fetch_array($q_pipcheck)) {
    
    
$playerid $r_pipcheck[pid];
    
$playerip $r_pipcheck[ip];
    
     
// Check the players IP against the country database and update record if a country is found
    
$q_playercountry small_query("SELECT country FROM uts_ip2country WHERE $playerip BETWEEN ip_from AND ip_to;");
    IF(
$q_playercountry) {
        
$playercountry strtolower($q_playercountry[country]);
        
mysql_query("UPDATE uts_pinfo SET country = '$playercountry' WHERE id = $playerid") or die(mysql_error());
        
mysql_query("UPDATE uts_player SET country = '$playercountry' WHERE pid = $playerid") or die(mysql_error());
        echo 
"Updated Player ID $playerid to country $playercountry <br />";
    }
}
?>
__________________

Phoenix Alliance - The Movie
Revisionistic Movie
The Unreal Admins Page - the resource for unreal admins

Last edited by Azazel : 25th February, 2007 at 12:03 PM.
Reply With Quote
  #13  
Unread 25th February, 2007, 12:06 PM
Azazel's Avatar
Azazel Azazel is offline
Administrator
 
Join Date: Jul 2002
Location: UK
Posts: 2,408
Default

I've done a quick test of the above and it should work fine ...
__________________

Phoenix Alliance - The Movie
Revisionistic Movie
The Unreal Admins Page - the resource for unreal admins
Reply With Quote
  #14  
Unread 25th February, 2007, 12:26 PM
[BSC]MasterJohnny [BSC]MasterJohnny is offline
Holy Shit!!
 
Join Date: Apr 2005
Location: Hajdúszoboszló, Hungary
Posts: 823
Default

Yes, it works, thanks very much!
Reply With Quote
  #15  
Unread 25th February, 2007, 04:11 PM
Adrian[ADIES]'s Avatar
Adrian[ADIES] Adrian[ADIES] is offline
Godlike
 
Join Date: May 2005
Location: UK NorthWest
Posts: 252
Default

Cool Thanks Azazel it worked for me.
__________________
www.adies.net - Dedicated to Strangelove-CTF.

Download my UT map CTF-Microhec33 CTF-Microhec33.zip :::: Read The Review
Reply With Quote
  #16  
Unread 25th February, 2007, 10:11 PM
Matthew Matthew is offline
Holy Shit!!
 
Join Date: Dec 2004
Posts: 2,382
Default

Two bottles Azazel?

Yeah...
Reply With Quote
  #17  
Unread 15th March, 2007, 12:18 AM
{BL}GypsyAngel[BLA] {BL}GypsyAngel[BLA] is offline
Rampage
 
Join Date: May 2006
Posts: 59
Default

This is sweet. Azazel, you're a god!

Next question, does anyone know how to encode the IPs into the "values" in the SQL file?

Once I know this, I can update the file from my UTStats listing that show anyone without a flag. Take their IP, run it through a reverse IP lookup. That gives me an ISP. I then do a WhoIs to get the entire range that that IP resides in. I get the player's country by asking them. Once I encode it, voila! Anyone else coming in from that IP block is covered.

(if only every ISP would enter their WhoIs data!)
Reply With Quote
  #18  
Unread 15th March, 2007, 06:10 AM
[BSC]MasterJohnny [BSC]MasterJohnny is offline
Holy Shit!!
 
Join Date: Apr 2005
Location: Hajdúszoboszló, Hungary
Posts: 823
Default

You can use this to convert IPs to decimal:

http://www.kloth.net/services/iplocate.php
Reply With Quote
  #19  
Unread 28th May, 2007, 04:14 PM
Adrian[ADIES]'s Avatar
Adrian[ADIES] Adrian[ADIES] is offline
Godlike
 
Join Date: May 2005
Location: UK NorthWest
Posts: 252
Default

Sorry feels like a double post but seemed better than making a 3rd thread
I also posted the download in Player Merging Query thread.

I have securely integrated Azazel's
Player Merging Query and Recalculate Flags After New iptocountry Import
with the UTSTATS ADMIN page and make them MySQL5 compatible.

Here is a download with the slightly modified scripts and install instructions:
http://www.adies.net/downloads/utsta...s-admin001.zip
__________________
www.adies.net - Dedicated to Strangelove-CTF.

Download my UT map CTF-Microhec33 CTF-Microhec33.zip :::: Read The Review
Reply With Quote
  #20  
Unread 28th May, 2007, 09:44 PM
[BSC]MasterJohnny [BSC]MasterJohnny is offline
Holy Shit!!
 
Join Date: Apr 2005
Location: Hajdúszoboszló, Hungary
Posts: 823
Default

Nice, thanks.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:08 AM.


 

All pages are copyright The Unreal Admins Page.
You may not copy any pages without our express permission.