mysql - Compare a rows in a table for simularites and diffrences -

-

i have issue need block bots on site , need list of there account block

question answer this. same account using different ip address @ same time. need simple table of account numbers can copy , paste security

if account number appears 2 or more times under different ip address @ same time show me account number.

here example of table working with.

 table temp1  account          ip            last_used    14k4chc     79.110.19.199   2016-01-07 09:06:52 17ffhqy     79.110.19.199   2016-01-07 09:06:52 14k4chc     91.215.136.75   2016-01-07 09:06:52 17ffhqy     91.215.136.75   2016-01-07 09:06:52 15lessr     193.9.158.98    2016-01-07 09:06:51

lines 1 thru 4 example of same 2 accounts using 2 different ip addresses @ same time spamming site , possible bot.

as starting point, identify different ip addresses same account , last_used time, use query this:

  select t.account        , t.last_used        , count(distinct t.ip) cnt_ip     temp1 t    group t.account, t.last_used   having count(distinct t.ip) > 1   order t.account, t.last_used

this more rows want return, since returns distinct values of last_used.

the group by collapses rows have same values specified expressions. in case, account , last_used.

the count(distinct ) gets number of unique ip addresses each collapsed group. if of ip addresses given account , last_used values non-null , equal, return value of 1. include having clause filter out rows have same ip address.

this query starting point. used inline view, or enhanced.


Comments

Post a Comment

Popular posts from this blog

Capture and play voice with Asterisk ARI -

-
there channel ari demo in wich can control channel state: ring, answer, play silence, play tone or audio-file ( https://github.com/asterisk/ari-examples/tree/master/channel-state , https://wiki.asterisk.org/wiki/display/ast/ari+and+channels%3a+manipulating+channel+state ) possible receive chunks (parts, buffers, etc.) of call voice (which created remote subscriber) or write chunks of voice, example array of bytes (not file) in audio format (alaw, ulow etc). you can use asterisk eagi interface voice data. other option use record or mixmonitor app record channel(channel have put stasis allow dialplan control ari) "write chunks of voice" can done application playback also can create own application using c/c++, compile asterisk , result want. no, can't redirect voice directly using ari.
Read more

java - Why database contraints in HSQLDB are only checked during a commit when using transactions in Hibernate? -

-
i spotted odd behavior in hsql, seems when using database transactions database contraints not checked during sql inserts during sql commits , when transaction rollbacked not checked @ all. i have spring integration test: @runwith(springjunit4classrunner.class) @transactionconfiguration(defaultrollback=true, transactionmanager="transactionmanager") @transactional public class integrationtest { with test creates new entity instance , calls hibernate's persist on it. it works fine, when change defaultrollback false fails: caught exception while allowing testexecutionlistener [org.springframework.test.context.transaction.transactionaltestexecutionlistener@517a2b0] process 'after' execution test: method [public void myintegrationtest.test()], instance [myintegrationtest@546e61d5], exception [null] org.springframework.dao.dataintegrityviolationexception: not execute statement; sql [n/a]; constraint [null]; nested exception org.hibernate.exception.const...
Read more

ios - Debugging Core Data __NSCFSet addObject nil exception -

-
i'm getting exceptions thrown during unit tests, on core data thread message: coredata: error: serious application error. exception caught during core data change processing. bug within observer of nsmanagedobjectcontextobjectsdidchangenotification. -[__nscfset addobject:]: attempt insert nil userinfo (null) *** terminating app due uncaught exception 'nsinvalidargumentexception', reason: '-[__nscfset addobject:]: attempt insert nil' *** first throw call stack: ( 0 corefoundation 0x00683a14 __exceptionpreprocess + 180 1 libobjc.a.dylib 0x02334e02 objc_exception_throw + 50 2 corefoundation 0x0068393d +[nsexception raise:format:] + 141 3 corefoundation 0x005595b9 -[__nscfset addobject:] + 185 4 coredata 0x001d47c0 -[nsmanagedobjectcontext(_nsinternalchangeprocessing) _processpendinginsertions:withdeletions:withupdate...
Read more