IBM i Global

IBM i Global

Connect, learn, share, and engage with IBM Power.

 View Only
Expand all | Collapse all

Verifying network security parameters

  • 1.  Verifying network security parameters

    Posted Thu February 20, 2025 03:31 PM

    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
    ------------------------------


  • 2.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 05:52 AM

    How about using this freeware tool named SECTCP at http://easy400.net/sectcp/html/start.htm



    ------------------------------
    Satid S
    ------------------------------



  • 3.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 09:47 AM
    Edited by support Me Fri February 21, 2025 09:51 AM

    Thank you. Any other suggestion I would really appreciate too



  • 4.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 09:49 AM

    I had to check WRKCFGL on one of my lpars.  I was surprised to see I still had entries.  I thought I had cleared off all that sna stuff.



    ------------------------------
    support Me
    ------------------------------



  • 5.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 08:33 AM

    Some of those answers are at https://www.ibm.com/docs/en/i/7.5?topic=is-communication-services

    I had to check WRKCFGL on one of my lpars.  I was surprised to see I still had entries.  I thought I had cleared off all that sna stuff.



    ------------------------------
    Robert Berendt IBMChampion
    Business Systems Analyst, Lead
    Dekko
    Fort Wayne
    ------------------------------



  • 6.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 09:51 AM

    Hi thank you for your suggestions, I will check that link out.



    ------------------------------
    support Me
    ------------------------------



  • 7.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 10:12 AM

    Hi.

    Unfortunately, I have access to AS400 v7.4 and in this version there is no the view https://www.ibm.com/docs/en/ssw_ibm_i_75/rzajq/rzajqviewtelnetattr.htm  

    that only contains the telnet attributes, therefore none of those views that are listed on https://www.ibm.com/docs/en/i/7.4?topic=is-communication-services

    can help me. There is no a view for FTP attribute ALWSSL and the Secure Loc For Remote locations.

    Any other suggestions I would really appreciate



    ------------------------------
    support Me
    ------------------------------



  • 8.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 10:24 AM

    Mr. Me,

    You could try the APIs at https://www.ibm.com/docs/en/i/7.4?topic=category-communications-apis



    ------------------------------
    Robert Berendt IBMChampion
    Business Systems Analyst, Lead
    Dekko
    Fort Wayne
    ------------------------------



  • 9.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 10:20 AM

    Can you get to Navigator for i

    some of the info you want is there under TCP/IP Servers



    ------------------------------
    Rich Malloy
    Principal Systems Engineer - IBMi
    Cox Automotive
    Draper UT
    ------------------------------



  • 10.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 10:26 AM

    Hi Rich

    I need to get those actual values by command line some way because I would need them to process automation tasks, that's why with graphical interface is not useful for me.

    Any other suggestions I would really appreciate.



    ------------------------------
    support Me
    ------------------------------



  • 11.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 11:09 AM

    ok - this is a little convoluted but......this MAY work for you

    File QATMFTP in QUSRSYS contains the FTP configuration. Looks like line 11 is the value for Allowing SSL

    File QATMTELN in QURSYS contains the telnet configuration. Looks like Line 9 is the value for SSL . 

    I don't have an answer for the DSPCFGL   - hope the above helps some




    ------------------------------
    Rich Malloy
    Principal Systems Engineer - IBMi
    Cox Automotive
    Draper UT
    ------------------------------



  • 12.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 11:58 AM

    I really need a kind of SQL query or CL command to extract the value of "Allowing SSL" from the configuration files QATMFTP and QATMTELN. Is there any straigthforward strategy to achieve that goal? Thank you in advance for your help. 



    ------------------------------
    support Me
    ------------------------------



  • 13.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 12:29 PM

    Well you can submit an "idea" for such an enhancement at https://ibm-power-systems.ideas.ibm.com/ideas

    Or you can try the following while you wait

    select charfield as FTP_ALWSSL
    from qusrsys.QATMFTP
    where rid(QATMFTP) = 11;

    SELECT CHARFIELD AS TELNET_ALWSSL
    FROM QUSRSYS/QATMTELN
    WHERE RID(QATMTELN) = 7;

    Since this is a non published method there's no guarantee that the rid stays constant with each release/TR/etc.



    ------------------------------
    Robert Berendt IBMChampion
    Business Systems Analyst, Lead
    Dekko
    Fort Wayne
    ------------------------------



  • 14.  RE: Verifying network security parameters

    Posted Thu February 27, 2025 05:19 PM

    Hi Robert

    Thank you for your suggestion. When I executed I got this error


    "sql": "select charfield as FTP_ALWSSL from qusrsys.QATMFTP where rid(QATMFTP) = 11",
      "stdout": [],
      "stderr": "ibm_db_dbi::ProgrammingError: The column or table qualifier QATMTELN is not defined. SQLSTATE=42703 SQLCODE=-5001",
      "rc": 301,
      

    "sql": "SELECT CHARFIELD AS TELNET_ALWSSL FROM QUSRSYS.QATMTELN WHERE RID(QATMTELN) = 7;",
      "stdout": [],
      "stderr": "ibm_db_dbi::ProgrammingError: The column or table qualifier QATMTELN is not defined. SQLSTATE=42703 SQLCODE=-5001",



    ------------------------------
    support Me
    ------------------------------



  • 15.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 12:29 PM

    From what I can see, if you go with the SQL approach via these tables...you will have to use the RRN function.. For instance,  for telnet, the RRN is 7 (i mistaken when I noted '9'). This is based on me testing/verifying the value changed for that parm

    select * from qusrsys.QATMTELN
    where rrn(qusrsys.QATMTELN) = 7;

    for the FTP file -  QATMFTP - 

    select * from qusrsys. QATMFTP
    where rrn(qusrsys. QATMFTP) = 11

    I imagine this is the same for other versions of the O/S - but my testing was done only at a 7.4 machine



    ------------------------------
    Rich Malloy
    Principal Systems Engineer - IBMi
    Cox Automotive
    Draper UT
    ------------------------------



  • 16.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 01:10 PM

    Mine was at 7.5 TR5



    ------------------------------
    Robert Berendt IBMChampion
    Business Systems Analyst, Lead
    Dekko
    Fort Wayne
    ------------------------------



  • 17.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 01:16 PM

    Looks like a good candidate for RGZPFM, (not!!!!)



    ------------------------------
    Robert Berendt IBMChampion
    Business Systems Analyst, Lead
    Dekko
    Fort Wayne
    ------------------------------



  • 18.  RE: Verifying network security parameters

    Posted Thu February 27, 2025 05:12 PM

    Hi Rich

     Apparently I am getting the expected result as follows with your suggestion:

     RRN FTP:


       "query_ftp2.row": [
        {
          "CHARFIELD": "Y                                                                                                   "
        }
      ],
     
     
     RRN TELNET :


     "query_telnet2.row": [
        {
          "CHARFIELD": "Y                                                                                                   "
        }
      ],

    Could you tell me how can I display the content of the files QATMFTP and QATMTELN by executing a cl command or sql query? I should include that information as part of the automation to get the ALWSSL parameter of telnet and ftp respectively.

    thank you in advance for your help.



    ------------------------------
    support Me
    ------------------------------



  • 19.  RE: Verifying network security parameters

    Posted Thu February 27, 2025 05:21 PM

    I'm not sure If I am following what you are asking

    The following SQL statements give you what you are looking for right ?

    select * from qusrsys.QATMTELN
    where rrn(qusrsys.QATMTELN) = 7;

    for the FTP file -  QATMFTP - 

    select * from qusrsys. QATMFTP
    where rrn(qusrsys. QATMFTP) = 11

    Please try to clarify your question for me. thank you



    ------------------------------
    Rich Malloy
    Principal Systems Engineer - IBMi
    Cox Automotive
    Draper UT
    ------------------------------



  • 20.  RE: Verifying network security parameters

    Posted Thu February 27, 2025 05:27 PM

    Hi Rich

    That's right, apparently those queries give me the answer that I need, which is the value of ALWSSL parameter of telnet and ftp configuration. It is just that I need to confirm that it is exactly the value of that parameter, I was thinking that maybe these commands could help me to achieve that: "

    DSPPFM FILE(QUSRSYS/QATMFTP)
    DSPPFM FILE(QUSRSYS/QATMTELN)

    Or what do you suggest me?



    ------------------------------
    support Me
    ------------------------------



  • 21.  RE: Verifying network security parameters

    Posted Thu February 27, 2025 05:53 PM

    DSPPFM will show you that.

    The confusing thing is that this is the first time I've seen a system still use relative record number, or RRN, on a file in decades.  Normally someone would design a file like this with columns instead of rows.  So, each parameter on CHGTELNA would have it's own column.

    If one just HAD to do rows, they would use two columns.  like CREATE TABLE QTELNETA (Parameter_name char 10, Parameter_value varchar 10).  In this case IBM eschewed the Parameter_name column for just "knowing" that RRN x would stand for parameter_name y.

    Excuse my sql syntax if it is incorrect but hopefully you get the gist of it.



    ------------------------------
    Robert Berendt IBMChampion
    Business Systems Analyst, Lead
    Dekko
    Fort Wayne
    ------------------------------



  • 22.  RE: Verifying network security parameters

    Posted Thu February 27, 2025 09:00 PM

    i don't think those commands will tell you anything different than what the SQl shows you execpt that you will see "all values' in the file. Unfortunately, there is no column heading for each of the possible values. I know its RRN 'x' and 'y' based on testing and verification of values from a before and after perspective. 



    ------------------------------
    Rich Malloy
    Principal Systems Engineer - IBMi
    Cox Automotive
    Draper UT
    ------------------------------



  • 23.  RE: Verifying network security parameters

    Posted Fri February 28, 2025 11:05 AM

    Hi guys

    Unfortunately when I execute with my plabook:

    DSPPFM FILE(QUSRSYS/QATMFTP)
    DSPPFM FILE(QUSRSYS/QATMTELN)

    I get this error:  "CPD0031: not allowed in this setting.
    CPF0006:  Errors occurred in command"

    I asked the AS400 admin to check if he can get the output with those commands and I notice that the ouput doesn´t allow me to show the content of those files to show that ALWSSL has the "Y" value. Is there something like cat command that can help me in this case to show all the contents of the files "

    QATMFTP and QATMTELN

    Thank you for any suggestions



    ------------------------------
    support Me
    ------------------------------



  • 24.  RE: Verifying network security parameters

    Posted Fri February 28, 2025 11:25 AM

    There is nothing else to suggest.  You are just not getting the fact that the file does NOT contain anything to indicate that Relative Record Number 7 is the value you are looking for.

    If you were using Excel, and I told you to look at row 7, and I even had a formula which said =D7, would you keep asking me if there is something to say that D7 has the value I am looking for?  Or would you accept that on word?  This is basically the same exact thing.  The value for ALWSSL just happens to be in Row 7 of that file.  You can access that row by using the RRN.



    ------------------------------
    Robert Berendt IBMChampion
    Business Systems Analyst, Lead
    Dekko
    Fort Wayne
    ------------------------------



  • 25.  RE: Verifying network security parameters

    Posted Fri February 28, 2025 11:49 AM

    Hi Robert

    I understand completely what you say, then is there any CL command or SQL query that allow me to show all the contents fo the file "QATMTELN" and "QATMFTP" similar to CHGTELNA and pressing F4, and CHFTPA and pressing F4?



    ------------------------------
    support Me
    ------------------------------



  • 26.  RE: Verifying network security parameters
    Best Answer

    Posted Fri February 28, 2025 12:44 PM

    Here is a start.  When you finish it, you can also look up how to do a pivot table if you want.

    SELECT RRN(QATMTELN), 
    CASE
        WHEN RRN(QATMTELN) = 1 THEN 'AUTOSTART'
        WHEN RRN(QATMTELN) = 4 THEN 'DFTNVTTYPE'
        WHEN RRN(QATMTELN) = 7 THEN 'ALWSSL'
        ELSE 'TBD'
    END AS KEYWORD,
    CHARFIELD
    FROM QUSRSYS.QATMTELN

    This is the end for me.  I'm swamped.



    ------------------------------
    Robert Berendt IBMChampion
    Business Systems Analyst, Lead
    Dekko
    Fort Wayne
    ------------------------------



  • 27.  RE: Verifying network security parameters

    Posted Wed March 12, 2025 07:21 PM

    Thank you guys for all your help !! I finally accomplish the goals thanks to all your ideas



    ------------------------------
    support Me
    ------------------------------



  • 28.  RE: Verifying network security parameters

    Posted Thu March 13, 2025 10:08 AM

    Glad to hear you got it working



    ------------------------------
    Rich Malloy
    Principal Systems Engineer - IBMi
    Cox Automotive
    Draper UT
    ------------------------------



  • 29.  RE: Verifying network security parameters

    Posted Fri February 28, 2025 11:54 AM

    Hi Marc

    I think we are very close to the output with this sql query:

    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 =>'QPDCCFGL'))

    The only thing I don't know is how to figure when the spool file is ready to be recovered completely so that I can get the results like this with Ansible:

          "ORDINAL_POSITION": 11,
                "SPOOLED_DATA": " AS624568  APPN      S7845869  AS24568  APPN      *NO                                                                                                                                                                                                                                                                                                                                    "
            },
            {
                "ORDINAL_POSITION": 12,
                "SPOOLED_DATA": " DV0I      ECIJHYTE S7845869  VTAM02    ECIBMY6Y1  *NO                                                                                                                                                                                                                                                                                                                                    "
            },
            {
                "ORDINAL_POSITION": 13,
                "SPOOLED_DATA": " ECB6PHCC1  ECIBMHH1  S7845869  CTLUNIX   ECIBMY6Y1  *NO                                                                                                                                                                                                                                                                                                                                    "
            }

    How do I know how much I should wait to get the spool file ready



    ------------------------------
    support Me
    ------------------------------



  • 30.  RE: Verifying network security parameters

    Posted Fri February 28, 2025 12:19 PM

    you can view the whole file by select * from ..... or even runqry *n .......

    but as we keep saying - there are no columns to map to the values being returned.



    ------------------------------
    Rich Malloy
    Principal Systems Engineer - IBMi
    Cox Automotive
    Draper UT
    ------------------------------



  • 31.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 12:31 PM

    Rich,

    I don't think it's 9.  it's 7 for telnet. Try changing that value quickly, run you sql and change it back.



    ------------------------------
    Robert Berendt IBMChampion
    Business Systems Analyst, Lead
    Dekko
    Fort Wayne
    ------------------------------



  • 32.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 01:00 PM

    I caught that and posted the update..right around the time you did as well



    ------------------------------
    Rich Malloy
    Principal Systems Engineer - IBMi
    Cox Automotive
    Draper UT
    ------------------------------



  • 33.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 10:48 AM

    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
    ------------------------------



  • 34.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 10:58 AM

    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
    ------------------------------



  • 35.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 11:37 AM

    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
    ------------------------------



  • 36.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 11:57 AM

    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
    ------------------------------



  • 37.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 12:15 PM

    Thank you Marc !! I will test it and I will tell you that it helped me.



    ------------------------------
    support Me
    ------------------------------



  • 38.  RE: Verifying network security parameters

    Posted Fri February 21, 2025 05:59 PM

    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
    ------------------------------



  • 39.  RE: Verifying network security parameters

    Posted Sat February 22, 2025 05:27 AM

    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
    ------------------------------



  • 40.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 11:32 AM

    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
    ------------------------------



  • 41.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 01:37 PM
      |   view attached

    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, 10as 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
    ------------------------------

    Attachment(s)

    txt
    DSPLIBL.txt   946 B 1 version


  • 42.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 02:36 PM

    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
    ------------------------------



  • 43.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 04:11 PM

    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 yes, the SQL statement below shoud work:

    CALL QSYS2.QCMDEXC('DSPCFGL CFGL(QAPPNRMT) OUTPUT(*PRINT)');

    SELECT SPOOLED_DATA FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(SPOOLED_FILE_NAME =>'QPDCCFGL'
    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)));

    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 yes, try this (cannot test it, sorry):

    create variable qgpl.lowlimit integer;
    create variable qgpl.highlimit integer;

    CALL QSYS2.QCMDEXC('DSPCFGL CFGL(QAPPNRMT) OUTPUT(*PRINT)');

    set qgpl.lowlimit = 1+(SELECT ORDINAL_POSITION FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(SPOOLED_FILE_NAME =>'QPDCCFGL'
    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)))
    where substr(spooled_data, 110) = '----------');

    set qgpl.highlimit = -1+(SELECT ORDINAL_POSITION FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(SPOOLED_FILE_NAME =>'QPDCCFGL'
    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)))
    where substr(spooled_data, 110) = '----------' and ordinal_position > qgpl.lowlimit);

    SELECT SPOOLED_DATA FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA(SPOOLED_FILE_NAME =>'QPDCCFGL'
    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)))
    where ordinal_position between qgpl.lowlimit and qgpl.highlimit order by ordinal_position;

    drop variable qgpl.lowlimit;
    drop variable qgpl.highlimit;

    If you indeed need all the columns for each remote location, this is another challenge.



    ------------------------------
    Marc Rauzier
    ------------------------------



  • 44.  RE: Verifying network security parameters

    Posted Thu February 27, 2025 05:15 PM

    Hi Marc

    I don't know why it fails, since the ansible module uses "QSYS/RUNSQLSTM"  to execute sql scripts it fails. I am using this module: https://ibm.github.io/ansible-for-i/modules/ibmi_script.html



    ------------------------------
    support Me
    ------------------------------



  • 45.  RE: Verifying network security parameters

    Posted Fri February 28, 2025 04:34 AM

    I am not really comfortable with Ansible but as far as I understand, there is a way to gather the job log from the Ansible script ; it looks like job_log is available in the return value. Could you check that to get more information back? However, when there is an error in an SQL script with RUNSQLSTM command, this one creates a spool file to provide the root cause, which is not always written to the job log.

    On another way, RUNSQLSTM, by default, uses *SYS naming convention and the SQL statements I have provided uses *SQL naming convention. So you may want to try to add 'NAMING(*SQL)' as a parameter to your ibmi_script. Checkout https://www.ibm.com/docs/en/i/7.4?topic=ssw_ibm_i_74/cl/runsqlstm.html



    ------------------------------
    Marc Rauzier
    ------------------------------



  • 46.  RE: Verifying network security parameters

    Posted Mon February 24, 2025 02:16 PM

    Many things are stored in stream files (IFS) or in DB2 files.  However there are other object types in IBM i.  One of these are

    WRKOBJ OBJ(*ALL) OBJTYPE(*CFGL)

    So the previous hacks for looking at the objects does not apply here.

    About the last remaining hack I can think of, other than those previously suggested, is DMPOBJ.



    ------------------------------
    Robert Berendt IBMChampion
    Business Systems Analyst, Lead
    Dekko
    Fort Wayne
    ------------------------------