• Replies 4 replies
  • Subscribers 187 subscribers
  • Views 130 views
  • Users 0 members are here
Options
Related

SQL code to find IP Addresses of Potential Broker Accounts

Christopher Cuhel
$organization

Hello,

We recently got some SQL code to find IP addresses. We are using this to check accounts that are created that we feel might be a broker purchase. The code will look at every time they accesses the web so you can get each one. The web session table holds this data for 14 days so you won't see anything beyond that. We've already found the code helpful. I use https://nordvpn.com/ip-lookup/ to look up where the IP is located to compare to the mailing address. This isn't fool proof as a VPN could circumnavigate that but it's something.

SELECT DISTINCT s.*
FROM t_web_session_Session AS s
WHERE s.IP <> '::1' AND s.IP <> ''
AND s.customer_no IN (
XXXXX
)
ORDER BY StartDateTime;

The XXXXX is where you put the account number and you can comma separate it if you'd like.

- Chris

Parents
  • $organization

    I'll add in that we do something similar in a List Manager list refreshed overnight to do a clunky sort of velocity check - looking for 3+ orders in the past 24 hours all originating from the same IP address. There can certainly be false positives if a patron books things in separate orders instead of just filling up their cart, but one more tool in the toolbox. This snippet is embedded in a larger List Manager query UNIONed with other queries to try to flag potential suspicious accounts or resellers, but here's the relevant IP address bit.

    we are on v15.2.39 currently, so I don't think the web session table is v16 specific - sorry I'm not sure why it's not turning up for you! I expect it is only accessible via SSMS or manual SQL queries, not visible in the Tessitura frontend.

    SELECT DISTINCT a.customer_no
FROM   V_CUSTOMER_WITH_PRIMARY_GROUP AS a WITH (NOLOCK)
       INNER JOIN
       (SELECT a1.customer_no
        FROM   T_ORDER AS a1 WITH (NOLOCK)
		JOIN T_WEB_ORDER as a2 WITH (NOLOCK) on a1.order_no = a2.order_no
        JOIN t_web_session_Session as a3 WITH (NOLOCK) on a2.sessionkey = a3.SessionKey
        WHERE  a1.order_dt >= DateAdd(dd, -1, CONVERT (VARCHAR, GetDate(), 112))
        and a1.mos in (10,12,13,34)
        GROUP BY a1.customer_no
		HAVING count(a3.IP) >= 3) AS e
       ON e.customer_no = a.customer_no
WHERE  a.inactive = 1

  • $organization in reply to Evan Cartwright

    (oh, and the MOS bit in that code snippet are narrowing down to our specific website modes of sale, easy to change or cut if you adapt this in your environment!)

Reply Children
No Data