• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.3 How do I search for IP addresses

#1
Hello Gentleman :)

I could make use of an update on the information provided here.
The query in the link doesn't work anymore.
How would the correct query for an ip lookup look like?

e.g. if I simply reverse the currently needed command to display ip addresses <INET_NTOA(CONV(HEX(ip.ip), 16, 10))>the command still fails.
SQL:
SELECT u.username, INET_NTOA(CONV(HEX(ip.ip), 16, 10)) AS IP
FROM xf_ip AS ip
LEFT JOIN xf_user AS u ON (u.user_id = ip.user_id)
WHERE ip.ip = HEX(CONV(INET_ATON('xxx.xxx.xxx.xxx'), 10, 16))
GROUP BY u.user_id
The xxx.xxx.xxx.xxx ofc is just a placeholder for an actual ip address.
 
Last edited:
#3
This is not an answer to my question.
I know about the acp option. Direct Database queries are a lot more structured though. I can do better analysis and generally the acp doesn't help if I want to automate stuff with scripts (pulling reports e.g.). I am specifically looking for database level solutions.
 
#6
Nice addon but not what I am looking for.

Like I said earlier. I particularily would like to know a database query for this.

Is it not possible to do this via a MySQL query?
 

Mike

XenForo developer
Staff member
#8
Custom queries aren't really in the scope of our support. We won't generally recommend running direct queries on the database except in specific circumstances (though a SELECT query won't harm the DB at least). The UI would generally be our recommended approach.

As it turns out though, after some testing, your query was fairly close. The where should use:
Code:
UNHEX(CONV(INET_ATON('xxx.xxx.xxx.xxx'), 10, 16))
 
#9
Yeah, I rarely do invasive queries. It obviously is dangerous for any database consistency.
SELECT statements are different though, as you mentioned yourself, and I have a strong preference for working on terminal. To me it's a lot easier to have a statement I can fire up in a second then to click me through the forum just to get a simple information like "who logged in with that ip".

I must admit I am biting my ass for not realizing my fault with "hex" function. Sometimes it's the simple parts one tends to overlook sometimes. Now that I see your query it's so obvious. Thanks a lot for it. :)