https://github.com/osquery/osquery logo
Join Slack
Powered by
Is there any way we can query to find all the offl...
# fleet
v
Is there any way we can query to find all the offline host and tag them through Fleet UI ?
k
You can limit the host list by status:
Tell me more about the tagging aspect... what would you like to accomplish there?
v
I also want t use regex where hostnames starts with specific names and which are offline and tag them
k
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
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
Gotcha. But you only want this to happen with specific hosts, not all hosts across the organization?
v
offline host that starts with specific names
so I guess it should run on all hosts
k
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
😞
so no other way where we can do that
k
It's definitely doable.. will just take some extra steps.
v
okay no worries , can we do a query on offline host ?
k
Any query woudn't run until it was online again.
v
got it
k
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
Yeah got it. We have done that through API.
So I get thats the only option
k
Awesome.
v
okay cool no worries. I will stick with script.
thanks a lot for all the help.
k
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
we can still label it , when its online and then filter it out , when there are offline
k
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
okay cool
k
As long as they're online to get enrolled in the label, you should be golden.
v
okay great , thanks a lot for all the info.
k
Happy to help!
v
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
I'm personally terrible at regex 🙂 I usually pop things in https://regex101.com/
v
its working on that
k
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
k
Looks like it's valid.
What's happening on the Fleet side?
v
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
What's the full query?
v
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
I'm about to jump into a meeting, but I'll poke at this a bit as soon as I can 🙂
v
no worries
working fine here
k
Ah! Need to query the system_info table to get the hostname. try:
Copy code
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
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
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:
Copy code
SELECT hostname FROM system_info
v
yes its working
k
If you copy the returned hostname and plug it into the validator, does that work?
v
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
Glad you got it sorted!
https://fleetdm.com/tables/account_policy_data is a great resource for table information
Open in Slack
PreviousNext
Powered by