https://github.com/osquery/osquery logo
Powered by
Title
v

Vikas

03/30/2023, 4:20 PM
Is there any way we can query to find all the offline host and tag them through Fleet UI ?
k

Kathy Satterlee

03/30/2023, 4:22 PM
You can limit the host list by status:
Tell me more about the tagging aspect... what would you like to accomplish there?
v

Vikas

03/30/2023, 4:23 PM
I also want t use regex where hostnames starts with specific names and which are offline and tag them
k

Kathy Satterlee

03/30/2023, 4:24 PM
Ah, I see that additional info below.
For the names, things might get a little more complicated.
What's the point of the tag?
Might help me put it all together and help strategize
v

Vikas

03/30/2023, 4:26 PM
so we have hosts which are offline but are added into fleet, all the hostname starts with specific names, I wan to tag them and if that are offline more than 30 days I want to delete them.
k

Kathy Satterlee

03/30/2023, 4:27 PM
Gotcha. But you only want this to happen with specific hosts, not all hosts across the organization?
v

Vikas

03/30/2023, 4:27 PM
offline host that starts with specific names
so I guess it should run on all hosts
k

Kathy Satterlee

03/30/2023, 4:30 PM
Gotcha. I wanted to make sure that setting up `host_expiry` wasn't the best option for you.
I you only want specific hosts removed after 30 days, but keep others in Fleet, that won't do the trick.
v

Vikas

03/30/2023, 4:31 PM
😞
so no other way where we can do that
k

Kathy Satterlee

03/30/2023, 4:32 PM
It's definitely doable.. will just take some extra steps.
v

Vikas

03/30/2023, 4:33 PM
okay no worries , can we do a query on offline host ?
k

Kathy Satterlee

03/30/2023, 4:34 PM
Any query woudn't run until it was online again.
v

Vikas

03/30/2023, 4:35 PM
got it
k

Kathy Satterlee

03/30/2023, 4:36 PM
What I would likely do in this situation is build a script that would ping the 
/hosts
API for hosts that had been offline for 30 days, and then use the regex against the results.
v

Vikas

03/30/2023, 4:36 PM
Yeah got it. We have done that through API.
So I get thats the only option
k

Kathy Satterlee

03/30/2023, 4:37 PM
Awesome.
v

Vikas

03/30/2023, 4:37 PM
okay cool no worries. I will stick with script.
thanks a lot for all the help.
k

Kathy Satterlee

03/30/2023, 4:37 PM
Do you know what hosts you want to remove before they go offline?
Or does the whole workflow look more like: Hosts go offline > You figure out which you want to remove > The step we're currently discussing happens
There's potential for using a label if it's more: Certain hosts are flagged for removal if they go offline > They go offline > This step happens
v

Vikas

03/30/2023, 4:39 PM
we can still label it , when its online and then filter it out , when there are offline
k

Kathy Satterlee

03/30/2023, 4:40 PM
Then that would be a more UI - focused solution. Set up a label that queries the hostname using your regex Look for offline hosts with that label
v

Vikas

03/30/2023, 4:40 PM
okay cool
k

Kathy Satterlee

03/30/2023, 4:41 PM
As long as they're online to get enrolled in the label, you should be golden.
v

Vikas

03/30/2023, 4:42 PM
okay great , thanks a lot for all the info.
k

Kathy Satterlee

03/30/2023, 4:42 PM
Happy to help!
v

Vikas

03/30/2023, 4:49 PM
sorry to bother you again @Kathy Satterlee.
is this not valid regex 
^[a-z]-(?:[a-z]{1,10}-){1,2}[0-9]{1,15}\.(?:staging|prod|dev)$
?
k

Kathy Satterlee

03/30/2023, 4:51 PM
I'm personally terrible at regex 🙂 I usually pop things in https://regex101.com/
v

Vikas

03/30/2023, 4:52 PM
its working on that
k

Kathy Satterlee

03/30/2023, 4:53 PM
Seeing what you're testing for, I've gotta throw in a recommendation for Fleet Premium if that's an option for you... teams are a really useful feature.
v

Vikas

03/30/2023, 4:53 PM
https://regex101.com/r/IWrf8Y/1
k

Kathy Satterlee

03/30/2023, 4:53 PM
Looks like it's valid.
What's happening on the Fleet side?
v

Vikas

03/30/2023, 4:56 PM
not giving any result
Expecting to see results? Check to see if the hosts you targeted reported “Online” or check out the “Errors” table.
there is no error and host is online
k

Kathy Satterlee

03/30/2023, 4:57 PM
What's the full query?
v

Vikas

03/30/2023, 4:59 PM
SELECT * FROM osquery_info where regex_match('hostname','^\w-(?:\w+-){1,2}\d{1,15}\.(?:staging|prod|dev)\.(?:abc\.com)$',0) is not null;
k

Kathy Satterlee

03/30/2023, 4:59 PM
I'm about to jump into a meeting, but I'll poke at this a bit as soon as I can 🙂
v

Vikas

03/30/2023, 4:59 PM
no worries
https://regex101.com/r/IWrf8Y/1
working fine here
k

Kathy Satterlee

03/30/2023, 5:09 PM
Ah! Need to query the system_info table to get the hostname. try: 
SELECT 1 FROM system_info where regex_match(hostname,'^\w-(?:\w+-){1,2}\d{1,15}\.(?:staging|prod|dev)\.(?:abc\.com)$',0) is not null;
v

Vikas

03/30/2023, 5:13 PM
same results
If I do query like 
SELECT 1 FROM system_info where regex_match(hostname,'^d*',0) is not null;
it will return hostnames start with d
but not whole query is working, either I use system_info or osquery_info
k

Kathy Satterlee

03/30/2023, 5:16 PM
Interesting. At least you've got a start. Try querying a couple of hosts and just grabbing the hostname to make sure that lines up with what you're expecting: 
SELECT hostname FROM system_info
v

Vikas

03/30/2023, 5:17 PM
yes its working
k

Kathy Satterlee

03/30/2023, 5:28 PM
If you copy the returned hostname and plug it into the validator, does that work?
v

Vikas

03/30/2023, 5:39 PM
yess
its working now
thanks for all the help
do you know where can I find all the column details of particular tables ? Is there any document avaiable ?
k

Kathy Satterlee

03/30/2023, 6:47 PM
Glad you got it sorted!
https://fleetdm.com/tables/account_policy_data is a great resource for table information
#fleetJoin Slack
Powered by