Getting a List of All TCP/IP Addresses Used in *LAN Printer Device Descriptions

Steps

Overall procedure:

Step 1: Clear QTEMP library (cleanup if needed)
Step 2: Create source physical file
Step 3: Retrieve configuration source for all device descriptions
Step 4: Filter relevant configuration data
Step 5: Add record numbers to maintain line order
Step 6: Group multi-line commands together
Step 7: Join multi-line commands into single strings
Step 8: Parse command parameters into structured columns
Step 9: Query the final results
 

SQL code with comments:

-- =====================================================
-- IBM i Printer Configuration Extraction Script
-- =====================================================
-- Purpose: Extract and parse printer device configurations
-- Target: IBM i ACS Run SQL Scripts
-- =====================================================
-- Step 1: Clear QTEMP library (cleanup if needed)
CL:CLRLIB QTEMP;
-- Step 2: Create source physical file
CL:CRTSRCPF FILE(QTEMP/QTXTSRC);
-- Step 3: Retrieve configuration source for all device descriptions
CL:RTVCFGSRC CFGD(*ALL) CFGTYPE(*DEVD) SRCFILE(QTEMP/QTXTSRC)
SRCMBR(PRINTERS) RTVOPT(*OBJ);
-- Step 4: Filter relevant configuration data
CREATE TABLE QTEMP.WORK AS (
SELECT SRCDTA
FROM QTEMP.QTXTSRC
WHERE (
srcdta LIKE '%DEVD%'
OR srcdta LIKE '%CRTDEVPRT%'
OR srcdta LIKE '%MFRTYPMDL(%'
OR srcdta LIKE '%WSCST(%'
OR srcdta LIKE '%TRANSFORM(*%'
OR srcdta LIKE '%TEXT(%'
OR srcdta LIKE '%PORT(%'
OR srcdta LIKE '%RMTLOCNAME(%'
)
) WITH DATA;
-- Step 5: Add record numbers to maintain line order
CREATE TABLE QTEMP.WORK_NUMBERED AS (
SELECT RRN(QTEMP.WORK) AS recno,
SRCDTA
FROM QTEMP.WORK
) WITH DATA;
-- Step 6: Group multi-line commands together
CREATE TABLE QTEMP.WORK_GROUPED AS (
SELECT SRCDTA,
recno,
SUM(CASE
WHEN TRIM(SRCDTA) LIKE 'CRTDEVPRT%' THEN 1
ELSE 0
END) OVER (ORDER BY recno) AS CMDGROUP
FROM QTEMP.WORK_NUMBERED
) WITH DATA;
-- Step 7: Join multi-line commands into single strings
CREATE TABLE QTEMP.WORK_JOINED AS (
SELECT CMDGROUP,
LISTAGG(TRIM(TRAILING '+' FROM TRIM(SRCDTA)), ' ')
WITHIN GROUP (ORDER BY recno) AS JOINED_COMMAND
FROM QTEMP.WORK_GROUPED
WHERE CMDGROUP > 0
GROUP BY CMDGROUP
) WITH DATA;
-- Step 8: Parse command parameters into structured columns
CREATE TABLE QTEMP.WORK_FIN AS (
SELECT
-- Device Name
CAST(
CASE WHEN LOCATE('DEVD(', JOINED_COMMAND) > 0 THEN
SUBSTR(JOINED_COMMAND,
LOCATE('DEVD(', JOINED_COMMAND) + 5,
LOCATE(')', JOINED_COMMAND, LOCATE('DEVD(', JOINED_COMMAND)) -
LOCATE('DEVD(', JOINED_COMMAND) - 5)
ELSE NULL END
AS CHAR(10)) AS DEVD,
    -- Manufacturer Type/Model
    CAST(
        CASE WHEN LOCATE('MFRTYPMDL(', JOINED_COMMAND) > 0 THEN 
            SUBSTR(JOINED_COMMAND, 
                   LOCATE('MFRTYPMDL(', JOINED_COMMAND) + 10, 
                   LOCATE(')', JOINED_COMMAND, LOCATE('MFRTYPMDL(', JOINED_COMMAND)) - 
                   LOCATE('MFRTYPMDL(', JOINED_COMMAND) - 10)
        ELSE NULL END 
    AS CHAR(10)) AS MFRTYPMDL,
    
    -- Transform
    CAST(
        CASE WHEN LOCATE('TRANSFORM(', JOINED_COMMAND) > 0 THEN 
            SUBSTR(JOINED_COMMAND, 
                   LOCATE('TRANSFORM(', JOINED_COMMAND) + 10, 
                   LOCATE(')', JOINED_COMMAND, LOCATE('TRANSFORM(', JOINED_COMMAND)) - 
                   LOCATE('TRANSFORM(', JOINED_COMMAND) - 10)
        ELSE NULL END 
    AS CHAR(10)) AS TRANSFORM,
    
    -- Port Number
    CAST(
        CASE WHEN LOCATE('PORT(', JOINED_COMMAND) > 0 THEN 
            SUBSTR(JOINED_COMMAND, 
                   LOCATE('PORT(', JOINED_COMMAND) + 5, 
                   LOCATE(')', JOINED_COMMAND, LOCATE('PORT(', JOINED_COMMAND)) - 
                   LOCATE('PORT(', JOINED_COMMAND) - 5)
        ELSE NULL END 
    AS CHAR(5)) AS PORT,
    
    -- Remote Location Name
    CAST(
        CASE WHEN LOCATE('RMTLOCNAME(', JOINED_COMMAND) > 0 THEN 
            SUBSTR(JOINED_COMMAND, 
                   LOCATE('RMTLOCNAME(', JOINED_COMMAND) + 11, 
                   LOCATE(')', JOINED_COMMAND, LOCATE('RMTLOCNAME(', JOINED_COMMAND)) - 
                   LOCATE('RMTLOCNAME(', JOINED_COMMAND) - 11)
        ELSE NULL END 
    AS CHAR(100)) AS RMTLOCNAME,
    
    -- Description Text
    CAST(
        CASE WHEN LOCATE('TEXT(', JOINED_COMMAND) > 0 THEN 
            SUBSTR(JOINED_COMMAND, 
                   LOCATE('TEXT(', JOINED_COMMAND) + 5, 
                   LOCATE(')', JOINED_COMMAND, LOCATE('TEXT(', JOINED_COMMAND)) - 
                   LOCATE('TEXT(', JOINED_COMMAND) - 5)
        ELSE NULL END 
    AS CHAR(50)) AS TEXT,
    
    -- Workstation Customization
    CAST(
        CASE WHEN LOCATE('WSCST(', JOINED_COMMAND) > 0 THEN 
            SUBSTR(JOINED_COMMAND, 
                   LOCATE('WSCST(', JOINED_COMMAND) + 6, 
                   LOCATE(')', JOINED_COMMAND, LOCATE('WSCST(', JOINED_COMMAND)) - 
                   LOCATE('WSCST(', JOINED_COMMAND) - 6)
        ELSE NULL END 
    AS CHAR(20)) AS WSCST
    
FROM QTEMP.WORK_JOINED 
) WITH DATA;
-- =====================================================
-- Step 9: Query the final results
-- =====================================================
SELECT * FROM QTEMP.WORK_FIN;
-- =====================================================
-- Optional: Additional useful queries
-- =====================================================
-- Count printers by manufacturer type
-- SELECT MFRTYPMDL, COUNT(*) AS PRINTER_COUNT
-- FROM QTEMP.WORK_FIN
-- GROUP BY MFRTYPMDL
-- ORDER BY PRINTER_COUNT DESC;
-- List network printers with IP addresses
-- SELECT DEVD, RMTLOCNAME, PORT, TEXT
-- FROM QTEMP.WORK_FIN
-- WHERE RMTLOCNAME IS NOT NULL
-- ORDER BY DEVD;
-- Find printers by port number
-- SELECT DEVD, PORT, RMTLOCNAME, TEXT
-- FROM QTEMP.WORK_FIN
-- WHERE PORT IS NOT NULL
-- ORDER BY PORT;

