Script for validating osquery extensions were installed

[zhumo]

Problem

Once osquery extensions are installed, how can we verify that the extension has been successfully installed successfully?

Requirements

• Script to check that the extensions are successful

Proposal

osquery extensions remote management confirmation script

Pre-steps:

1. Create a canary team. Save the team ID.
2. Create a global query "Currently-installed extensions" with the SQL: SELECT * FROM osquery_extensions. Save the query ID.
3. Change the osquery extension settings for the canary team in osquery. Wait 5-10 min to allow the installation to take effect.
4. Get the hosts within the canary team. Save the list of host_ids.

   GET /hosts
   request body: { team_id: CANARY_TEAM_ID }
   

This can also be done via the fleetctl using fleetctl get hosts --team CANARY_TEAM_ID --json.
5. Get the extensions installed on all the hosts via the saved query

GET /queries/run
request body: { 
  query_ids: [QUERY_ID],
  host_ids: [HOST1, HOST2, etc.]
}

It should return something like this:

{
  "summary": {
    "targeted_host_count": 4,
    "responded_host_count": 2
  },
  "live_query_results": [
    {
      "query_id": 2,
      "results": [
        {
          "host_id": 1,
          "rows": [
            {
               "uuid": "asdfasdf",
               "name": "macadmins",
               "version": "1.2.3",
               "sdk_version": "1.2.3",
               "type": "extension",
               "path": "/var/lib/osquery/macadmins.em"
            }
          ],
          "error": null
        },
        {
          "host_id": 2,
          "rows": [],
          "error": "no such table: os_version"
        }
      ]
    }
  ]
}

This can also be done via the fleetctl using fleetctl query --hosts 1,2,3,etc. --query SELECT * FROM osquery_extensions.

NOTE:
An extension will update whenever the host wakes up and checks in with Fleet. Hosts which are not active will not update, for example, an end-user who has logged off for the day.

Similarly, a live query will stop waiting for results after 25 seconds. This essentially means it will only return results if the host is currently active. A host may have updated its extensions correctly but does not report results to the live query.

As such, we recommend giving your hosts some time to update and also make sure they are awake for the live query.

6. Loop through your list of host IDs and check that all of the expected extensions and versions are present in the returned results. Note which hosts returned errors or returned no response.

   print "Number hosts targeted: "
   print response.body["summary"]["targeted_host_count"]
   print "Number of hosts responded: "
   print response.body["summary"]["responded_host_count"]
   
   results = response.body["live_query_results"].find_by_query[QUERY_ID]["results"]
   
   expected_extensions = {
     "mac_admins": "1.2.3",
     "windows_admins": "2.3.4",
     "my_custom_extension": "3.4.5"
   }
   
   for host_id in HOST_IDS:
       host_results = results.find_by_host(host_id)
       if host_results.blank?
    	   print "No results for host #{host_id}"
    	   next
       end
       
       print "Results for #{host_id}:"
       if host_results["error"].present?
    	   print "Error: #{host_results["error"]}"
    	   next
       end
       
     host_extensions = host_results["rows"]
    	 for extension_name, expected_version in expected_extensions:
    	   print "checking for #{extension_name} v#{expected_version}"
    	   
    	   extension = host_extensions.find_by_extension(extension_name)
    	   if extension.blank?
    	     print: "#{extension} not found"
    	     next
    	   end
    	   if extension["version"] == expected_version
    		   print "#{extension_name} v#{expected_version} found!"
    	   else
    	     print "expected #{extension_name} v#{expected_version} but found #{extension["version"]}"
    	   end
    	 end
      end
    end
   end

7. If you're satisfied with the data, change the configs for the non-canary team. You can do this manually using the UI (settings > agent options or settings > team options > agent options) or via the API using this endpoint: https://fleetdm.com/docs/using-fleet/rest-api#modify-configuration.

PATCH /config
request body:
{
  agent_options: {
    ... # Other options
    extensions: [
      {
         "name": "new_extension",
         "channel": stable
       },
      {
        "name": "old_extension",
         "channel": edge
      }
    ]
  }
}

WATCH OUT: when you update the configuration, you must specify the full final configuration for the agent_options key -- the system will NOT merge the new config with the old one. This can also be done using the fleetctl apply command.
9. Once that has been updated, you can then re-run this script, but using the non-canary team's id for the same analysis.

[zhumo]

@mikermcneil @lucasmrod @sharvilshah @zwass WDYT of the proposal section above?

[lucasmrod]

Looks good!, couple of questions/comments:

• Re 2: API also allows to run a non-saved query too IIRC.
• Any reason to use API instead of fleetctl? (e.g. fleetctl get hosts --team <ID> --json && fleetctl query --query "SELECT * from osquery_extensions" --json ...)
• Just to confirm, if an extension is present in osquery_extensions then it means it is successfully "installed"?

[zhumo]

@lucasmrod

1. It doesn't look like it from here: https://fleetdm.com/docs/using-fleet/rest-api#run-live-query. But lmk if I'm not understanding correctly.
2. Yes, could use CLI... I will note that.
3. I believe so, but @sharvilshah maybe you can confirm this is the case?

[sharvilshah]

Looks good! And yep, when extension is present in osquery_extensions it is successfully registered