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 ?
On Thursday, February 13, 2020 at 10:24:36 AM UTC-5, Vladimir Botka wrote:
>
> On Thu, 13 Feb 2020 05:57:39 -0800 (PST)
> Anand Solomon <[email protected] <javascript:>> 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
> loop: "{{ scripts|dict2items }}"
>
> HTH,
>
> -vlado
>
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/ansible-project/8acfe745-4f9b-4fda-a5e3-ecdc85c90578%40googlegroups.com.