The same code compacted for copy/paste/runAll in Access Client Solutions:

-- if cleanup is needed
CL:CLRLIB QTEMP;
CL:CRTSRCPF FILE(QTEMP/QTXTSRC);
CL:RTVCFGSRC CFGD( *ALL) CFGTYPE(*DEVD) SRCFILE(QTEMP/QTXTSRC)
SRCMBR(PRINTERS) RTVOPT(*OBJ);

create table QTEMP.work as ( SELECT SRCDTA FROM QTEMP.QTXTSRC WHERE (
srcdta like '%DEVD%' and srcdta like '%CRTDEVPRT%' or srcdta like
'%MFRTYPMDL(%' or srcdta like '%WSCST(%' or srcdta like '%HPT(*%' or
srcdta like '%TEXT(%') or srcdta like '%PORT(%' or srcdta like
'%RMTLOCNAME(%' ) with data;

CREATE TABLE QTEMP.WORK_NUMBERED AS ( SELECT RRN(QTEMP.WORK) as recno,
SRCDTA FROM QTEMP.WORK ) WITH DATA;
CREATE TABLE QTEMP.WORK_GROUPED AS ( SELECT SRCDTA, recno , SUM( CASE
WHEN TRIM(SRCDTA) LIKE 'CRTDEVPRT%' THEN 1 ELSE 0 END) OVER ( ORDER BY
recno ) AS CMDGROUP FROM QTEMP.WORK_NUMBERED ) WITH DATA;
CREATE TABLE QTEMP.WORK_JOINED AS ( SELECT CMDGROUP, LISTAGG(
TRIM(TRAILING '+' FROM TRIM(SRCDTA)), ' ' ) WITHIN GROUP (ORDER BY
recno) AS JOINED_COMMAND FROM QTEMP.WORK_GROUPED WHERE CMDGROUP > 0
GROUP BY CMDGROUP ) WITH DATA;

