https://github.com/osquery/osquery logo
#fleet
Title
# fleet
t

Tor Houghton

01/14/2022, 10:06 AM
What 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 PM
Hi! What table are you querying?
t

Tor Houghton

01/14/2022, 4:57 PM
bpf_socket_events; i wish to run a scheduled query for communication outside of my local network.
i 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 PM
OK, I'll check with the team.
s

sharvil

01/14/2022, 6:15 PM
hey @Tor Houghton, would you mind sharing your query — I can try and take a look
Usually 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 PM
I 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 PM
Umm I think if is matching with wildcard
%
, it requires multiple
not like
If 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