Title
#core
a

Artem

01/13/2021, 2:13 PM
Hello everyone! Faced the following problem. We collect some parameters (such as a screensaver, disk encryption, etc.) from user machines through osquery/fleet via one query (with sub selects), and also collect information about the user’s current VPN IP address. The question is, what is the best way to get the user’s address through osquery, knowing only the VPN pool (several subnets)? Now I do it through interface_addresses table and match the address with a huge regex via regex_match function. I noticed that this greatly affects the performance of the query and in some hosts it even gets into the denylist because of the CPU threshold. Could you please tell me if there is some other way to get from the user a IP from the required subnet, if he has one?
z

Zach Zeid

01/13/2021, 2:14 PM
This might be a better question for either #fleet or #general
a

Artem

01/13/2021, 2:18 PM
@Zach Zeid hi! I assumed that the problem relates to the mechanisms of osquery itself, a similar request can be made outside the fleet, so I decided to write here. But no problem, I could duplicate it in general channel 👍🏼
theopolis

theopolis

01/13/2021, 2:42 PM
Have you narrowed down the performance issue to the query with the regex? How confident are you that is the root cause?
a

Artem

01/14/2021, 11:23 AM
@theopolis hello! Sorry for delay with answer. Not sure at all, but it was checked by visually analyzing CPU load through system monitor with/without regex subquery. In my opinion, without it, the request becomes 2 times easier. If it is possible to measure the load technically and collect some metrics, please tell me how, I’m ready to do it. If it is possible to find an IP from the desired subnet only through a regular expression, we would like to understand how much it is necessary to increase the watchdog parameters by CPU. Probably, this can only be done experimentally.
theopolis

theopolis

01/14/2021, 5:08 PM
You can do some basic benchmarking using
time
and the specific query:
/tmp  » time osqueryi --disable_extensions "select count(1) from users"
+----------+
| count(1) |
+----------+
| 105      |
+----------+
^[[Aosqueryi --disable_extensions "select count(1) from users"  0.06s user 0.03s system 27% cpu 0.346 total
/tmp  » time osqueryi --disable_extensions "select count(1) from users join users"
+----------+
| count(1) |
+----------+
| 11025    |
+----------+
osqueryi --disable_extensions "select count(1) from users join users"  2.55s user 1.79s system 45% cpu 9.470 total
5:10 PM
You may be able to speed up the query by substituting the regex for integer comparisons using the
inet_aton
method:
/tmp  » osqueryi --disable_extensions "select inet_aton('1.0.1.5') as ipInt"
+----------+
| ipInt    |
+----------+
| 16777477 |
+----------+
a

Artem

01/14/2021, 5:20 PM
These are great ideas! Thank you! Especially the second. I completely forgot that IP addresses can be translated into integer values. We use several subnets for the VPN pool, so I think I can implement the logic <>. Coupled with increasing watchdog values, this should help! 👍