How to get clean output ?

[Anand Solomon]

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;

[Hugo Gonzalez]

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

Hugo G.

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;

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/2a69d90d-3ea8-4615-8f63-ace11f83c622%40googlegroups.com.

[Anand Solomon]

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 ?

On Tuesday, February 11, 2020 at 4:39:08 PM UTC-5, Hugo Gonzalez wrote:

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

Hugo G.

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;

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ansible...@googlegroups.com.

[Vladimir Botka]

On Tue, 11 Feb 2020 14:02:45 -0800 (PST)
Anand Solomon <anand.v...@gmail.com> wrote:

> "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 ?
>
>
> On Tuesday, February 11, 2020 at 4:39:08 PM UTC-5, Hugo Gonzalez wrote:
> > Without knowing anything about how you got those values, it would be {{
> > query_result.msg | join(' ') }}

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

[Vladimir Botka]

On Tue, 11 Feb 2020 14:02:45 -0800 (PST)
Anand Solomon <anand.v...@gmail.com> wrote:

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

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

[Anand Solomon]

Thank you so much Vlado. This works fine. 

[Anand Solomon]

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

[Vladimir Botka]

On Tue, 11 Feb 2020 14:59:04 -0800 (PST)
Anand Solomon <anand.v...@gmail.com> wrote:

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

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

[Anand Solomon]

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 ?

[Vladimir Botka]

On Wed, 12 Feb 2020 08:51:12 -0800 (PST)
Anand Solomon <anand.v...@gmail.com> wrote:

> - 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"}

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

[Anand Solomon]

Thank you so much Vlado.

It works fine.