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?

This might be a better question for either #C01DXJL16D8 or #C08V7KTJB

@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 👍🏼

Have you narrowed down the performance issue to the query with the regex? How confident are you that is the root cause?

@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.

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

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! 👍