Channels
#fleet
Title
# fleet
v
Vikas
03/30/2023, 4:20 PMIs 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 PMYou 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 PMI 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 PMAh, 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 PMso 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 PMGotcha. But you only want this to happen with specific hosts, not all hosts across the organization?
v
Vikas
03/30/2023, 4:27 PMoffline host that starts with specific names
so I guess it should run on all hosts
k
Kathy Satterlee
03/30/2023, 4:30 PMGotcha. 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 PMIt's definitely doable.. will just take some extra steps.
v
Vikas
03/30/2023, 4:33 PMokay no worries , can we do a query on offline host ?
k
Kathy Satterlee
03/30/2023, 4:34 PMAny query woudn't run until it was online again.
v
Vikas
03/30/2023, 4:35 PMgot it
k
Kathy Satterlee
03/30/2023, 4:36 PMWhat I would likely do in this situation is build a script that would ping the
/hostsv
Vikas
03/30/2023, 4:36 PMYeah got it. We have done that through API.
So I get thats the only option
k
Kathy Satterlee
03/30/2023, 4:37 PMAwesome.
v
Vikas
03/30/2023, 4:37 PMokay cool no worries. I will stick with script.
thanks a lot for all the help.
k
Kathy Satterlee
03/30/2023, 4:37 PMDo 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 PMwe can still label it , when its online and then filter it out , when there are offline
k
Kathy Satterlee
03/30/2023, 4:40 PMThen 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 PMokay cool
k
Kathy Satterlee
03/30/2023, 4:41 PMAs long as they're online to get enrolled in the label, you should be golden.
v
Vikas
03/30/2023, 4:42 PMokay great , thanks a lot for all the info.
k
Kathy Satterlee
03/30/2023, 4:42 PMHappy to help!
v
Vikas
03/30/2023, 4:49 PMsorry 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 PMI'm personally terrible at regex 🙂 I usually pop things in https://regex101.com/
v
Vikas
03/30/2023, 4:52 PMits working on that
k
Kathy Satterlee
03/30/2023, 4:53 PMSeeing 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 PMk
Kathy Satterlee
03/30/2023, 4:53 PMLooks like it's valid.
What's happening on the Fleet side?
v
Vikas
03/30/2023, 4:56 PMnot 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 PMWhat's the full query?
v
Vikas
03/30/2023, 4:59 PMSELECT * 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 PMI'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 PMno worries
working fine here
k
Kathy Satterlee
03/30/2023, 5:09 PMAh! 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
Vikas
03/30/2023, 5:13 PMsame results
If I do query like
SELECT 1 FROM system_info where regex_match(hostname,'^d*',0) is not null;but not whole query is working, either I use system_info or osquery_info
k
Kathy Satterlee
03/30/2023, 5:16 PMInteresting. 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_infov
Vikas
03/30/2023, 5:17 PMyes its working
k
Kathy Satterlee
03/30/2023, 5:28 PMIf you copy the returned hostname and plug it into the validator, does that work?
v
Vikas
03/30/2023, 5:39 PMyess
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 PMGlad you got it sorted!
https://fleetdm.com/tables/account_policy_data is a great resource for table information