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
Try this
- include_vars:
file: /home/ansible/sqlvar.yml
name: scripts
- debug:
msg: "{{ item.key }}: {{ item.value }}"
loop: "{{ scripts|dict2items }}"
HTH,
-vlado
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 }}”
Basically I want to read sqlvar.yml, pass the “select username from dba_users;” to SQLID and “Script1” to SQLQRY one by one.
Declare the vars in the scope of the task. For example
- debug:
msg: "{{ sqlqry }}: {{ sqlid }}"
vars:
sqlqry: "{{ item.key }}"
sqlid: "{{ item.value }}"
loop: "{{ scripts|dict2items }}"
HTH,
-vlado
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 belowScript1: 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
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 ?
I don't understand what "the query result which is just the username" means.
What result do you expect?
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”
]
]
}
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?
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;”
]
]
}
]
}
}
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".
Thank you so very much Vlado
if I say
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;”
]
]
- 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?
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.
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
Thanks Rache,
This didn’t solve the issue. I need to read the content in the “msg” in “results” section
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
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;”
]
]