Sponsored Content
Top Forums Programming MySQL select user with the same IP address Post 302860175 by durden_tyler on Saturday 5th of October 2013 01:04:10 AM
Old 10-05-2013
Quote:
Originally Posted by Lord Spectre
...
I woul like to make a query to output all the users having the same IP address.
... I'm expecting somethig like:
Code:
name          logged_ip   count
========      =========   ======
user1,user4   127.0.0.1   2
user2,user5   127.0.0.2   2

How can I output the usernames with the same IP as the example?
...
Code:
mysql>
mysql> select * from users;
+-------+-----------+
| name  | logged_ip |
+-------+-----------+
| user1 | 127.0.0.1 |
| user2 | 127.0.0.2 |
| user3 | 127.0.0.3 |
| user4 | 127.0.0.1 |
| user5 | 127.0.0.2 |
| user6 | 127.0.0.5 |
+-------+-----------+
6 rows in set (0.00 sec)

mysql>
mysql> select group_concat(name) as name, logged_ip, count(*) as count from users group by logged_ip having count(*) > 1;
+-------------+-----------+-------+
| name        | logged_ip | count |
+-------------+-----------+-------+
| user1,user4 | 127.0.0.1 |     2 |
| user2,user5 | 127.0.0.2 |     2 |
+-------------+-----------+-------+
2 rows in set (0.00 sec)

mysql>
mysql>

This User Gave Thanks to durden_tyler For This Post:
 

10 More Discussions You Might Find Interesting

1. Programming

Get user name from ip address

hi all i am new in unix programming i want some help from this forum, i want to know how to get user name of other machine in local network from its ip address or suggest any books for networking programming on unix sachin (6 Replies)
Discussion started by: munnu
6 Replies

2. Shell Programming and Scripting

Insert IP address into MySQL int field

Greets all, I'm using Perl trying to insert an IP address from a log file into an INT field in a MYSQL table. So it needs to be converted into an int first. I thought the pack() function could do this for me, but I'm not sure I get the right thing. I also need to be able to extract it correctly... (3 Replies)
Discussion started by: otheus
3 Replies

3. UNIX for Dummies Questions & Answers

script to ask the user to select a number

hi all, I have 4 scripts which i would need to run 1. BP.sh 2.DB.sh 3.LK.sh 4.TB.sh I would like write a script which would ask the user to select a number from 1-4 accordingly and run only that script for him/her I succeeded till reading the user input but not sure how to run that... (5 Replies)
Discussion started by: family_guy
5 Replies

4. OS X (Apple)

Ho do I masquerade the "user@user.local" address in mail/mailx?

Hi, I'm brand new here and looking for a solution: I'm using mail or mailx. The default reply address is «myshortusername@mylongusername.local» which makes absolutely no sense for anybody receiving my emails. But how do I change it? There seem to be many solutions but none for Mac OS X.... (0 Replies)
Discussion started by: gczychi
0 Replies

5. UNIX for Dummies Questions & Answers

Panic kernal-mode address fault on user address 0x14

:) Firstly Hi all!!, im NEW!! and on here hoping that someone might be able to offer me some help... i have a server that keeps crashing every few days with the error message: PANIC KERNAL-MODE ADDRESS FAULT ON USER ADDRESS 0X14 KERNAL PAGE FAULT FROM (CS:EIP)=(100:EF71B5BD) EAX=EF822000... (10 Replies)
Discussion started by: Twix
10 Replies

6. Programming

Select several minimum values from row (MySQL)

Hello there. I've got the query like that SELECT count(tour_id) AS cnt FROM orders JOIN tours ON orders.tour_id=tours.id GROUP BY tour_id The result Is cnt 1 4 2 1 1 Now i have to select all records with minimum values in field "cnt" MySQL function min() returns only one.... (2 Replies)
Discussion started by: Trump
2 Replies

7. Shell Programming and Scripting

Select record with MAX value in MySQL

Hi there, I have trouble selecting record that contain one biggest value for a group of other values. I mean, this is my table: mysql> SELECT * FROM b; +----+------+-------+ | id | user | value | +----+------+-------+ | 1 | 1 | 100 | | 3 | 1 | 150 | | 5 | 1 | 300 | | 6... (20 Replies)
Discussion started by: chebarbudo
20 Replies

8. Shell Programming and Scripting

mysql how to select a specific row from a table

i have a table records ------------ id | user | time | event 91 admin | 12:00 | hi 92 admin | 11:00 | hi 93 admin | 12:00 | bye 94 admin | 13:00 | bye 95 root | 12:00 | hi 96 root | 12:30 | hi 97 root | 12:56 | hi how could i only select and display only the user and event from... (6 Replies)
Discussion started by: kpddong
6 Replies

9. Shell Programming and Scripting

mysql select query optimization..

