Newsgroup: it.comp.software.database
Titolo: [LONG] Domandona SQL

Ciao a tutti,
sono nuova di queste parti e da pochissimo (per esigenze lavorative)
sono stata "obbligata" ad imparare ad usare SQL per interrogare un database
attraverso un'applicativo (forse GNU) che sto sviluppando il quale
catturando i pacchetti presenti in LAN salva alcune info di tali pacchetti
su un database, il quale opportunamente interrogato dovrebbe generare dei
quadri statistici di indubbio valore...
Come avrete già intuito, mi sono piantata sulle query SQL da fare per
ottenere i risultati che voglio.
Allora partiamo dalle descrizione delle tabelle:
Sono 3: Transactions, Elements, Field_Type
l'idea è stata quella di sviluppare un database tipo carrello della spesa
dove ad ogni transazione (vedi tabella Transactions) sono associati uno o
più elementi (vedi tabella Elements)
In Transactions ho le seguenti colonne:
ID, Int_ID, S_Time, E_Time, F_P_s, L_P_s, Bytes_Count, Packets_Count,
Elements.
DOVE:
ID = chiave primaria di tipo autoincrement.
Int_ID = attualmente non serve a nulla.
S_Time, E_Time, F_P_s, L_P_s = Timestamp dei pacchetti raggruppati.
Bytes_Count = Numero di Bytes di quel gruppo di pacchetti.
Packets_Count = Numero di pacchetti del gruppo.
Elements = numero di voci  presenti nella tabella Elements  e riferita
alla singola riga della tabella Transactions.
In Elements ho le seguenti colonne:
ID, Trans_ID, Type, Value.
DOVE:
ID = chiave primaria di tipo autoincrement.
Trans_ID = contiene il valore del campo ID della Tabella Transactions
associato a questo elemento.
Type = tipo di protocollo, è un numero che è presente nel campo ID della
tabella Field_Type.
Value = valore dell'elemento (varia a seconda del Type).
In Field_Type ho le seguenti colonne:
ID, Protocol, Field.
DOVE:
ID = valore di riferimento (usato dal campo Type della tabella Elements).
Protocol = descrizione del protocollo (IPX piuttosto che IP).
Field = campo del protocollo (source piuttosto che destination).
Magari volete degli esempi per capire meglio come sono popolate le tabelle:
TRANSACTIONS:
|ID|Int_ID|S_Time|E_Time|F_P_s|L_P_s|Bytes_Count|Packets_Count|Elements|
------------------------------------------------------------------------
|1|0|1000|1020|1002|1019|180|3|9|
|2|0|1000|1020|1002|1019|2400|8|9|
|3|0|1000|1020|1002|1019|60|1|9|
|4|0|1000|1020|1002|1019|5400|33|9|
-----------------------------------
ELEMENTS:
|ID|Trans_ID|Type|Value|
------------------------
|1|1|1|00E063137E32|
|2|1|2|00E063137E04|
|3|1|3|0800|
|4|1|6|00|
|5|1|7|82C00C78|
|6|1|8|82C02A4B|
|7|1|9|0006|
|8|1|19|1114|
|9|1|20|008B|
|10|2|1|00E063137E32|
|11|2|2|00E063137E04|
|12|2|3|0800|
|13|2|6|00|
|14|2|7|82C00DA2|
|15|2|8|82C011FA|
|16|2|9|0006|
|17|2|19|008B|
|18|2|20|0469|
|19|3|1|00E063137E04|
|20|3|2|00E063137E32|
|21|3|3|0800|
|22|3|6|00|
|23|3|7|C0A8120E|
|24|3|8|82C00DF0|
|25|3|9|0006|
|26|3|19|0B20|
|27|3|20|008B|
|28|4|1|00E063137E04|
|29|4|2|00E063137E32|
|30|4|3|0800|
|31|4|6|00|
|32|4|7|82C00350|
|33|4|8|82C00F80|
|34|4|9|0006|
|35|4|19|006E|
|36|4|20|0D8F|
--------------
FIELD_TYPE:
|ID|Protocol|Field|
-------------------
|1|Ethernet|src|
|2|Ethernet|dst|
|3|Ethernet|type-length|
|4|VLAN|pri|
|5|VLAN|vlanid|
|6|IP|tos|
|7|IP|src|
|8|IP|dst|
|9|IP|nextp|
|10|ARP|sIPAddr|
|11|ARP|dIPAddr|
|12|ARP|op|
|13|IPv6|tos|
|14|IPv6|src|
|15|IPv6|dst|
|16|IPv6|nexthdr|
|17|ICMPv4|type|
|18|ICMPv6|type|
|19|TCP|sPort|
|20|TCP|dPort|
|21|UDP|sPort|
|22|UDP|dPort|
--------------
Ora veniamo al dunque, devo realizzare una query che mi dia una tabella
con le seguenti colonne:
Host, Bytes Sent, Bytes Recv, Packets Sent, Packets Recv
Dove: 
Host = indirizzo IP dell'host.
Bytes Sent = Bytes TOTALI inviati dall'host.
Bytes Recv = Bytes TOTALI ricevuti dall'host.
Packets Sent = Pacchetti TOTALI inviati dall'host.
Packets Recv = Pacchetti TOTALI ricevuti dall'host.
Per ora sono risucita a creare due query che mi danno quello che voglio,
ma non so come si fa ad unire le colonne fra di loro.
PRIMA QUERY:
SELECT E1.Value AS "Host", SUM(Bytes_Count) AS "Bytes Sent",
SUM(Packets_Count) AS "Packets Sent"
FROM Transactions LEFT JOIN Elements E1, Field_Type
ON Transactions.ID=E1.Trans_ID AND E1.Type=Field_Type.ID
WHERE Field_Type.Protocol IN("IP") AND Field_Type.Field IN("src")
GROUP BY (E1.Value)
ORDER BY "Bytes Sent" DESC;
SECONDA QUERY:
SELECT E2.Value AS "Host", SUM(Bytes_Count) AS "Bytes Recv",
SUM(Packets_Count) AS "Packets Recv"
FROM Transactions LEFT JOIN Elements E2, Field_Type
ON Transactions.ID=E2.Trans_ID AND E2.Type=Field_Type.ID
WHERE Field_Type.Protocol IN("IP") AND Field_Type.Field IN("dst")
GROUP BY (E2.Value)
ORDER BY "Bytes Recv" DESC;
Io vorrei unire le due query in un'unica con la clausola che E1.Value sia
uguale ad E2.Value
dato che devo avere un'unica colonna denominata Host.
Ah, dimenticavo, le due query precedenti me le ha passate un mio collega,
ora però mi hanno detto che devo aggiustarmi da sola...
Aiutatemi, vi prego.
Ciao, Federica.
-- 
questo articolo e` stato inviato via web dal servizio gratuito 
http://www.newsland.it/news
 segnala gli abusi ad 
ab...@newsland.it


:Provate a guardare gli ingredienti del famoso Labello che si usa per
:le labbra screpolate: Butirrospermum Parkii (sperma di maiale).

 Leonardo
GRAZIE, grazie di cuore!
Baci, Federica.


> Leonardo
Ho di nuovo bisogno del tuo aiuto!
Se ad esempio volessi le statistiche degli host che hanno spedito ed
inviato pacchetti TCP cosa dovrei fare?
la tabella risultante è la medesima di prima:
|Host|Bytes Sent|Bytes Recv|Packets Sent|Packets Recv|
------------------------------------------------------
dove però negli host devono comparire sia 'IP' che 'IPv6' (con ovviamente
la divisione tra 'src' e 'dst' x il conteggio di Bytes e Packets) limitata
alle sole transazioni dove è presente il campo 'TCP'
Ho provato questo ma non dà alcun risultato:
SELECT E1.Value AS "Host",
SUM(CASE WHEN Field_Type.Protocol = 'TCP' THEN Bytes_Count ELSE 0 END) AS
"Bytes Sent",
SUM(CASE WHEN Field_Type.Protocol = 'TCP' THEN Bytes_Count ELSE 0 END) AS
"Bytes Recv",
SUM(CASE WHEN Field_Type.Protocol = 'TCP' THEN Packets_Count ELSE 0 END)
AS "Packets Sent",
SUM(CASE WHEN Field_Type.Protocol = 'TCP' THEN Packets_Count ELSE 0 END)
AS "Packets Recv"
FROM Transactions LEFT JOIN Elements E1, Field_Type
ON Transactions.ID=E1.Trans_ID AND E1.Type = Field_Type.ID
WHERE
(Field_Type.Protocol IN ('IP') OR Field_Type.Protocol IN ('IPv6'))
AND
Field_Type.Field IN ('src', 'dst')
GROUP BY E1.Value
ORDER BY E1.Value;
O meglio, dà l'elenco degli host, ma segna tutti 0 sia nei Bytes che nei
Packets.
Grazie, Federica.


 Ho di nuovo bisogno del tuo aiuto!
;-)

 ;-)
Che mattacchione!
O rispondi in modo costruttivo, oppure evita, fai + bella figura.









Scervellandomi sono arrivata a questa soluzione:
SELECT E1.Value AS "Host",
SUM(CASE WHEN (F1.Field = 'src' AND F2.Protocol = 'ICMPv4') THEN
Bytes_Count ELSE 0 END) AS "Bytes Sent",
SUM(CASE WHEN (F1.Field = 'dst' AND F2.Protocol = 'ICMPv4') THEN
Bytes_Count ELSE 0 END) AS "Bytes Recv",
SUM(CASE WHEN (F1.Field = 'src' AND F2.Protocol = 'ICMPv4') THEN
Packets_Count ELSE 0 END) AS "Packets Sent",
SUM(CASE WHEN (F1.Field = 'dst' AND F2.Protocol = 'ICMPv4') THEN
Packets_Count ELSE 0 END) AS "Packets Recv"
FROM Transactions T LEFT JOIN Elements E1, Field_Type F1, Elements E2,
Field_Type F2
ON 
T.ID
=E1.Trans_ID AND E1.Type = 
F1.ID
 AND E2.Type = 
F2.ID
 AND
E1.Trans_ID = E2.Trans_ID
WHERE
F1.Protocol IN('IP', 'IPv6')
AND F1.Field IN ('src', 'dst')
AND F2.Protocol IN ('ICMPv4')
GROUP BY E1.Value
ORDER BY E1.Value;
Secondo voi è corretta?
Ciao, Federica.


inviato pacchetti TCP cosa dovrei fare?
Limitare la ricerca ai soli record che hanno "TCP".
Sinceramente, dalla descrizione della tabella Type che hai fatto prima,
non mi sembra sia previsto. Cioe', il protocollo e' IP o IPX, ma TCP e'
qualcosa che di norma sta "dentro" a IP.
Io _CREDO_ che basti sostituire "TCP" a "IP" dentro alla query vista. E
ovviamente a questo punto non distingui IP da IPng - a meno che ti crei
dei "metaprotocolli" come TCPv4, TCPv6, ICMPv4... cosa che ho l'orrendo
dubbio che abbiate gia' cominciato a fare.
Credo quindi che quel database abbia bisogno di una revisione e per es.
io allargherei la tabella "Type" prevedendo campi diversi per:
	- Protocollo di rete (es. Ethernet)
	- Protocollo di networking (es. IPv4, IPv6...)
	- Protocollo di trasporto (es. TCP, UDP)
	- porta sorgente (o zero)
	- porta destinazione (o zero)
	- host sorgente
	- host destinazione
per evitare appunto di doverlo fare a mano ("ICMPv4", invece che "ICMP"
nel campo trasporto e "IPv4" nel campo networking [1])
Ovviamente dipende molto da che tipo di query ci si va a fare.
Leonardo
[1] okay, potete scuoiarmi sul fatto che ICMP sia un po' piu' in basso,
    a rigore, di TCP.
