KEY: VALUE iterations

[Anand Solomon]

Hi,

What is the best way to loop the below KEY : VALUE ?

main.yml
 
 var_files:
 - /home/ansible/sqlvar.yml
 
 - name: Script
 oracle_sql:
 sql: {{Value}}
 environment: "{{oracle_env}}"
 register: query_result
 connection: local
 
 - name: Runs statements
 command: $ORACLE_HOME/sqlplus -s {{user}}/{{password}}@{{sname}} @/home/ansible/Playbooks/{{sname}}/sql/{{Key}}_revoke.sql
 environment: "{{oracle_env}}"
 register: sqloutput
 connection: local
 
 ------
 sqlvar.yml
 
 Key: Value
 Script1: select username from dba_users;
 Script2: select file_name from dba_data_files;
 Script3: select name from v$profile;
 Script4: select username from dba_profile;

[Vladimir Botka]

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

> What is the best way to loop the below KEY : VALUE ?

> [...]

> sqlvar.yml
> Key: Value
> Script1: select username from dba_users;
> Script2: select file_name from dba_data_files;
> Script3: select name from v$profile;
> Script4: select username from dba_profile;

Try this

- include_vars:
file: /home/ansible/sqlvar.yml
name: scripts
- debug:
msg: "{{ item.key }}: {{ item.value }}"
loop: "{{ scripts|dict2items }}"

HTH,

-vlado

[Anand Solomon]

You helped me Vlado. Thanks again. This works very well. 

How will get the value of item.key and item.value to variable

        sqlqry = "{{ item.key }}"

        sqlid = "{{ item.value }}"

[Anand Solomon]

Basically I want to read sqlvar.yml, pass the "select username from dba_users;" to SQLID and "Script1" to SQLQRY  one by one.

[Vladimir Botka]

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

> How will get the value of item.key and item.value to variable
>
> sqlqry = "{{ item.key }}"
> sqlid = "{{ item.value }}"

Declare the vars in the scope of the task. For example

- debug:
msg: "{{ sqlqry }}: {{ sqlid }}"
vars:
sqlqry: "{{ item.key }}"
sqlid: "{{ item.value }}"

[Anand Solomon]

Thanks again Vlado. I am struggling with the basics here...

This is my block

    - include_vars:

        file: /home/ansible/Playbooks/Compliance1.yml

        name: scripts

    - debug:

        msg: "{{ sqlqry }}: {{ sqlid }}"

      vars:

        sqlqry: "{{ item.key }}"

        sqlid: "{{ item.value }}"

      loop: "{{ scripts|dict2items }}"

--------

The Complaince1.yml has the below 

 Script1: select username from dba_users;

 Script2: select name from v$profile;

 I am seeing my key & values in my output

TASK [debug] *****************************************************************************************************************************************************

ok: [tstdb.cdst2ld65goi.us-east-1.rds.amazonaws.com] => (item={'key': u'Script1', 'value': u'select username from dba_users;'}) => {

    "msg": "Script1: select username from dba_users;"

}

