Hi all! I have a series of queries I pulled from https://github.com/chainguard-dev/osquery-defense-kit - they are all in .sql format, and it looks like fleetctl ingests yaml files for queries. Do I need to convert them, or is there some method to pull them in as-is?

hey @Mike S. - you can follow the guide for importing groups of queries via fleetctl https://fleetdm.com/guides/import-and-export-queries-in-fleet#basic-article

Thank you Zay! It look like we'll need to convert these from the SQL format to a YAML format that Fleet recognizes. Are there any conversion tools available? I have around 270 queries I would need to convert manually.

Hi @Mike S. We don't have any official linting / tooling for an operation like this (that I know of, there may be some cool yaml population thing on Homebrew...) but i think this is something you could do with sed, kind of a find & replace operation. If you can give me a couple of examples of what you are trying to put in the yaml I may be able to give an example of how to do this with a shell script. Thanks!

@Mike S. sorry! i missed that part of the question

@Zay Hanlon No worries!

Hi @Mike S. So, this is a little hacky, but should give you the idea. It is totally dependent on the formatting you sent above (I am using shell commands to parse the format...)

#!/bin/bash

sqlfile='/Users/brock/Desktop/sql.txt'
tmpfile='/private/tmp/tmp.yml'
ymlfile='/Users/brock/Desktop/sql.yml'

while read -r line
do
    case "$line" in
        *.sql ) sqlarry+=("$line") ;;
    esac
done < "$sqlfile"

for i in "${sqlarry[@]}"
do
    sqlrcrd="$(/usr/bin/sed -n "/$i/,/^----/p" "$sqlfile")"
    sqlname="$(echo "$sqlrcrd" | /usr/bin/sed -n '1p')"
    sqldesc="$(echo "$sqlrcrd" | /usr/bin/sed -n 's/-- //g;2p')"
    sqlqrry="$(echo "$sqlrcrd" | /usr/bin/awk '/^-- platform:/{flag=1;next}/^----.*/{flag=0}flag' | /usr/bin/sed 's/[[:space:]]//g' | /usr/bin/tr '\n' ' ' | /usr/bin/sed 's/[[:space:]]*$//g')"

    /usr/bin/touch "$tmpfile"
    /opt/homebrew/bin/yq -i ".apiVersion=\"v1\" | .kind=\"query\" | .spec.description=\"$sqldesc\" | .spec.name=\"$sqlname\" | .spec.query=\"$sqlqrry\"" "$tmpfile"
    cat "$tmpfile" >> "$ymlfile"
    echo '---' >> "$ymlfile"
    /bin/rm -f "$tmpfile"
done

Running the script on the text you posted above results in the following .yml output:

apiVersion: v1
kind: query
spec:
  description: Retrieves the list of the latest logins with PID, username and timestamp.
  name: last.sql
  query: SELECT * FROM last;
---
apiVersion: v1
kind: query
spec:
  description: Return the list of configured DNS servers on this system
  name: dns_resolves.sql
  query: SELECT * FROM dns_resolvers;
---

This is great, thank you!