CREATE TABLE QTEMP.WORK_FIN AS ( SELECT CAST( CASE WHEN LOCATE('DEVD(',
JOINED_COMMAND) > 0 THEN SUBSTR( JOINED_COMMAND, LOCATE('DEVD(',
JOINED_COMMAND) + 5, LOCATE(')', JOINED_COMMAND, LOCATE('DEVD(',
JOINED_COMMAND)) - LOCATE('DEVD(', JOINED_COMMAND) - 5 ) ELSE NULL END
AS CHAR(10) ) AS DEVD , CAST( CASE WHEN LOCATE('MFRTYPMDL(',
JOINED_COMMAND) > 0 THEN SUBSTR( JOINED_COMMAND, LOCATE('MFRTYPMDL(',
JOINED_COMMAND) + 10, LOCATE(')', JOINED_COMMAND, LOCATE('MFRTYPMDL(',
JOINED_COMMAND)) - LOCATE('MFRTYPMDL(', JOINED_COMMAND) -10 ) ELSE NULL
END AS CHAR(10) ) AS MFRTYPMDL, CAST( CASE WHEN LOCATE('TRANSFORM(',
JOINED_COMMAND) > 0 THEN SUBSTR( JOINED_COMMAND, LOCATE('TRANSFORM(',
JOINED_COMMAND) + 10, LOCATE(')', JOINED_COMMAND, LOCATE('TRANSFORM(',
JOINED_COMMAND)) - LOCATE('TRANSFORM(', JOINED_COMMAND) - 10 ) ELSE NULL
END AS CHAR(10) ) AS TRANSFORM, CAST( CASE WHEN LOCATE('PORT(',
JOINED_COMMAND) > 0 THEN
SUBSTR( JOINED_COMMAND, LOCATE('PORT(', JOINED_COMMAND) + 5, LOCATE(')',
JOINED_COMMAND, LOCATE('PORT(', JOINED_COMMAND)) - LOCATE('PORT(',
JOINED_COMMAND) - 5 ) ELSE NULL END AS CHAR(5)
) AS PORT, CAST( CASE WHEN LOCATE('RMTLOCNAME(', JOINED_COMMAND) > 0
THEN SUBSTR( JOINED_COMMAND, LOCATE('RMTLOCNAME(', JOINED_COMMAND) + 11,
LOCATE(')', JOINED_COMMAND, LOCATE('RMTLOCNAME(', JOINED_COMMAND)) -
LOCATE('RMTLOCNAME(', JOINED_COMMAND) - 11 ) ELSE NULL END AS CHAR(100)
) AS RMTLOCNAME, CAST( CASE WHEN LOCATE('TEXT(', JOINED_COMMAND) > 0
THEN SUBSTR( JOINED_COMMAND, LOCATE('TEXT(', JOINED_COMMAND) + 5,
LOCATE(')', JOINED_COMMAND, LOCATE('TEXT(', JOINED_COMMAND)) -
LOCATE('TEXT(', JOINED_COMMAND) - 5 ) ELSE NULL END AS CHAR(50) ) AS
TEXT, CAST( CASE WHEN LOCATE('WSCST(', JOINED_COMMAND) > 0 THEN SUBSTR(
JOINED_COMMAND, LOCATE('WSCST(', JOINED_COMMAND) + 6, LOCATE(')',
JOINED_COMMAND, LOCATE('WSCST(', JOINED_COMMAND)) - LOCATE('WSCST(',
JOINED_COMMAND) - 6 ) ELSE NULL END AS CHAR(20) ) AS WSCST FROM
QTEMP.WORK_JOINED ) WITH DATA;
-- Sample use:
select * from qtemp.work_fin;

If you wish to create permanent WORK_FIN table, then create that final table in library other than QTEMP, leaving all other working temporary  tables in QTEMP.

Replace line

CREATE TABLE QTEMP.WORK_FIN AS ( ..

by 

CREATE TABLE QGPL.WORK_FIN AS ( ..
in above SQL code