Salt stack grains data from sqlite3 external pillar

Question

I am trying to pull results from a sqlite3 database (set up as an external salt pillar) and use jinja templating to set grains data.

Here is the relevant section of my salt master file:

sqlite3:
    database: '/var/lib/salt/stations.db'
    timeout: 5.0

ext_pillar:
    - sqlite3:
        fromdb:
            query: 'SELECT * FROM table;'

And here is the relevant part of the init.sls file I am using to create the grains file:

{% set station_id = salt['grains.filter_by']({
    {% for row in query_result %}
    {% hostname = station_id %}
}, default="UNKNOWN", grain="host") %}

I confirmed that the external pillar produces results by running

salt '*' sqlite3.fetch /var/lib/salt/stations.db 'SELECT * FROM test;'

But I can't figure out how to get results into the jinja file.

I want something like

'SELECT * FROM table WHERE hostname=station_id LIMIT 1;'

and use the result to set the grain environmental variable called 'hostname'.

But am not sure how to get there from here.

Any help is greatly appreciated.

Answer 1

Thanks to the good folks in Saltstack IRC this problem is solved.

Master:

- sqlite3:
    station_map:
        query: 'SELECT hostname, id
                  FROM stations
                WHERE hostname like ?'

init.sls:

{% set station_id = salt['grains.filter_by']({
    {% for row in station_map %}
    {{ hostname }} : {{ station_id }}
}, default="UNKNOWN", grain="host") %}