hi.. i need to optimize my select query .. my situation is like this .. i have 15 lac recors in my table.. the following query takes nine seconds to give the required output.. SELECT max(ah.AUC_AMT), SUBSTRING_INDEX(GROUP_CONCAT(SUBSTRING_INDEX(ah.AUC_CUS_NAME,'@',1) order by AUC_AMT... (0 Replies)
Discussion started by: senkerth
0 Replies

10. UNIX for Advanced & Expert Users

mysql select query optimization..

hi.. i need to optimize my select query .. my situation is like this .. i have 15 lac recors in my table.. the following query takes nine seconds to give the required output.. SELECT max(ah.AUC_AMT), SUBSTRING_INDEX(GROUP_CONCAT(SUBSTRING_INDEX(ah.AUC_CUS_NAME,'@',1) order by AUC_AMT... (1 Reply)
Discussion started by: senkerth
1 Replies
POSTGREYREPORT(1)					User Contributed Perl Documentation					 POSTGREYREPORT(1)

NAME
postgreyreport - Fatal report for Postfix Greylisting Policy Server SYNOPSIS
postgreyreport [options...] -h, --help display this help and exit --version display version and exit --user=USER run as USER (default: postgrey) --dbdir=PATH find db files in PATH (default: /var/lib/postgrey) --delay=N report triplets that did not try again after N seconds (default: 300) --greylist-text=TXT text to match on for greylist maillog lines --skip_pool Skip report for 'subscriber pools' ( last 2 octets of IP found in PTR name ) --skip_dnsbl=RBL RBL server to query and skip reporting for any listed hosts (SLOW!!) --skip_clients=FILE PTR or IP or REGEXP of clients to skip in report --match_clients=FILE *ONLY* report if fatal *AND* PTR/IP of client matches --show_tries display the number of attempts failed triplets made in first column --show_time show entry time in maillog (single line only) --tab use tabs as separators for easy cut(1)ting --nosingle_line display sender/recipients grouped by ptr - ip --separate_by_subnet=TXT display TXT for every new /24 (ex: "================= " ) --separate_by_ip=TXT display TXT for every new IP (ex: " ") --check_sender=LIST one or more of: mx,mx/24,a,a/24 does DNS/A lookups for sender @domain and compares sending IP if match displays "MX" "A" or "MX/24" or "A/24" depending on LIST Note that --(skip|match)_clients can be specified multiple times and there are no default files. Same rules apply as postgrey's --whitelist-clients, see postgrey doc for more info. --skip_dnsbl can also be specified multiple times to query multiple DNSBL servers. DESCRIPTION
postgreyreport opens postgrey.db as read-only; reads a maillog via STDIN, extracts the triplets for any Greylisted lines and looks them up in postgrey.db. if the difference in first and last time seen is less than --delay=N then the triplet is considered fatal and displayed to STDOUT The report sorts by client IP address Note: unless you are using --lookup_by_subnet or excluding all known MTA pools you will likely have false fatal reports for "BigISPs". A message that was tried from every IP in SMTP pool before making it through will show up in the report for all of the attempted source IPs USAGE It is best to run postgreyreport against a maillog that is at least several hours old (yesterdays?) ( you be the judge on how old is acceptable ). if you run the report against a live maillog you are not giving legit MTA's enough time to try again and you will have lots of inaccurate information. o Ex usage: zcat /var/log/maillog.0.gz | ./postgreyreport [options] > postgreyreport.log or zcat /var/log/maillog.0.gz | ./postgreyreport --nosingle_line --check_sender=mx,a --separate_by_subnet=":================== " # 94 "=" total, some were omitted for clarity o Ex Output: ( POD wrapping will mess this up, view source ) :============================================================================================ unknown 4.29.43.31 marissa_mcclendonuu@abit.com.tw user1@recipient1.com jake_meyerdt@ali.com.tw user2@recipient1.com jenny_banks_sh@translate.ru user1@recipient2.com rvazquezpo@ali.com.tw user3@recipient1.com aep@notimexico.com user2@recipient1.com brittneystanley_ei@cetra.org.tw user2@recipient1.com brendasheehan_cw@lib.ru user2@recipient1.com :============================================================================================ lsanca1-ar5-127-189.biz.dsl.gtei.net 4.33.127.189 A fokkensr@lsanca1-ar5-127-189.biz.dsl.gtei.net user2@recipient1.com cyxlfrfwciercu@publicist.com user3@recipient4.com :============================================================================================ smtpout.mac.com 17.250.248.83 do_not_reply@apple.com user4@recipient5.com smtpout.mac.com 17.250.248.88 MX legituser@mac.com user6@recipient7.com :============================================================================================ HISTORY
1.14.3 20100321 Some additions, Leonard den Ottolander <leonard.den.ottolander.nl> New option: --tab Use tabs as separator in single line mode New option: --show_time Show entry time in maillog in single line mode 1.14.2 20040715 BUGFIX: (automatic) lookup-by-subnet support was broken, fixed. BUGFIX: corrected a few spelling errors new Option: --skip_pool Skip report for 'subscriber pools' 1.14.1 20040712 Changed --return-string to --greylist-text to match postgrey new Option: --skip_clients=FILE new Option: --match_clients=FILE new Option: --skip_dnsbl=RBL.DNS.NAME All 3 of the new options can be specified multiple times. Updated do_*_subsititions again to match postgrey 1.11.1 20040701 missing keys from DB are considered fatal triplets and included in report Changed --delay testing from "greater than" to "greater than or equal to" Fixed --help and --man switches Removed setuid Notice 1.6.4 20040618 Initial Public Version (postgrey/contrib) AUTHOR
Tom Baker <tbaker@bakerfl.org> perl v5.14.2 2012-02-03 POSTGREYREPORT(1)
All times are GMT -4. The time now is 01:34 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy