What is the best way of doing a where clause for r...
# fleett
Tor Houghton
01/14/2022, 10:06 AMWhat is the best way of doing a where clause for remote_address equalling an address (either ipv6 or ipv4)?
l
Lucas Rodriguez
01/14/2022, 4:31 PMHi! What table are you querying?
t
Tor Houghton
01/14/2022, 4:57 PMbpf_socket_events; i wish to run a scheduled query for communication outside of my local network.
Tor Houghton
01/14/2022, 4:58 PMi have a horrible list of "and like" clauses and it's just getting worse as i eliminate what ends up in the remote_address column 🙂
l
Lucas Rodriguez
01/14/2022, 5:19 PMOK, I'll check with the team.
s
sharvil
01/14/2022, 6:15 PMhey @Tor Houghton, would you mind sharing your query — I can try and take a look
sharvil
01/14/2022, 6:17 PMUsually the format would be something like:
SELECT * FROM bpf_socket_events WHERE (remote_address = '127.0.0.1' OR remote_address = 'ff:ff:ff');t
Tor Houghton
01/14/2022, 6:29 PMI do the following (I've removed the where clauses that filter out rfc1918 addresses in this query, because they take up so much space):
select time,path,remote_address,remote_port,family,type,protocol
from bpf_socket_events where
remote_address not like '/%' and
remote_address not like '' and
remote_address not like '%7f0000:01' and
remote_address not like '127.0.0.%' and
remote_address not like '0.0.0.0' order by time desc;
s
sharvil
01/14/2022, 6:39 PMUmm I think if is matching with wildcard
%not likesharvil
01/14/2022, 6:41 PMIf you have a limited set of internal ip addresses, one could also do something like
select * from bpf_socket_events where remote_address not in ('127.0.0.1', '127.0.0.2');2 Views