Baller Guide to Finding All Them IPs

Version 1

    Banning IPs from the Admin Console: https://brewspace.jiveland.com/docs/DOC-70967

    My method for finding a user's IP address: https://brewspace.jiveland.com/docs/DOC-122666

    @mention: https://brewspace.jiveland.com/message/1421813?et=notification.mention#1421813

    Mollom Plugin: https://brewspace.jiveland.com/docs/DOC-213584

    Related Case: https://community.jivesoftware.com/casethread/364529

     

    I started by looking up some kind of common activity that I might be able to trace in the Apache Access Logs.

    SELECT * FROM jivedw_activity_fact WHERE user_id IN (SELECT user_id FROM jivedw_user WHERE username = 'creed_31') AND direct_dw_object_id = 543132 AND activity_type = 20;

           activity_ts       | day_id | user_id | activity_type | direct_object_type | direct_dw_object_id | indirect_object_type | indirect_dw_object_id | dw_container_id | metadata

    -------------------------+--------+---------+---------------+--------------------+---------------------+----------------------+-----------------------+-----------------+----------

    2015-03-02 03:55:54.389 |   2265 |   90334 |            20 |                  1 |              543132 |                      |                       |              14 |

    (1 row)

     

    SELECT * FROM jivedw_container WHERE dw_container_id IN (SELECT dw_container_id FROM jivedw_activity_fact WHERE user_id IN (SELECT user_id FROM jivedw_user WHERE username = 'creed_31') AND direct_dw_object_id = 543132 AND activity_type = 20);

    dw_container_id | container_type | container_id |      name      |       creation_ts       |     modification_ts   

    -----------------+----------------+--------------+----------------+-------------------------+-------------------------

                  14 |             14 |         2006 | Internet Forum | 2012-06-01 16:10:26.587 | 2015-03-02 09:50:26.357

    (1 row)

    I then reproduced the creation event in another 5.0 test instance to identify how it is written to the Apache Access Logs. Then used the container_type, container_id and activity_ts to put together a search query I could use to locate the activity.

    [nathan.howard@shawcomm-wa02 logs]$ grep '01/Mar/2015:19:55:54' jive-httpd-access.log-20150302 | grep 'POST /post.jspa?container=2006&containerType=14'

    211.191.127.207 - - [01/Mar/2015:19:55:54 -0800] "POST /post.jspa?container=2006&containerType=14&reply=false HTTP/1.1" 302 20 404002 8 "https://community.shaw.ca/post!input.jspa?container=2006&containerType=14" "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/40.0.2214.115 Safari/537.36" "text/html" 0F08E1CE1D250BDE1213F14DD67F5C6C. 403839

    Then just to be certain, I ruled this out as one of Jive's IP Addresses and pulled some information via WHOIS.

    LT-A8-120939:~ nathan.howard$ whois 211.191.127.207

     

     

    #

    # ARIN WHOIS data and services are subject to the Terms of Use

    # available at: https://www.arin.net/whois_tou.html

    #

    # If you see inaccuracies in the results, please report at

    # http://www.arin.net/public/whoisinaccuracy/index.xhtml

    #

     

     

     

     

    #

    # Query terms are ambiguous.  The query is assumed to be:

    #     "n 211.191.127.207"

    #

    # Use "?" to get help.

    #

     

     

    #

    # The following results may also be obtained via:

    # http://whois.arin.net/rest/nets;q=211.191.127.207?showDetails=true&showARIN=false&ext=netref2

    #

     

     

    NetRange:       211.0.0.0 - 211.255.255.255

    CIDR:           211.0.0.0/8

    NetName:        NET-211

    NetHandle:      NET-211-0-0-0-1

    Parent:          ()

    NetType:        Allocated to APNIC

    OriginAS:

    Organization:   Asia Pacific Network Information Centre (APNIC)

    RegDate:        1996-07-01

    Updated:        2010-08-02

    Comment:        This IP address range is not registered in the ARIN database.

    Comment:        For details, refer to the APNIC Whois Database via

    Comment:        WHOIS.APNIC.NET or http://wq.apnic.net/apnic-bin/whois.pl

    Comment:        ** IMPORTANT NOTE: APNIC is the Regional Internet Registry

    Comment:        for the Asia Pacific region. APNIC does not operate networks

    Comment:        using this IP address range and is not able to investigate

    Comment:        spam or abuse reports relating to these addresses. For more

    Comment:        help, refer to http://www.apnic.net/apnic-info/whois_search2/abuse-and-spamming

    Ref:            http://whois.arin.net/rest/net/NET-211-0-0-0-1

     

     

    OrgName:        Asia Pacific Network Information Centre

    OrgId:          APNIC

    Address:        PO Box 3646

    City:           South Brisbane

    StateProv:      QLD

    PostalCode:     4101

    Country:        AU

    RegDate:

    Updated:        2012-01-24

    Ref:            http://whois.arin.net/rest/org/APNIC

     

     

    ReferralServer: whois://whois.apnic.net

     

     

    OrgTechHandle: AWC12-ARIN

    OrgTechName:   APNIC Whois Contact

    OrgTechPhone:  +61 7 3858 3188

    OrgTechEmail:  search-apnic-not-arin@apnic.net

    OrgTechRef:    http://whois.arin.net/rest/poc/AWC12-ARIN

     

     

    OrgAbuseHandle: AWC12-ARIN

    OrgAbuseName:   APNIC Whois Contact

    OrgAbusePhone:  +61 7 3858 3188

    OrgAbuseEmail:  search-apnic-not-arin@apnic.net

    OrgAbuseRef:    http://whois.arin.net/rest/poc/AWC12-ARIN

     

     

     

     

    #

    # ARIN WHOIS data and services are subject to the Terms of Use

    # available at: https://www.arin.net/whois_tou.html

    #

    # If you see inaccuracies in the results, please report at

    # http://www.arin.net/public/whoisinaccuracy/index.xhtml

    #

     

     

    % [whois.apnic.net]

    % Whois data copyright terms    http://www.apnic.net/db/dbcopyright.html

     

     

    % Information related to '211.172.0.0 - 211.199.255.255'

     

     

    inetnum:        211.172.0.0 - 211.199.255.255

    netname:        KRNIC-KR

    descr:          KRNIC

    descr:          Korea Network Information Center

    country:        KR

    admin-c:        HM127-AP

    tech-c:         HM127-AP

    remarks:        ******************************************

    remarks:        KRNIC is the National Internet Registry

    remarks:        in Korea under APNIC. If you would like to

    remarks:        find assignment information in detail

    remarks:        please refer to the KRNIC Whois DB

    remarks:        http://whois.nic.or.kr/english/index.html

    remarks:        ******************************************

    mnt-by:         APNIC-HM

    mnt-lower:      MNT-KRNIC-AP

    changed:        hostmaster@apnic.net 20000607

    changed:        hostmaster@apnic.net 20010606

    status:         ALLOCATED PORTABLE

    source:         APNIC

     

     

    person:         Host Master

    address:        11F, KTF B/D, 1321-11, Seocho2-Dong, Seocho-Gu,

    address:        Seoul, Korea, 137-857

    country:        KR

    phone:          +82-2-2186-4500

    fax-no:         +82-2-2186-4496

    e-mail:         hostmaster@nic.or.kr

    nic-hdl:        HM127-AP

    mnt-by:         MNT-KRNIC-AP

    changed:        hostmaster@nic.or.kr 20020507

    source:         APNIC

     

     

    % Information related to '211.191.112.0 - 211.191.143.255'

     

     

    inetnum:        211.191.112.0 - 211.191.143.255

    netname:        SHINBIRO-KR

    descr:          ONSE Telecom

    country:        KR

    admin-c:        IA10-KR

    tech-c:         IM10-KR

    status:         ALLOCATED PORTABLE

    mnt-by:         MNT-KRNIC-AP

    mnt-irt:        IRT-KRNIC-KR

    remarks:        This information has been partially mirrored by APNIC from

    remarks:        KRNIC. To obtain more specific information, please use the

    remarks:        KRNIC whois server at whois.krnic.net.

    changed:        hostmaster@nic.or.kr

    source:         KRNIC

     

     

    % This query was served by the APNIC Whois Service version 1.69.1-APNICv1r0 (UNDEFINED)

    It looks like this is the IP Address you want as it's also coming from South Korea as seen in many other reported Spam incidents in the last few weeks.

     

    SELECT * FROM jivedw_activity_fact WHERE user_id IN (SELECT user_id FROM jivedw_user WHERE username = 'ausmfl44') AND direct_dw_object_id = 543969 AND activity_type = 20;

           activity_ts       | day_id | user_id | activity_type | direct_object_type | direct_dw_object_id | indirect_object_type | indirect_dw_object_id | dw_container_id | metadata

    -------------------------+--------+---------+---------------+--------------------+---------------------+----------------------+-----------------------+-----------------+----------

    2015-03-04 04:39:53.948 |   2267 |   90543 |            20 |                  1 |              543969 |                      |                       |              10 |

    (1 row)

     

    SELECT * FROM jivedw_container WHERE dw_container_id IN (SELECT dw_container_id FROM jivedw_activity_fact WHERE user_id IN (SELECT user_id FROM jivedw_user WHERE username = 'ausmfl44') AND direct_dw_object_id = 543969 AND activity_type = 20);

    dw_container_id | container_type | container_id |  name  |       creation_ts       |     modification_ts   

    -----------------+----------------+--------------+--------+-------------------------+-------------------------

                  10 |             14 |         2004 | Lounge | 2012-05-14 19:12:09.946 | 2015-03-04 18:32:40.932

    (1 row)

    Checked Access Logs to pull IP Address:

    [nathan.howard@shawcomm-wa02 logs]$ nice zgrep '03/Mar/2015:20:39:53' jive-httpd-access.log-20150304.gz | grep 'POST /post.jspa?container=2004&containerType=14'

    183.97.32.133 - - [03/Mar/2015:20:39:53 -0800] "POST /post.jspa?container=2004&containerType=14&reply=false HTTP/1.1" 302 20 342011 3 "https://community.shaw.ca/community/post%21input.jspa?containerType=14&container=2004" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/40.0.2214.115 Safari/537.36" "text/html" C996B04E528F44D35D2345E48CC898AF. 341910

    Checked IP Address:

    LT-A8-120939:application nathan.howard$ whois 183.97.32.133

     

    #

    # ARIN WHOIS data and services are subject to the Terms of Use

    # available at: https://www.arin.net/whois_tou.html

    #

    # If you see inaccuracies in the results, please report at

    # http://www.arin.net/public/whoisinaccuracy/index.xhtml

    #

     

     

    #

    # Query terms are ambiguous.  The query is assumed to be:

    #     "n 183.97.32.133"

    #

    # Use "?" to get help.

    #

     

    #

    # The following results may also be obtained via:

    # http://whois.arin.net/rest/nets;q=183.97.32.133?showDetails=true&showARIN=false&ext=netref2

    #

     

    NetRange:       183.0.0.0 - 183.255.255.255

    CIDR:           183.0.0.0/8

    NetName:        APNIC-183

    NetHandle:      NET-183-0-0-0-1

    Parent:          ()

    NetType:        Allocated to APNIC

    OriginAS:

    Organization:   Asia Pacific Network Information Centre (APNIC)

    RegDate:        2009-04-30

    Updated:        2010-07-30

    Comment:        This IP address range is not registered in the ARIN database.

    Comment:        For details, refer to the APNIC Whois Database via

    Comment:        WHOIS.APNIC.NET or http://wq.apnic.net/apnic-bin/whois.pl

    Comment:        ** IMPORTANT NOTE: APNIC is the Regional Internet Registry

    Comment:        for the Asia Pacific region. APNIC does not operate networks

    Comment:        using this IP address range and is not able to investigate

    Comment:        spam or abuse reports relating to these addresses. For more

    Comment:        help, refer to http://www.apnic.net/apnic-info/whois_search2/abuse-and-spamming

    Ref:            http://whois.arin.net/rest/net/NET-183-0-0-0-1

     

    OrgName:        Asia Pacific Network Information Centre

    OrgId:          APNIC

    Address:        PO Box 3646

    City:           South Brisbane

    StateProv:      QLD

    PostalCode:     4101

    Country:        AU

    RegDate:

    Updated:        2012-01-24

    Ref:            http://whois.arin.net/rest/org/APNIC

     

    ReferralServer: whois://whois.apnic.net

     

    OrgAbuseHandle: AWC12-ARIN

    OrgAbuseName:   APNIC Whois Contact

    OrgAbusePhone:  +61 7 3858 3188

    OrgAbuseEmail:  search-apnic-not-arin@apnic.net

    OrgAbuseRef:    http://whois.arin.net/rest/poc/AWC12-ARIN

     

    OrgTechHandle: AWC12-ARIN

    OrgTechName:   APNIC Whois Contact

    OrgTechPhone:  +61 7 3858 3188

    OrgTechEmail:  search-apnic-not-arin@apnic.net

    OrgTechRef:    http://whois.arin.net/rest/poc/AWC12-ARIN

     

     

    #

    # ARIN WHOIS data and services are subject to the Terms of Use

    # available at: https://www.arin.net/whois_tou.html

    #

    # If you see inaccuracies in the results, please report at

    # http://www.arin.net/public/whoisinaccuracy/index.xhtml

    #

     

    % [whois.apnic.net]

    % Whois data copyright terms    http://www.apnic.net/db/dbcopyright.html

     

    % Information related to '183.96.0.0 - 183.127.255.255'

     

    inetnum:        183.96.0.0 - 183.127.255.255

    netname:        KORNET

    descr:          Korea Telecom

    descr:          Network Management Center

    descr:          ********************************

    descr:          Allocated to KRNIC Member.

    descr:          If you would like to find assignment

    descr:          information in detail please refer to

    descr:          the KRNIC Whois Database at:

    descr:          http://whois.nic.or.kr/english/index.htm

    descr:          *********************************

    country:        KR

    admin-c:        IM76-AP

    tech-c:         IM76-AP

    status:         ALLOCATED PORTABLE

    remarks:        www.kt.co.com

    mnt-by:         MNT-KRNIC-AP

    mnt-lower:      MNT-KRNIC-AP

    changed:        hm-changed@apnic.net 20091104

    source:         APNIC

     

    person:         IP Manager

    nic-hdl:        IM76-AP

    e-mail:         kornet_ip@kt.com

    address:        Seoul

    address:        206, Jungja-Dong, Bundang-Gu, Sungnam, Gyunggi-Do

    address:        463-711

    phone:          +82-2-500-6630

    fax-no:         +82-2-3674-5721

    country:        KR

    changed:        hostmaster@nic.or.kr 20111229

    mnt-by:         MNT-KRNIC-AP

    source:         APNIC

     

    % Information related to '183.96.0.0 - 183.127.255.255'

     

    inetnum:        183.96.0.0 - 183.127.255.255

    netname:        KORNET-KR

    descr:          Korea Telecom

    country:        KR

    admin-c:        IA9-KR

    tech-c:         IM9-KR

    status:         ALLOCATED PORTABLE

    mnt-by:         MNT-KRNIC-AP

    mnt-irt:        IRT-KRNIC-KR

    remarks:        This information has been partially mirrored by APNIC from

    remarks:        KRNIC. To obtain more specific information, please use the

    remarks:        KRNIC whois server at whois.krnic.net.

    changed:        hostmaster@nic.or.kr

    source:         KRNIC

     

    % This query was served by the APNIC Whois Service version 1.69.1-APNICv1r0 (UNDEFINED)

    However to be clear, this is likely a futile effort. South Korea likely has hundreds of thousands of IP Addresses. I can personally guarantee you will never reach all of them and we'll be playing this game of "where's the IP" for years. The most effective ways of blocking Spam are what we've outlined in our Documentation on Preventing Spam.