XF 1.3 How do I search for IP addresses

[tobi-the-fraggel]

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.

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.

 

[Mike]

You can search by IP in the control panel, under search for users (it's a tab).

 

[tobi-the-fraggel]

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.

 

[tobi-the-fraggel]

Would still appreciate to get help on this question.

 

[AndyB]

You might like this add-on:

https://xenforo.com/community/resources/user-agent.3082/

 

[tobi-the-fraggel]

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?

 

[tobi-the-fraggel]

Is there a reason why staff is so mysterious about giving me the query?
Don't mind me questioning but I thought this would be a "1 minute" thread.

 

[Mike]

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:

UNHEX(CONV(INET_ATON('xxx.xxx.xxx.xxx'), 10, 16))

 

[tobi-the-fraggel]

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.