[Risolto][MySQL]Estrarre incidenza di record in un DB

Linguaggi di programmazione: php, perl, python, C, bash e tutti gli altri.
Avatar utente
DoctorStrange
Entusiasta Emergente
Entusiasta Emergente
Messaggi: 2153
Iscrizione: mercoledì 14 ottobre 2015, 9:33
Desktop: Gnome3
Distribuzione: Ubuntu 18.04 Bionic Beaver
Sesso: Maschile
Località: Roma, Italia

[Risolto][MySQL]Estrarre incidenza di record in un DB

Messaggio da DoctorStrange »

Buonasera a tutti.

Ho un db composto da 1567 record. Il DB in questione è questo: https://pastebin.ubuntu.com/p/3NXfWwKfcN/ .

In questo DB fisso gli indirizzi IP da cui ricevo attacchi sulla mia istanza virtuale. Ci sono anche la marcatura temporale, e l'utenza usata per tentare l'accesso.

Vorrei provare a fare una query un po piu particolare, ma non mi intendo molto di SQL. Vorrei in pratica sapere di tutti questi indirizzi IP, quali sono quelli dai quali arrivano piu attacchi e, in particolare ottenere un ordinamento, crescente o decrescente di occorrenze dei vari indirizzi IP.

Qualcuno mi sa aiutare?

Se faccio, per esempio:

Codice: Seleziona tutto

mysql> select distinct(Ip) from AccessiFraudolenti;
+-----------------+
| Ip              |
+-----------------+
| 27.50.29.90     |
| 36.76.192.155   |
| 124.204.49.58   |
| 45.135.232.165  |
| 199.19.226.109  |
| 195.133.40.104  |
| 82.76.145.127   |
| 141.98.10.29    |
| 75.183.59.92    |
| 180.241.47.36   |
| 95.91.214.224   |
| 88.225.216.171  |
| 161.35.80.11    |
| 199.195.248.154 |
| 92.99.104.144   |
| 142.93.97.193   |
| 37.0.11.249     |
| 41.193.25.8     |
| 178.174.148.58  |
| 141.98.10.203   |
| 107.189.2.68    |
| 142.93.105.220  |
| 109.191.238.231 |
| 1.2.177.160     |
+-----------------+
24 rows in set (0.00 sec)

Ottengo tutti gli indirizzi IP che sono presenti nella tabella, ma il dato piu importante, è ordinarlo per numero di occorrenze di ognuno di questi, del dataset complessivo.

Parlo di questa attività, perche sembra che, in meno di venti minuti io abbia già avuto quasi 300 hit su uno degli IP di questo datase: t
Regola-firewall-resized.png
Qualcuno ha familiarità con la sitassi SQL?

Grazie mille.
Ultima modifica di DoctorStrange il giovedì 22 luglio 2021, 10:47, modificato 1 volta in totale.
damfer
Prode Principiante
Messaggi: 32
Iscrizione: venerdì 11 maggio 2012, 12:30
Desktop: ubuntu
Distribuzione: Ubuntu 20.04.2 LTS

Re: [MySQL]Estrarre incidenza di record in un DB

Messaggio da damfer »

Prova con questo:

Codice: Seleziona tutto

SELECT Ip, COUNT(*) AS freq FROM AccessiFraudolenti GROUP BY Ip ORDER BY freq DESC
Avatar utente
korda
Scoppiettante Seguace
Scoppiettante Seguace
Messaggi: 304
Iscrizione: giovedì 24 dicembre 2020, 15:58
Desktop: KDE Plasma
Distribuzione: solo Ubuntu LTS
Sesso: Maschile

Re: [MySQL]Estrarre incidenza di record in un DB

Messaggio da korda »

Avatar utente
tokijin
Moderatore Globale
Moderatore Globale
Messaggi: 4372
Iscrizione: mercoledì 3 giugno 2009, 23:10
Desktop: Plasma 5.19.0
Distribuzione: KDE Neon User Edition 5.19 x86_64
Località: Abruzzo

Re: [MySQL]Estrarre incidenza di record in un DB

Messaggio da tokijin »

Intanto che ho spostato nella sezione adatta, potresti provare ad aggiungere informazioni allo statement di damfer, che comunque risponde a quello che chiedi:
damfer ha scritto:
mercoledì 21 luglio 2021, 19:10
Prova con questo:

Codice: Seleziona tutto

SELECT Ip, COUNT(*) AS freq FROM AccessiFraudolenti GROUP BY Ip ORDER BY freq DESC
Se per ogni indirizzo Ip vuoi sapere da quando ha iniziato e quando ha finito, puoi provare questo:

Codice: Seleziona tutto

SELECT Ip, COUNT(*) AS freq, MIN(Data) as inizio, MAX(Data) as fine FROM AccessiFraudolenti GROUP BY Ip ORDER BY freq DESC
Ciao

EDIT:
avrei aggiunto anche una colonna con la differenza di tempo tra inizio e fine, ma abituato a usare Redshift e quasi mai MySQL non ho trovato una funzione simile a questa https://docs.aws.amazon.com/redshift/la ... ction.html che permette di scegliere l'unità di misura della differenza temporale
Sei abbruzzese se dopo che ti sei strafogato un chilogrammo di pasta, hai il coraggio di dire alla cuoca "cacc ch'è cott" - Se entra un piccione in casa..chiudi le finestre!
Ubuntu User #28657 - Il mio vecchio hardware - Tag Codice
Avatar utente
DoctorStrange
Entusiasta Emergente
Entusiasta Emergente
Messaggi: 2153
Iscrizione: mercoledì 14 ottobre 2015, 9:33
Desktop: Gnome3
Distribuzione: Ubuntu 18.04 Bionic Beaver
Sesso: Maschile
Località: Roma, Italia

Re: [MySQL]Estrarre incidenza di record in un DB

Messaggio da DoctorStrange »

Buongiorno,

Prima di tutto ringrazio tutti.
@damfer: la tua query era proprio quello che cercavo. Sembra funzionare come da aspettative. Devo piuttosto capire come funziona la clausola "freq", che mi rimane ancora un po oscura. Vedrò di documentarmi, e capire bene come funziona.

@korda: ringrazio anche te, ma a grandi linee conosco la sintassi di distinct e count. Non sapevo invece come applicarle per ottenere un incidenza su dati distribuiti in un DB.

@tokijin: Grazie per avermi spostato il thread. Ero indeciso se postarlo sotto "Applicazioni" o "Programmazione".
Per quanto riguarda il DB, non sono molto interessato alle marcature temporali degli hit, mi interessa piuttosto conoscere l'incidenza, in questo modo, posso sviluppare regole firewall che si adattino meglio agli attacchi.

Piuttosto vedrò di creare una seconda tabella nella quale, se ne avrò bisogno, fisserò le corrispondenze ed i relativi timestamp.

Saluti
damfer
Prode Principiante
Messaggi: 32
Iscrizione: venerdì 11 maggio 2012, 12:30
Desktop: ubuntu
Distribuzione: Ubuntu 20.04.2 LTS

Re: [Risolto][MySQL]Estrarre incidenza di record in un DB

Messaggio da damfer »

freq è semplicemente un nome arbitrario (etichetta) assegnato alla colonna calcolata con la funzione COUNT(*) :ciao:
Scrivi risposta

Ritorna a “Programmazione”

Chi c’è in linea

Visualizzano questa sezione: 0 utenti iscritti e 2 ospiti