OK, so, you have the Secure Location information in the "Segura loc" column. Does the output you got fit your needs without further action?
If you indeed need a database format, do you want all the columns or is providing only the first group of information (up to "Ubic segura") possible to fit your needs?
If you indeed need all the columns for each remote location, this is another challenge.
Original Message:
Sent: Mon February 24, 2025 02:36 PM
From: support Me
Subject: Verifying network security parameters
Hi Marc
The output is similar to this one: https://www.ibm.com/support/pages/creating-remote-configuration-list-or-adding-entry-existing-list-wrkcfgl-qappnrmt but in spanish:
Visualizar lista configuración Página 1
DEMO1 DEMO1 455878 SXXXXV 24/01/25 18:00:12 UTC-05:00S
Lista de configuración . . . . . . : CFGD QAPPNRMT
Tipo de lista de configuración . . : CFGTYPE *APPNRMT
Texto . . . . . . . . . . . . . . : TEXT
------------------Ubicaciones remotas APPN------------------
ID de Punto de ID red
Ubic red Ubic control punto de Ubic
Remota remota Local remoto control segura
*ANY FGHYUUI STRETYY VTAM01 UYTEFEFG *NO
AS78QN SXXXXX7 AS27045 APPN APPN *NO
DV023 TREWWWD S7654707 VTAM02 UYTEFEFG *NO
-------------------Ubicaciones remotas APPN-------------------
ID de Punto
Ubic red Ubic Sesión Núm control Sesión
Remota remota Local única convers local preest
*ANY FGHYUUI STRETYY *NO 10 *NO *NO
AS78QN SXXXXX7 AS27045 *NO 10 *NO *NO
DV023 TREWWWD S7654707 *NO 10 *NO *NO
-------------Ubicaciones remotas APPN-------------
ID de
Ubic red Ubic
Remota remota Local Texto
*ANY FGHYUUI S7654707
AS78QN SXXXXX7 S7654707
DV023 TREWWWD S7654707
* * * * * F I N D E L I S T A D O * * * * *
------------------------------
support Me
Original Message:
Sent: Mon February 24, 2025 01:37 PM
From: Marc Rauzier
Subject: Verifying network security parameters
Hello
Yes, you are right, this is a similar solution.
However, do you really need an output properly formatted like a database report? Unfortunately I don't have an access to a system with configured configuration lists, therefore I cannot retrieve the output format of DSPCFGL command.
I can simulate it with DSPLIBL command output. This shows like the attached file. Is a similar simple output could fit your needs?
It would be nice if you can get an example of the output from DSPCFGL command to check if without any change it could fit your needs.
If it does not, we do need an example of the output from DSPCFGL command. There are ways to create an SQL script without hard coding positions and length.
Example with DSPLIBL to provide only the Library column:
create variable qgpl.LibraryPosition integer;
set qgpl.LibraryPosition = (select min(locate_in_string(spooled_data, 'Library')) from table(systools.spooled_file_data(job_name=>'103859/DIMARCO/MARCOA1', SPOOLED_FILE_NAME =>'QPRTLIBL')) where locate_in_string(spooled_data, 'Library') <> 0);
select substr(spooled_data, qgpl.LibraryPosition, 10) as Library from table(systools.spooled_file_data(job_name=>'103859/DIMARCO/MARCOA1', SPOOLED_FILE_NAME =>'QPRTLIBL')) where
spooled_data not like '%Library%' and
spooled_data not like '%ASP%' and
spooled_data not like '%E N D%' and
rtrim(ltrim(spooled_data)) not like (select rtrim(ltrim(system_value)) from QSYS2.SYSTEM_VALUE_INFO where system_value_name = 'QPRTTXT');
drop variable qgpl.LibraryPosition;
Similar piece of code can be used for more colums. However, this method requires the language of the spooled file to be in English. We can set the job in English if needed, but the language must exist on the system. If running within an Ansible session, we can imagine sending some variables to the script to set the Text values to test.
------------------------------
Marc Rauzier
Original Message:
Sent: Mon February 24, 2025 11:31 AM
From: support Me
Subject: Verifying network security parameters
Hi Marc.
Apparently the solution that you are indicating to me is similar to this one: https://www.rpgpgm.com/2019/05/using-sql-to-retrieve-data-from-spooled.html
Is that correct? Honestly, I do not know how to figure out the position and lenght for characters because I do not have direct access to the AS400 system, I just must automate the verification with ansible, that´s why I need something like a query similar to this one: https://www.ibm.com/support/pages/verifying-ddm-tcpip-attribute-lowest-authentication-method-password-required-current-setting . If there is a less complex strategy would be more useful to me, otherwise I have to continue researching based on the idea that you are giving to me.
------------------------------
support Me
Original Message:
Sent: Sat February 22, 2025 05:26 AM
From: Marc Rauzier
Subject: Verifying network security parameters
According to https://www.ibm.com/docs/en/i/7.4?topic=services-spooled-file-info-table-function:
ENDING_TIMESTAMP is not a column which is provided by the table function, therefore you cannot use it in the ORDER BY clause.
However, it should work with CREATION_TIMESTAMP, as this one is provided by the table function. So you may double check your syntax. To verify the column name, just try (with iACS), something like "SELECT * TABLE(QSYS2.SPOOLED_FILE_INFO(USER_NAME => '*CURRENT'));". This statement will show you all the available column names.
------------------------------
Marc Rauzier
Original Message:
Sent: Fri February 21, 2025 05:59 PM
From: support Me
Subject: Verifying network security parameters
Hi Marc !!!
Thank you so much for your suggestions !! However, I was trying to run it and there is a sintaxis error or something because I got "Creation_timestamp" doesn't exist, I was trying this query too but I have the same error with "ENDING_TIMESTAMP":
SELECT QUALIFIED_JOB_NAME FROM TABLE(QSYS2.SPOOLED_FILE_INFO(
USER_NAME => '*CURRENT',
STATUS => '*READY *HELD'
))
ORDER BY ENDING_TIMESTAMP DESC
FETCH FIRST 1 ROW ONLY
Thank you in advance for any ideas to solve the issue
------------------------------
support Me
Original Message:
Sent: Fri February 21, 2025 11:57 AM
From: Marc Rauzier
Subject: Verifying network security parameters
SELECT * FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(JOB_NAME=>(
SELECT QUALIFIED_JOB_NAME FROM TABLE(QSYS2.SPOOLED_FILE_INFO(USER_NAME => '*CURRENT')) WHERE SPOOLED_FILE_NAME = 'thenameofspooledefileproducedbyDSPCFGLsuchasQSYSPRT' ORDER BY CREATION_TIMESTAMP DESC FETCH FIRST 1 ROWS ONLY), SPOOLED_FILE_NAME =>'thenameofspooledefileproducedbyDSPCFGLsuchasQSYSPRT')
);
Adding a selection to spooled file name might be better to ensure you are retrieving the correct job.
------------------------------
Marc Rauzier
Original Message:
Sent: Fri February 21, 2025 11:37 AM
From: Marc Rauzier
Subject: Verifying network security parameters
Try this (as an SQL only script);
CALL QSYS2.QCMDEXC('DSPCFGL CFGL(QAPPNRMT) OUTPUT(*PRINT)');
SELECT * FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(JOB_NAME=>
(SELECT QUALIFIED_JOB_NAME FROM TABLE(QSYS2.SPOOLED_FILE_INFO(USER_NAME => '*CURRENT'))
ORDER BY CREATION_TIMESTAMP DESC FETCH FIRST 1 ROWS ONLY),
SPOOLED_FILE_NAME =>'thenameofspooledefileproducedbyDSPCFGLsuchasQSYSPRT'));
The use of the embedded select over QSYS2.SPOOLED_FILE_INFO is required in case you run the script from iACS, as the spooled files are not owned by the current job but by a QPRTJOB named job with an unknown job number.
Order by and fetch first are here to display only the very last spool file.
But again, you may ask knowing people to review APPC/APPN setup and clean it if no longer used. Within a security/audit context, this is an healthy behavior just like not starting not needed IP services.
------------------------------
Marc Rauzier
Original Message:
Sent: Fri February 21, 2025 10:57 AM
From: support Me
Subject: Verifying network security parameters
Hi Marc
I am just trying to automate some tasks that I have to but I actually do not know if technicians are still using APPC/APPN connections. But they appear with the command DSPCFGL as you say. I understand what you say about spooled-file-data-table-function, however I was trying to find a query tha allow me to get the data more quickly and straightforward, like this example https://www.ibm.com/support/pages/verifying-ddm-tcpip-attribute-lowest-authentication-method-password-required-current-setting
------------------------------
support Me
Original Message:
Sent: Fri February 21, 2025 10:47 AM
From: Marc Rauzier
Subject: Verifying network security parameters
Regarding #1, do you really still use APPC/APPN based connections (if yes for sure based on a HPR/IP aka Enterprise Extender setup)?
If yes, you can output the DSPCFGL command to a spool file, then display this spool file through SQL with https://www.ibm.com/docs/en/i/7.4?topic=services-spooled-file-data-table-function.
If no, you can simply delete all configuration lists. And use https://www.ibm.com/docs/en/i/7.4?topic=services-object-statistics-table-function with a selection on *CFGL object type to output an empty list showing that there is no more any configurgation list.
------------------------------
Marc Rauzier
Original Message:
Sent: Wed February 19, 2025 06:44 PM
From: support Me
Subject: Verifying network security parameters
I must get the actual value of some network security parameters, therefore I need to know:
1. How to view "Secure Loc" of remote locations without prompting on "DSPCFGL CFGL(QAPPNRMT)"
2. How to view "ALWSSL" without prompting on "CHGTELNA" and pressing F4
3. How to view "ALWSSL" without prompting on "CHGFTPA" and pressing F4
I really need an answer similar to this one: https://www.ibm.com/support/pages/verifying-ddm-tcpip-attribute-lowest-authentication-method-password-required-current-setting
Thank you in advance for your help
------------------------------
bluehat MAR
------------------------------