Re: Running OS commands from SQL

[Bryan Dietz <bdietz400@xxxxxxxxx>]

something to add.
after you cpysplf for anzdftpwd you may want to delet the spooled file
and make sure the PDF is secured.

lots of good data in there if "you" need a (spare) logon to the server....

Bryan


Rob Berendt wrote on 11/4/2021 1:52 PM:
> Works great!  My audit script runs really fast.  Then I pull that down to my PC, zip it up (along with the sql script from ACS) and send that to the audit.  Reports done so darn quick.
> I change the date from the "insert from examples" below:
>
> --  category:  IT Audits
> --  description:  DSPOBJAUT
> --
> -- Search for ROB and replace appropriately.
>
> CL:  CRTLIB M120180927 TEXT('Audit 2018-09-27');
> set current schema = 'M120180927';
> CL:  CHGCURLIB M120180927;
> CL:  MD '/home/ROB/M120180927';
> CL:  CD '/home/ROB/M120180927';
>
> CL: DSPUSRPRF USRPRF(*ALL) TYPE(*BASIC)
>                   OUTPUT(*OUTFILE) OUTFILE(DSPUSRPRF);
> CL:  CPYTOIMPF FROMFILE(DSPUSRPRF)
>            TOSTMF(DSPUSRPRF.csv)
>            MBROPT(*REPLACE)
>            STMFCCSID(*PCASCII)
>            RCDDLM(*CRLF)
>            ADDCOLNAM(*SYS);
>
> -- Use tools to convert spool file into pdf file
> CL:  OVRPRTF FILE(QSYSPRT)
>          TOSTMF('WRKSYSVAL.pdf')
>          DEVTYPE(*AFPDS)
>          WSCST(*PDF)
>          OVRSCOPE(*JOB);
> CL:  WRKSYSVAL OUTPUT(*PRINT);
> CL:  DLTOVR QSYSPRT LVL(*JOB);
>
> -- Use tools to convert spool file into pdf file
> CL:  ENDJOB JOB(ROB/QPRTJOB) OPTION(*IMMED) SPLFILE(*YES);
> CL:  ANZDFTPWD ACTION(*NONE);
> CL:  CPYSPLF FILE(QPSECPWD)
>          TOFILE(*TOSTMF)
>          JOB(ROB/QPRTJOB)
>          SPLNBR(*LAST)
>          TOSTMF('ANZDFTPWD.pdf')
>          WSCST(*PDF);
>
>
> -- Get list of objects users are authorized to:
> -- https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.OBJECT_PRIVILEGES%20View
> create table objectlist as (
> select *
> from QSYS2.OBJECT_PRIVILEGES
> where system_object_schema in('ERPLXF', 'ERPLXUSRF', 'QSYS', 'QSYS2')
> ) with data;
> CL:  CPYTOIMPF FROMFILE(OBJECTLIST)
>            TOSTMF(OBJECTLIST.csv)
>            MBROPT(*REPLACE)
>            STMFCCSID(*PCASCII)
>            RCDDLM(*CRLF)
>            ADDCOLNAM(*SYS);
>
> -- Get list of libraries users are authorized to:
> -- https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/QSYS2.OBJECT_PRIVILEGES%20View
> create table schemalist as (
> select *
> from QSYS2.OBJECT_PRIVILEGES
> where system_object_schema in('QSYS')
>    and system_object_name in('ERPLXF', 'ERPLXUSRF')
> ) with data;
> CL:  CPYTOIMPF FROMFILE(SCHEMALIST)
>            TOSTMF(SCHEMALIST.csv)
>            MBROPT(*REPLACE)
>            STMFCCSID(*PCASCII)
>            RCDDLM(*CRLF)
>            ADDCOLNAM(*SYS);
>
>
> Rob Berendt