How to get clean output ?

Archives Ansible Project
1

Hi,
How will I get only the sql statement from the json output ? I am registering this as " register: query_result"

Here is my json output

`

“msg”: {
“ansible_facts”: {
“discovered_interpreter_python”: “/usr/bin/python”
},
“changed”: false,
“failed”: false,
“msg”: [
[
“Revoke EXECUTE on UTL_FILE from TESTACC1;”
],
[
“Revoke EXECUTE on UTL_FILE from TESTACC2;”
]
]
}
}

`

If I do

  • local_action:
    copy content={{query_result.msg}}
    dest=/home/ansible/Playbooks/{{sname}}/sql/revoke.sql

I am getting
[[“Revoke EXECUTE on UTL_FILE from TESTACC1;”], [“Revoke EXECUTE on UTL_FILE from TESTACC2;”]]

I need to get this
Revoke EXECUTE on UTL_FILE from TESTACC1; Revoke EXECUTE on UTL_FILE from TESTACC2;

2

Without knowing anything about how you got those values, it would be {{ query_result.msg | join(’ ') }}

3

Thanks Hugo for helping me. Basically I am running a sql command that runs a sql query which gives the below results. (this is a debug out)

“msg”: {
“ansible_facts”: {
“discovered_interpreter_python”: “/usr/bin/python”
},
“changed”: false,
“failed”: false,
“msg”: [
[
“Revoke EXECUTE on UTL_FILE from TESTACC1;”
],
[
“Revoke EXECUTE on UTL_FILE from TESTACC2;”
]
]
}
}

What I need to do is to generate another sql statement using this json output. When I run your command, it gives me
[u’Revoke EXECUTE on UTL_FILE from TESTACC1;‘][u’Revoke EXECUTE on UTL_FILE from TESTACC2;’]

Is there a filter or something we can use to get a cleaner output ?

4

Try "template". For example

    - local_action:
        template src=revoke.sql.j2
        dest=/home/ansible/Playbooks/{{sname}}/sql/revoke.sql

with the template

$ cat revoke.sql.j2
{% for line in query_result.msg|flatten %}
{{ line }}
{% endfor %}

give

$ cat revoke.sql
Revoke EXECUTE on UTL_FILE from TESTACC1;
Revoke EXECUTE on UTL_FILE from TESTACC2;

HTH,

  -vlado

5

To keep the element of the list in one line change the template. For example

$ cat revoke.sql.j2
{% for line in query_result.msg|flatten %}{{ line }}{% endfor %}

HTH,

  -vlado

6

Thank you so much Vlado. This works fine.

7

Instead of maintaining another template file. Can I embed the content in the playbook itself ?

8

Sure you can. Put it into the "content". For example

    - local_action:
        copy dest=/scratch/tmp/revoke.sql
        content="{% for line in query_result.msg|flatten %}{{ line }}{%
        endfor %}"

But, why to get rid of the flexibility and modularity?

HTH,

  -vlado

9

Thanks Vlado,
We don’t want to maintain separate files.

Here is my

  • name: Generate Revoke Statement
    local_action:
    copy dest= /home/ansible/query.sql
    content= {% for line in query_result.msg|flatten %} {{ line }} {% endfor %}
    connection: local

I get → localhost]: FAILED! => {“changed”: false, “msg”: “dest is required”}

Is that something I am missing ?

10

Very probably the problem is the formatting and indentation of the "shorthand
syntax". Try this

    - name: Generate Revoke Statement
      copy:
        dest: /home/ansible/query.sql
        content: |
          {% for line in query_result.msg|flatten %} {{ line }} {% endfor %}
      delegate_to: localhost
      connection: local

HTH,

  -vlado

11

Thank you so much Vlado.

It works fine.