I want to parse the json output and take the values
Here is the json 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' and grantee != 'DMSYS' and grantee != 'EXFSYS' and
> grantee not like 'FLOWS%' and grantee != 'MDSYS' and grantee != 'OLAPSYS'
> and grantee != 'ORACLE' and grantee != 'ORACLE_OCM' and grantee !=
> 'ORDPLUGINS' and grantee != 'ORDSYS' and grantee != 'SQLTXPLAIN' and
> grantee != 'SYSMAN' and grantee != 'SYSTEM' and grantor != 'SYS' order by
> grantee;",
> "user": "SYSDBA",
> "username": "SYSDBA"
> }
> },
> "item": {
> "key": "RDS_Guardium_Compliance_24",
> "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 not like 'FLOWS%' and grantee != 'MDSYS' and grantee != 'OLAPSYS'
> and grantee != 'ORACLE' and grantee != 'ORACLE_OCM' and grantee !=
> 'ORDPLUGINS' and grantee != 'ORDSYS' and grantee != 'SQLTXPLAIN' and
> grantee != 'SYSMAN' and grantee != 'SYSTEM' and grantor != 'SYS' order by
> grantee;"
> },
> "msg": [
> [
> "Revoke EXECUTE on UTL_FILE from TESTDBA;"
> ],
> [
> "Revoke EXECUTE on UTL_FILE from TSTSCH;"
> ]
> ]
> }
> ]
> }
> }
-------------------------------------------------------
I just want to get the one extract the below value and pass it on.
[
"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.results | flatten %}
{{ line }}
{% endfor %}
spool off;
delegate_to: localhost
connection: local
loop: "{{ scripts|dict2items }}"
-------------------------------------------------------------------------------
On Thursday, February 13, 2020 at 12:34:31 PM UTC-5, Vladimir Botka wrote:
>
> On Thu, 13 Feb 2020 09:25:16 -0800 (PST)
> Anand Solomon <[email protected] <javascript:>> 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?
>
>
> > "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"
> > ]
> > ]
> > }
> >
> > On Thursday, February 13, 2020 at 12:17:18 PM UTC-5, Vladimir Botka
> wrote:
> > >
> > > On Thu, 13 Feb 2020 08:32:08 -0800 (PST)
> > > Anand Solomon <[email protected] <javascript:>> wrote:
> > >
> > > > 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"
> > > > ]
> > > > ]
> > > > }
> > > >
> > > > [...]
> > > > 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?
> > >
> >
>
>
>
>
> --
> Regards,
> Vladimir
>
> Vladimir Botka
> [email protected] <javascript:>
> +420.773.041.182
> Smeralova 13, Prague 17000, Czech
> https://www.botka.link/
> https://www.linkedin.com/in/vbotka/
> --
>
--
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/dfcce0de-fd80-4fb7-acd5-d96d7dbee087%40googlegroups.com.
