I know this post is hugh, but it's a very complex file that you need a manual to get working.
What is this IP list?
It's an Excel sheet with lots of macros that easily makes it possible to find IPs, IPranges, names and such on players that causes problem on your server/servers.
I have used and developed this list during the past year and now I feel that it's good enough to make public.
During the past five or six months there has only been one problem with it, so I feel confident enough to share it with everyone else.
What so special about this list?
Have you ever had a player that has been causing problem and just as you where about to ban him/her they leave?
With this list you can see what other names your players use while they are in your server.
It shows you who has a static IP.
When the player first showed up on your server.
When the player was last seen.
Gives you the option to make a comment on a player.
If you want to ban a player with a dynamic IP this list will help you finding out what IPranges to ban.
Or why not just ban a whole country? Yes it's possible, we have done it on our servers.
All this with very little manual work.
Install procedure. MUST FOLLOW THIS STEP BY STEP!!!
1 http://www.fackliga-bilder.com/bur/Big_IP_list.xls">download the excel file and place it on the desktop (for easy access, can choose anywhere if you want).
2 create a cfg file in your configsfolder called ip.cfg and paste the following code in it:
3 open your unnamedsoldier file and create a keybind to execute the configfile, ex:
5 open the excel file you downloaded.
6 approve macros.
7 hit ALT +F11 to open VBA-editor. this is the macro editor, there are a few things that need to be set up for your computer.
8 in the project window on the left, double-click on ThisWorkbook.
9 about half way trough the code you will see this line:
edit the path to where you want your second copy of the file. you will need it.
if you have two hard drives save it on the second drive so you always have one copy if one drive crashes.
10 now double-click on Module1 in project window.
find the macro called ImportQConsol either by scrolling down (quite far) or by using the right dropdown menu at the top of the "sheet".
now you need to edit the search path where it says:
to where your cqonsolefile is.
if you never used the qconsole you might not find the file but it will be created when you use the ip.cfg- file you made earlier.
just edit the path to your where your game is, remember mainta is spearhead and maintt is breaktrough depending on what game you use.
now it's installed and ready to be used in a basic way. just hit the save button and close the macro editor.
lets walk trough what you see step by step.
the list you see is a fake list. this is just to show you what can be done. you have to fill your own list :-)
header (row 2) is static, so when you scroll down it will stay visible.
column A - this is the input-column. DONT USE IT TO ANYTHING ELSE!! i will tell you later how it works.
player, score, ping, name, lastmsg, IP and qport comes from the games rcon status (the base of this list).
S.IP is if the player has been see twice (or more) with the same IP and it has been more than 20 days between. in that case it
will put a Y in the column marking the player as having a static IP (could be wrong as i have noticed later).
date is when the player was recorded.
last seen is if the same player has been seen with the same IP on another day. this is good with players with dynamic IP, you can easily see what IP they used last. last seen value changes every time you see him again.
comment is where you put a comment on the player, example: "banned for being a hack.", "does not follow server rules" or whatever you like.
N-column is also used by the macros even if it looks empty so don't use it! it's for deleting duplicated rows.
ok, how does it work?
in game:
while in game you just hit I-button to "record" the players in the server at the moment.
if you open your console you will se the game prints out the rcon status for you.
this is all you need to do while in game.
while the ip.cfg is running you can't do anything because of the waitcommands, so use it while you are "safe". takes about 2 seconds to get done.
macro ImportQConsole
ok when you are done playing you open the list and hit Alt + F8 to run a macro, choose ImportQConsole and press run.
now you will see the excel sheet copying in all of your qconsole-file in column A (input-column).
notice that the imported data starts at the line just below where your list ends (could be one or two rows of gap depending on qconsolefile).
this important to notice since if you want import from qconsole and a scanner (ex: ASE, All seeing eye) at the same time you HAVE to import qconsole first then copy paste the rcon status from ASE.
the only thing really important to remember is that the input data MUST be in the following order player, score, ping, name, lastmsg, IP, qport and rate (rcon status way).
Macro Dates
now run the macro called Dates.
ta-da!
it split's the data from the input column to the correct fields. names containing a <space> will get a _ (underline) instead (this can be changed in the macro). our server renames anyone that comes in with space to an underline so this is just to correct if the server missed one.
also you will notice the date column is filled.
the IP has been slightly changed. in the rcon status it shows you IP:portno, it removes the protno since there is no need for it.
if a zmbi (player that left the server) has been recorded it removes the line.
if not the whole IP got recorded (can happen) the line is removed.
if the line has been corrupted but name and IP is still known the corrupted data is removed.
in this stage it's time to (if needed) place comments on any of the players you just played with (ex banned, and such).
the reason for that is because after the next step it will not be as easy to find the player when the list starts growing.
if you have several lines with the same "trouble" player write the comment on the upper one. will tell you why in the next macro.
Macro Duplicates / Duplicates2
why two versions?
to lazy to delete one of them, LOL.
no, not only because of that. i think duplicates is faster than duplicates2 when the list is still small, but once the list is getting bigger version 2 is faster.
where is the magic line? have no clue!
i don't think you will notice much difference so use version 2 since i know it's stable. not sure about version 1 since i haven't used it in a long time.
ok, what happens?
it sorts the list (that's why you should comment the players before this) and removes any duplicated lines.
if the line that is "supposed" to be deleted has a comment (column M) it will not be deleted, but given a color so you can manually look at it.
So if you place the comment on the "troubleplayer" below the first one it will not delete the duplicates.
also the last seen field is filled in if "needed".
Macro IPComments.
two version again?
yes! but this is only because it's fun keeping the old version. the old version is very very slow so don't use it.
i just keep it for fun.
use version 2!
what does it do?
it compares IPs and playernames, if a two playernames use the same IP there will be a comment (red box thing) on the IP telling you this IP has multiple names. place the mouse cursor above the IP and the comment will show up showing you the names being used on this IP starting with the "own" name first and what dates they where last seen.
when the macro is done it will give you some statistics.
known issue with IPComments2:
when the file gets large (lots of rows) when running this macro it will delete the name of one player.
i really don't have any clue of why it does so.
it's never the same name, not the same row, not the same IP.
it just happens. the good thing is that this player without name will end up at the bottom of the list so you can easily find it.
this is the reason you need two copies of the file.
rename the other copy
open it and search for the IP with the missing name
copy paste.
if anyone knows why this happens, please let me know.
i can't find the reason.
this is the order you need to run the macros for it to work.
the qconsole-file will get reset every time you start the game.
so if you have been playing you must open the IPlist file and run importqconsole and dates, after that you can save the file and close it. duplicates and IPcomments2 is not something you need to run every day.
create shortcuts, macro AddItemToContextMenu
don't run it yet.
mark it and press edit (should bring up VBA-editor). you can see the description of it.
what you need to do is (if needed) edit the order, names or macros in the shortcutmenu.
as you can see the code is repeated but it changes with the caption and onaction.
caption is what the shortcut should be called and onaction is the macroname.
begingroup adds a line to divide the shortcuts from all other functions therefore i only have true on the top one.
you can easily add or remove own shortcuts here by copy paste or just deleting.
when you are happy with the shortcuts, run the macro.
now go back to excel and right click in column A or below the list (outside of the blue line as you might have noticed by now) and at the bottom you will see your shortcuts.
delete shortcuts, macro RemoveContextMenuItem
i don't think i need to comment this much, it removes what you created with the last macro.
if you edited the AddItemToContextMenu you have to edit this one too in the same way for it to remove the shortcuts correctly.
Macro ExcelDiet
read the description in VBA-editor.
Macro Playerstat
this will count how many individual playernames you have in the list. since one playername can have hundreds of IPs you cant just look at how many rows you got.
macro duplicates or duplicates2 must have been run before this macro is started for it to count correct.
if a player got promoted it will count that as two players, same with misspelled names.
now we only have three macros left.
FindIPsOnPlayer
FindNamesInIPRange
ClearSheet1
all those macros have buttons so they don't have to be runed from ALT+F8.
open the sheet called Sheet1 (bottom left corner).
this is the searchpage of this file.
start by clicking on the button called Find IP´s on player.
this gives you the searchwindow.
in the top field you type in the name you want to find
example: max
if you only press search now it will search for *max* (* is wildcard)
so it could find max, MAX, Max, Maximillian, Supermax, and so on.
but notice it will not find m@x, it cant find specialcaracters "looking" like actual letters.
in the bottom field you can exclude searches-strings.
example: maximillian
so the result of the search will give you all max but maximillian.
case sensitive is not so hard to understand.
Append previous search, let's say you know max plays with the name John_doe also.
do the first search with just max (and excludes if necessary) then start the searchmacro again and mark the checkbox to append previous search.
in that way it won't delete the previous search data.
once you have done the search you will get a message box saying you should delete the lines with names you don't want in the search by double-clicking on the line/lines.
now hit the second button Find names in ranges.
this shows you the IPranges where this player/players have played.
you can manually add a IPrange (if you know the IP and don't want to use find IP´s on player)
the radiobuttons are to control the range.
also here you can exclude names in the list.
once all the settings are done mark the lines in "Found ranges" that you wish to look at and press Refresh.
"Found names" will show you the names that play in the different IPranges.
this is to minimize the "damage" when you need to ban a whole range of IPs.
so how do i ban a country?
for example say we want to ban UK.
use find IPs on player and search for players with UK in the name.
then use Find names in ranges to figure out what ranges you need to ban to keep UK-players out :-D
this off course requires a big list to work.
and the last button, clear fields.
guess.
can i let my computer scan the server 24 hours a day?
yes!
using something like this:
the filename is called GetRconStatus.cfg, so when you run it it will create a loop that will keep running forever.
WARNING!!! IT WILL ALSO LOCK UP THE GAME. THE ONLY WAY TO CLOSE THE GAME AFTER RUNNING THIS CFG-FILE IS FROM TASK MANAGER.
with this cfg-file the game will get the rcon status from those five servers every 10 minutes.
can i change these macros so they work the way i want?
yes! just remember who actually made it, and don't remove my name in the macroeditor.
puh... did I miss something?
What is this IP list?
It's an Excel sheet with lots of macros that easily makes it possible to find IPs, IPranges, names and such on players that causes problem on your server/servers.
I have used and developed this list during the past year and now I feel that it's good enough to make public.
During the past five or six months there has only been one problem with it, so I feel confident enough to share it with everyone else.
What so special about this list?
Have you ever had a player that has been causing problem and just as you where about to ban him/her they leave?
With this list you can see what other names your players use while they are in your server.
It shows you who has a static IP.
When the player first showed up on your server.
When the player was last seen.
Gives you the option to make a comment on a player.
If you want to ban a player with a dynamic IP this list will help you finding out what IPranges to ban.
Or why not just ban a whole country? Yes it's possible, we have done it on our servers.
All this with very little manual work.
Install procedure. MUST FOLLOW THIS STEP BY STEP!!!
1 http://www.fackliga-bilder.com/bur/Big_IP_list.xls">download the excel file and place it on the desktop (for easy access, can choose anywhere if you want).
2 create a cfg file in your configsfolder called ip.cfg and paste the following code in it:
seta logfile 3
wait 500
seta g_logsync 1
wait 500
rcon status
wait 1000
seta logfile 0
3 open your unnamedsoldier file and create a keybind to execute the configfile, ex:
bind i "exec configs/ip.cfg"
5 open the excel file you downloaded.
6 approve macros.
7 hit ALT +F11 to open VBA-editor. this is the macro editor, there are a few things that need to be set up for your computer.
8 in the project window on the left, double-click on ThisWorkbook.
9 about half way trough the code you will see this line:
DoubleSave "C:KOPIA"
edit the path to where you want your second copy of the file. you will need it.
if you have two hard drives save it on the second drive so you always have one copy if one drive crashes.
10 now double-click on Module1 in project window.
find the macro called ImportQConsol either by scrolling down (quite far) or by using the right dropdown menu at the top of the "sheet".
now you need to edit the search path where it says:
FileName = "J:Program FilesEA GAMESMOHAAmaintaqconsole.log"
to where your cqonsolefile is.
if you never used the qconsole you might not find the file but it will be created when you use the ip.cfg- file you made earlier.
just edit the path to your where your game is, remember mainta is spearhead and maintt is breaktrough depending on what game you use.
now it's installed and ready to be used in a basic way. just hit the save button and close the macro editor.
lets walk trough what you see step by step.
the list you see is a fake list. this is just to show you what can be done. you have to fill your own list :-)
header (row 2) is static, so when you scroll down it will stay visible.
column A - this is the input-column. DONT USE IT TO ANYTHING ELSE!! i will tell you later how it works.
player, score, ping, name, lastmsg, IP and qport comes from the games rcon status (the base of this list).
S.IP is if the player has been see twice (or more) with the same IP and it has been more than 20 days between. in that case it
will put a Y in the column marking the player as having a static IP (could be wrong as i have noticed later).
date is when the player was recorded.
last seen is if the same player has been seen with the same IP on another day. this is good with players with dynamic IP, you can easily see what IP they used last. last seen value changes every time you see him again.
comment is where you put a comment on the player, example: "banned for being a hack.", "does not follow server rules" or whatever you like.
N-column is also used by the macros even if it looks empty so don't use it! it's for deleting duplicated rows.
ok, how does it work?
in game:
while in game you just hit I-button to "record" the players in the server at the moment.
if you open your console you will se the game prints out the rcon status for you.
this is all you need to do while in game.
while the ip.cfg is running you can't do anything because of the waitcommands, so use it while you are "safe". takes about 2 seconds to get done.
macro ImportQConsole
ok when you are done playing you open the list and hit Alt + F8 to run a macro, choose ImportQConsole and press run.
now you will see the excel sheet copying in all of your qconsole-file in column A (input-column).
notice that the imported data starts at the line just below where your list ends (could be one or two rows of gap depending on qconsolefile).
this important to notice since if you want import from qconsole and a scanner (ex: ASE, All seeing eye) at the same time you HAVE to import qconsole first then copy paste the rcon status from ASE.
the only thing really important to remember is that the input data MUST be in the following order player, score, ping, name, lastmsg, IP, qport and rate (rcon status way).
Macro Dates
now run the macro called Dates.
ta-da!
it split's the data from the input column to the correct fields. names containing a <space> will get a _ (underline) instead (this can be changed in the macro). our server renames anyone that comes in with space to an underline so this is just to correct if the server missed one.
also you will notice the date column is filled.
the IP has been slightly changed. in the rcon status it shows you IP:portno, it removes the protno since there is no need for it.
if a zmbi (player that left the server) has been recorded it removes the line.
if not the whole IP got recorded (can happen) the line is removed.
if the line has been corrupted but name and IP is still known the corrupted data is removed.
in this stage it's time to (if needed) place comments on any of the players you just played with (ex banned, and such).
the reason for that is because after the next step it will not be as easy to find the player when the list starts growing.
if you have several lines with the same "trouble" player write the comment on the upper one. will tell you why in the next macro.
Macro Duplicates / Duplicates2
why two versions?
to lazy to delete one of them, LOL.
no, not only because of that. i think duplicates is faster than duplicates2 when the list is still small, but once the list is getting bigger version 2 is faster.
where is the magic line? have no clue!
i don't think you will notice much difference so use version 2 since i know it's stable. not sure about version 1 since i haven't used it in a long time.
ok, what happens?
it sorts the list (that's why you should comment the players before this) and removes any duplicated lines.
if the line that is "supposed" to be deleted has a comment (column M) it will not be deleted, but given a color so you can manually look at it.
So if you place the comment on the "troubleplayer" below the first one it will not delete the duplicates.
also the last seen field is filled in if "needed".
Macro IPComments.
two version again?
yes! but this is only because it's fun keeping the old version. the old version is very very slow so don't use it.
i just keep it for fun.
use version 2!
what does it do?
it compares IPs and playernames, if a two playernames use the same IP there will be a comment (red box thing) on the IP telling you this IP has multiple names. place the mouse cursor above the IP and the comment will show up showing you the names being used on this IP starting with the "own" name first and what dates they where last seen.
when the macro is done it will give you some statistics.
known issue with IPComments2:
when the file gets large (lots of rows) when running this macro it will delete the name of one player.
i really don't have any clue of why it does so.
it's never the same name, not the same row, not the same IP.
it just happens. the good thing is that this player without name will end up at the bottom of the list so you can easily find it.
this is the reason you need two copies of the file.
rename the other copy
open it and search for the IP with the missing name
copy paste.
if anyone knows why this happens, please let me know.
i can't find the reason.
this is the order you need to run the macros for it to work.
the qconsole-file will get reset every time you start the game.
so if you have been playing you must open the IPlist file and run importqconsole and dates, after that you can save the file and close it. duplicates and IPcomments2 is not something you need to run every day.
create shortcuts, macro AddItemToContextMenu
don't run it yet.
mark it and press edit (should bring up VBA-editor). you can see the description of it.
what you need to do is (if needed) edit the order, names or macros in the shortcutmenu.
as you can see the code is repeated but it changes with the caption and onaction.
caption is what the shortcut should be called and onaction is the macroname.
begingroup adds a line to divide the shortcuts from all other functions therefore i only have true on the top one.
you can easily add or remove own shortcuts here by copy paste or just deleting.
when you are happy with the shortcuts, run the macro.
now go back to excel and right click in column A or below the list (outside of the blue line as you might have noticed by now) and at the bottom you will see your shortcuts.
delete shortcuts, macro RemoveContextMenuItem
i don't think i need to comment this much, it removes what you created with the last macro.
if you edited the AddItemToContextMenu you have to edit this one too in the same way for it to remove the shortcuts correctly.
Macro ExcelDiet
read the description in VBA-editor.
Macro Playerstat
this will count how many individual playernames you have in the list. since one playername can have hundreds of IPs you cant just look at how many rows you got.
macro duplicates or duplicates2 must have been run before this macro is started for it to count correct.
if a player got promoted it will count that as two players, same with misspelled names.
now we only have three macros left.
FindIPsOnPlayer
FindNamesInIPRange
ClearSheet1
all those macros have buttons so they don't have to be runed from ALT+F8.
open the sheet called Sheet1 (bottom left corner).
this is the searchpage of this file.
start by clicking on the button called Find IP´s on player.
this gives you the searchwindow.
in the top field you type in the name you want to find
example: max
if you only press search now it will search for *max* (* is wildcard)
so it could find max, MAX, Max, Maximillian, Supermax, and so on.
but notice it will not find m@x, it cant find specialcaracters "looking" like actual letters.
in the bottom field you can exclude searches-strings.
example: maximillian
so the result of the search will give you all max but maximillian.
case sensitive is not so hard to understand.
Append previous search, let's say you know max plays with the name John_doe also.
do the first search with just max (and excludes if necessary) then start the searchmacro again and mark the checkbox to append previous search.
in that way it won't delete the previous search data.
once you have done the search you will get a message box saying you should delete the lines with names you don't want in the search by double-clicking on the line/lines.
now hit the second button Find names in ranges.
this shows you the IPranges where this player/players have played.
you can manually add a IPrange (if you know the IP and don't want to use find IP´s on player)
the radiobuttons are to control the range.
also here you can exclude names in the list.
once all the settings are done mark the lines in "Found ranges" that you wish to look at and press Refresh.
"Found names" will show you the names that play in the different IPranges.
this is to minimize the "damage" when you need to ban a whole range of IPs.
so how do i ban a country?
for example say we want to ban UK.
use find IPs on player and search for players with UK in the name.
then use Find names in ranges to figure out what ranges you need to ban to keep UK-players out :-D
this off course requires a big list to work.
and the last button, clear fields.
guess.
can i let my computer scan the server 24 hours a day?
yes!
using something like this:
seta logfile 3
wait 500
seta g_logsync 1
rconaddress 207.228.232.121
rcon status
rconaddress 207.228.232.122
rcon status
rconaddress 207.228.232.123
rcon status
rconaddress 207.228.232.124
rcon status
rconaddress 207.228.232.125
rcon status
wait 600000
exec configs/GetRconStatus.cfg
the filename is called GetRconStatus.cfg, so when you run it it will create a loop that will keep running forever.
WARNING!!! IT WILL ALSO LOCK UP THE GAME. THE ONLY WAY TO CLOSE THE GAME AFTER RUNNING THIS CFG-FILE IS FROM TASK MANAGER.
with this cfg-file the game will get the rcon status from those five servers every 10 minutes.
can i change these macros so they work the way i want?
yes! just remember who actually made it, and don't remove my name in the macroeditor.
puh... did I miss something?