ok: [tstdb.cdst2ld65goi.us-east-1.rds.amazonaws.com] => (item={'key': u'Script2', 'value': u'select name from v$profile;'}) => {

    "msg": "Script2: select name from v$profile;"

My problem is, I don't know how to get the key (like "Script1") and value (like "select username from dba_users;") to another block as 

    - name: Script will revoke

      oracle_sql:

        username: "{{user}}"

        password: "{{password}}"

        service_name: "{{sname}}"

        port: "{{prt}}"

        sqlId: "{{hostnme}}"

        sql: "{{sqlqry}}'

    - name: Runs the revoke statements and spool out.

      command: $ORACLE_HOME/sqlplus -s {{user}}/{{password}}@{{sname}} @/home/ansible/Playbooks/{{sname}}/sql/{{sqlid}}_revoke.sql

      environment: "{{oracle_env}}"

      register: sqloutput

      connection: local

[Vladimir Botka]

On Thu, 13 Feb 2020 05:57:39 -0800 (PST)
Anand Solomon <anand.v...@gmail.com> wrote:

> - include_vars:
> file: /home/ansible/Playbooks/Compliance1.yml
> name: scripts
> - debug:
> msg: "{{ sqlqry }}: {{ sqlid }}"
> vars:
> sqlqry: "{{ item.key }}"
> sqlid: "{{ item.value }}"
> loop: "{{ scripts|dict2items }}"

> [...]

> The Complaince1.yml has the below
>
> Script1: select username from dba_users;
> Script2: select name from v$profile;

> [...]

> "msg": "Script1: select username from dba_users;"

> "msg": "Script2: select name from v$profile;"
>
> My problem is, I don't know how to get the key (like "Script1") and value
> (like "select username from dba_users;") to another block as
>
> - name: Script will revoke
> oracle_sql:
> username: "{{user}}"
> password: "{{password}}"
> service_name: "{{sname}}"
> port: "{{prt}}"
> sqlId: "{{hostnme}}"
> sql: "{{sqlqry}}'

vars:
sqlqry: "{{ item.key }}"
sqlid: "{{ item.value }}"
loop: "{{ scripts|dict2items }}"

Simply append "vars" and "loop".

But I think the above task "oracle_sql" won't work because of attribute
"sql". The documentation says "sql: The sql you want to execute" but
"item.key" is the label of the script. See
https://github.com/oravirt/ansible-oracle-modules/blob/master/oracle_sql

Instead, "item.value" is the SQL you want to execute. Correct syntax is
(substitution is not necessary)

- name: Script will revoke
oracle_sql:
username: "{{ user }}"
password: "{{ password }}"
service_name: "{{ sname }}"
port: "{{ prt }}"
sqlId: "{{ hostnme }}"

sql: "{{ item.value }}'
loop: "{{ scripts|dict2items }}"

> - name: Runs the revoke statements and spool out.
> command: $ORACLE_HOME/sqlplus -s {{user}}/{{password}}@{{sname}}
> @/home/ansible/Playbooks/{{sname}}/sql/{{sqlid}}_revoke.sql
> environment: "{{oracle_env}}"
> register: sqloutput
> connection: local

dtto. Change "sqlid" to "item.key"

- name: Runs the revoke statements and spool out.
command: $ORACLE_HOME/sqlplus -s {{user}}/{{password}}@{{sname}}

@/home/ansible/Playbooks/{{sname}}/sql/{{ item.key }}_revoke.sql

environment: "{{oracle_env}}"
register: sqloutput
connection: local

[Anand Solomon]

Hello Vlado,

sql works fine actually. No issues with that. I am getting the query results. Here is the result

    "msg": {

        "changed": false,

        "msg": "All items completed",

        "results": [

            {

                "ansible_facts": {

                    "discovered_interpreter_python": "/usr/bin/python"

                },

                "ansible_loop_var": "item",

                "changed": false,

                "failed": false,

                "invocation": {

                    "module_args": {

                        "host": "tstdb.cdst2lsongoi.us-east-1.rds.amazonaws.com",

                        "hostname": "tstdb.cdst2lsongoi.us-east-1.rds.amazonaws.com",

                        "mode": "normal",

                        "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",

                        "port": "1521",

                        "script": null,

                        "service_name": "tstdb",

                        "sql": "select name from v$database;",

                        "user": "DBUSER",

                        "username": "DBAUSER"

                    }

                },

                "item": {

                    "key": "Script1",

                    "value": "select name from v$database;"

                },

                "msg": [

                    [

                        "TSTDB"

                    ]

                ]

            }

I have jinja template embedded in my play book, it captures the whole json 

Thank you so much for your help !!!

- Anand

    - name: Generate Revoke Statement

      copy:

        dest: /home/ansible/Playbooks/{{sname}}/sql/{{item.key}}_revoke.sql

        content: |

         spool /home/ansible/Playbooks/{{sname}}/log/{{item.key}}.log

         {% for line in query_result.msg |flatten %}

         {{ line }}

         {% endfor %}

         spool off;

      delegate_to: localhost

      connection: local

      loop: "{{ scripts|dict2items }}"

How do I capture only the query result which is just the username ?

[Vladimir Botka]

> [...]

> How do I capture only the query result which is just the username ?

> >

> > - name: Script will revoke
> > oracle_sql:
> > username: "{{ user }}"
> > password: "{{ password }}"
> > service_name: "{{ sname }}"
> > port: "{{ prt }}"
> > sqlId: "{{ hostnme }}"
> > sql: "{{ item.value }}'
> > loop: "{{ scripts|dict2items }}"

I don't understand what "the query result which is just the username" means.
What result do you expect?

[Anand Solomon]

Sorry about that.

Here is what I get as the debug out when I run my loop. I need to parse the JSON and get the hightlighted value to my Jinja template.

    "msg": {

        "changed": false,

        "msg": "All items completed",

        "results": [

            {

                "ansible_facts": {

                    "discovered_interpreter_python": "/usr/bin/python"

                },

                "ansible_loop_var": "item",

                "changed": false,

                "failed": false,

                "invocation": {

                    "module_args": {

                        "host": "tstdb.cds56fb9ngoi.us-east-1.rds.amazonaws.com",

                        "hostname": "tstdb.cdst2nb9ngoi.us-east-1.rds.amazonaws.com",

                        "mode": "normal",

                        "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",

                        "port": "1521",

                        "script": null,

                        "service_name": "tstdb",

                        "sql": "select name from v$database;",

                        "user": "DBUSER",

                        "username": "DBUSER"

                    }

                },

                "item": {

                    "key": "Script1",

                    "value": "select name from v$database;"

                },

                "msg": [

                    [

                        "LCKTSTDB"

                    ]

                ]

[Vladimir Botka]

On Thu, 13 Feb 2020 09:25:16 -0800 (PST)
Anand Solomon <anand.v...@gmail.com> wrote:

> Here is what I get as the debug out when I run my loop. I need to parse the
> JSON and get the hightlighted value to my Jinja template.

I don't see anything highlighted. What am I missing?

--
Regards,
Vladimir

Vladimir Botka
vbo...@gmail.com
+420.773.041.182
Smeralova 13, Prague 17000, Czech
https://www.botka.link/
https://www.linkedin.com/in/vbotka/
--

[Anand Solomon]

Thanks again Vlado

I want to parse the json output and take the values 

Here is the json debug output from the playbook I run, 

TASK [Display the query results] ************************************************************************************************************

ok: "tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com] => {

    "msg": {

                "ansible_loop_var": "item",

                "changed": false,

                "failed": false,

                "invocation": {

                    "module_args": {

                        "host": "tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com",

                        "hostname": "tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com",

                        "mode": "normal",

                        "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",

                        "port": "1521",

                        "script": null,

                        "service_name": "tstdb",

                        "sql": "select 'Revoke ' || privilege || ' on UTL_FILE from ' || grantee || ';' from dba_tab_privs where table_name='UTL_FILE' and grantee not like 'APEX%' and grantee != 'CTXSYS' and grantee != 'DBSNMP'  order by grantee;",

                        "user": "SYSDBA",

                        "username": "SYSDBA"

                    }

                },

                "item": {

                    "key": "Script1",

                    "value": "select 'Revoke ' || privilege || ' on UTL_FILE from ' || grantee || ';' from dba_tab_privs where table_name='UTL_FILE' and grantee not like 'APEX%' and grantee != 'CTXSYS' and grantee != 'DBSNMP' and grantee != 'DMSYS' and grantee != 'EXFSYS' and grantee != 'MDSYS' order by grantee;"

                },

                "msg": [

                    [

                        "Revoke EXECUTE on UTL_FILE from TESTDBA;"

                    ],

                    [

                        "Revoke EXECUTE on UTL_FILE from TSTSCH;"

                    ]

                ]

            }

        ]

    }

}

-------------------------------------------------------

I just want to get  extract the below value and pass it on to my Jinja template.

                  [

                        "Revoke EXECUTE on UTL_FILE from TESTDBA;"

                    ],

                    [

                        "Revoke EXECUTE on UTL_FILE from TSTSCH;"

                    ]

Here is my Jinja template

    - name: Generate Revoke Statement

      copy:

        dest: /home/ansible/Playbooks/{{sname}}/sql/{{item.key}}_revoke.sql

        content: |

         spool /home/ansible/Playbooks/{{sname}}/log/{{item.key}}.log

         {% for line in query_result  | flatten %}

         {{ line }}

         {% endfor %}

         spool off;

      delegate_to: localhost

      connection: local

      loop: "{{ scripts|dict2items }}"

[Vladimir Botka]

There is nothing to parse. The result is a dictionary with the attributes
listed in "msg". Simply access the attributes of the dictionary to "take the
values".

[Anand Solomon]

Thank you so very much Vlado

 if I say   

- name: Display the query results

      debug:

        msg: "{{query_result.msg}}

I get the below

  "msg": "All items completed"

I couldn't get the below, what ever I put in my debug statement. Is there is something I should do in order to get the below messages ? Thanks again for your help.

                "msg": [

                    [

                        "Revoke EXECUTE on UTL_FILE from TESTDBA;"

                    ],

                    [

                        "Revoke EXECUTE on UTL_FILE from TSTSCH;"

                    ]

                ]

[Vladimir Botka]

On Thu, 13 Feb 2020 13:33:32 -0800 (PST)
Anand Solomon <anand.v...@gmail.com> wrote:

> - name: Display the query results
> debug:
> msg: "{{query_result.msg}}
>
> I get the below
> "msg": "All items completed"
>
> I couldn't get the below, what ever I put in my debug statement. Is there
> is something I should do in order to get the below messages ? Thanks again
> for your help.
>
> "msg": [
> [
> "Revoke EXECUTE on UTL_FILE from TESTDBA;"
> ],
> [
> "Revoke EXECUTE on UTL_FILE from TSTSCH;"
> ]
> ]

What you should do in order to get the above messages? Solve the mystery and
find out how you get the "msg" below. Perhaps?

[Anand Solomon]

Thanks Vlado,

I am new to Ansible. I tried "query_result.results' etc. But no luck. Will really appreciate and also will learn at the same time if you can help me crack the mystery.

Thanks for all your help.

[Stefan Hornburg (Racke)]

On 2/14/20 1:31 PM, Anand Solomon wrote:
> Thanks Vlado,
> I am new to Ansible. I tried "query_result.results' etc. But no luck. Will really appreciate and also will learn at the
> same time if you can help me crack the mystery.
>
> Thanks for all your help.

Amand, you need to use the loop in the debug task as well:

- name: Display the query results
debug:
msg: "{{query_result.msg}}

loop: "{{ scripts|dict2items }}"

Regards
Racke

>
> On Thursday, February 13, 2020 at 5:08:12 PM UTC-5, Vladimir Botka wrote:
>
> On Thu, 13 Feb 2020 13:33:32 -0800 (PST)
> Anand Solomon <anand....@gmail.com <javascript:>> wrote:
>
> > - name: Display the query results
> >       debug:
> >         msg: "{{query_result.msg}}
> >
> > I get the below
> >   "msg": "All items completed"
> >
> > I couldn't get the below, what ever I put in my debug statement. Is there
> > is something I should do in order to get the below messages ? Thanks again
> > for your help.
> >
> >                 "msg": [
> >                     [
> >                         "Revoke EXECUTE on UTL_FILE from TESTDBA;"
> >                     ],
> >                     [
> >                         "Revoke EXECUTE on UTL_FILE from TSTSCH;"
> >                     ]
> >                 ]
>
> What you should do in order to get the above messages? Solve the mystery and
> find out how you get the "msg" below. Perhaps?
>
>
> > > > TASK [Display the query results]
> > > >  
> > > ************************************************************************************************************
> > >  

> > > > ok: "tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com>] => {

> > > >     "msg": {
> > > >                 "ansible_loop_var": "item",
> > > >                 "changed": false,
> > > >                 "failed": false,
> > > >                 "invocation": {
> > > >                     "module_args": {
> > > >                         "host":

> > > > "tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com>",
> > > >                         "hostname":
> > > > "tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com>",

> --
> 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 <mailto:ansible-proje...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/ansible-project/16c46f66-32bb-4719-97a6-b63fb4883f17%40googlegroups.com
> <https://groups.google.com/d/msgid/ansible-project/16c46f66-32bb-4719-97a6-b63fb4883f17%40googlegroups.com?utm_medium=email&utm_source=footer>.


--
Ecommerce and Linux consulting + Perl and web application programming.
Debian and Sympa administration. Provisioning with Ansible.

[Anand Solomon]

Thanks Rache,

This didn't solve the issue. I need to read the content in the "msg" in "results" section 

> ansible...@googlegroups.com <mailto:ansible-project+unsub...@googlegroups.com>.

[Stefan Hornburg (Racke)]

On 2/14/20 2:10 PM, Anand Solomon wrote:
> Thanks Rache,
> This didn't solve the issue. I need to read the content in the "msg" in "results" section 
>

What's the output when you run it in a loop as suggested?

Regards
Racke

> <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com> <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com

> <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com>>] => {
> >     > > >     "msg": {
> >     > > >                 "ansible_loop_var": "item",
> >     > > >                 "changed": false,
> >     > > >                 "failed": false,
> >     > > >                 "invocation": {
> >     > > >                     "module_args": {
> >     > > >                         "host":
> >     > > > "tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com>

> <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com>>",
> >     > > >                         "hostname":
> >     > > > "tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com>
> <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com <http://tstdb.fdst3sb9ngoi.us-east-1.rds.amazonaws.com>>",

> > ansible...@googlegroups.com <javascript:> <mailto:ansible-proje...@googlegroups.com <javascript:>>.

> > To view this discussion on the web visit
> > https://groups.google.com/d/msgid/ansible-project/16c46f66-32bb-4719-97a6-b63fb4883f17%40googlegroups.com
> <https://groups.google.com/d/msgid/ansible-project/16c46f66-32bb-4719-97a6-b63fb4883f17%40googlegroups.com>
> >

> <https://groups.google.com/d/msgid/ansible-project/16c46f66-32bb-4719-97a6-b63fb4883f17%40googlegroups.com?utm_medium=email&utm_source=footer

> <https://groups.google.com/d/msgid/ansible-project/16c46f66-32bb-4719-97a6-b63fb4883f17%40googlegroups.com?utm_medium=email&utm_source=footer>>.
>
>
>
> --
> Ecommerce and Linux consulting + Perl and web application programming.
> Debian and Sympa administration. Provisioning with Ansible.
>

> --
> 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 <mailto:ansible-proje...@googlegroups.com>.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/ansible-project/e951706a-a6bd-44c9-9df5-7308bde5d65a%40googlegroups.com
> <https://groups.google.com/d/msgid/ansible-project/e951706a-a6bd-44c9-9df5-7308bde5d65a%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Anand Solomon]

That was reading the first msg

 "msg": "All items completed"

We want to read the msg in results

            "msg": [

                [

                    "Revoke EXECUTE on UTL_FILE from TESTDBA;"

                ],

                [

                    "Revoke EXECUTE on UTL_FILE from TSTSCH;"

                ]

            ]

>     > ansible...@googlegroups.com <javascript:> <mailto:ansible-project+unsub...@googlegroups.com <javascript:>>.

>     > To view this discussion on the web visit
>     > https://groups.google.com/d/msgid/ansible-project/16c46f66-32bb-4719-97a6-b63fb4883f17%40googlegroups.com
>     <https://groups.google.com/d/msgid/ansible-project/16c46f66-32bb-4719-97a6-b63fb4883f17%40googlegroups.com>
>     >
>     <https://groups.google.com/d/msgid/ansible-project/16c46f66-32bb-4719-97a6-b63fb4883f17%40googlegroups.com?utm_medium=email&utm_source=footer
>     <https://groups.google.com/d/msgid/ansible-project/16c46f66-32bb-4719-97a6-b63fb4883f17%40googlegroups.com?utm_medium=email&utm_source=footer>>.
>
>
>
>     --
>     Ecommerce and Linux consulting + Perl and web application programming.
>     Debian and Sympa administration. Provisioning with Ansible.
>
> --
> 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 <mailto:ansible-project+unsub...@googlegroups.com>.

[Anand Solomon]

Hi, I still don’t have an answer for this. Is there someone to help me ?