List unused virtual devices

This was a first: I was asked by the auditors for a list of virtual devices that had not been used in the past six months. In all my years of dealing with auditors this was the first time I had been asked this question.

It is not a big deal to get this information. All virtual devices are just device descriptions, object type '*DEVD', with the object attribute of 'DSPVRT'. As they are objects I can get this information from the SQL Table function OBJECT_STATISTICS.

What columns of data am I interested in?

• Object's attribute
• Object's name
• The date the object was created
• The date the object was last used
• Object's description

My SQL statement to retrieve the data the auditors are interested in would be:

01 SELECT OBJATTRIBUTE AS "Attrib", 02 OBJNAME, 03 DATE(OBJCREATED) AS "Created", 04 DATE(LAST_USED_TIMESTAMP) AS "Last used", 05 OBJTEXT 06 FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 07 WHERE OBJATTRIBUTE = 'DSPVRT' 08 AND DATE(LAST_USED_TIMESTAMP) < CURRENT_DATE - 6 MONTHS

Line 1: I feel the name of the column is too long for displaying, therefore, I am giving it a short column name.

Line 2: The object name column.

Line 3: The request was for dates, not timestamps, therefore, I am using the Date scalar function to return the date part of the timestamp. I am also giving this column a new name.

Line 4: Ditto.

Line 5: Object's text.

Line 6: I am using the OBJECT_STATISTICS Table function, and passing to it that I want to look in the library QSYS for all device descriptions.

Line 7: I am only interested in virtual devices being in my results.

Line 8: And only those where the last used date of the object is older than six months ago.

The results are:

Attrib OBJNAME Created Last used OBJTEXT ------ ---------- ---------- ---------- -------------------------- DSPVRT QPADEV0291 2022-08-05 2023-05-16 Device created for DEV750. DSPVRT QPADEV0292 2022-08-05 2023-05-16 Device created for DEV750. DSPVRT QPADEV0293 2022-08-05 2023-05-16 Device created for DEV750. DSPVRT QPADEV0294 2022-08-05 2023-05-16 Device created for DEV750. DSPVRT QPADEV0295 2022-08-05 2023-05-16 Device created for DEV750.

What about any virtual devices that were created over six months ago and have never been used?

The statement below gives me that information.

01 SELECT OBJATTRIBUTE AS "Attrib", 02 OBJNAME, 03 DATE(OBJCREATED) AS "Created", 04 DATE(LAST_USED_TIMESTAMP) AS "Last used", 05 OBJTEXT 06 FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 07 WHERE OBJATTRIBUTE = 'DSPVRT' 08 AND DATE(OBJCREATED) < CURRENT_DATE - 6 MONTHS 09 AND LAST_USED_TIMESTAMP IS NULL

Lines 1 – 7: These are the same as the lines in my previous statement.

Line 8: I need to check if the virtual device was created more than six months ago.

Line 9: If the virtual device has never been used then the last used timestamp is null.

There is only one result from this statement:

Attrib OBJNAME Created Last used OBJTEXT ------ ---------- ---------- ---------- -------------------------- DSPVRT QPADEV0300 2022-08-05 <NULL> Device created for DEV750.

The auditors wanted this information in an Microsoft Excel spreadsheet. I decided to create an output file from the results, so I would be able to use the data if they wanted any further actions.

I need to combine the results from the two statements, which I can do using a Union clause.

01 CREATE OR REPLACE TABLE MYLIB.AUDIT007 02 (DEVICE,CREATED,LAST_USED,OBJTEXT) 03 AS 04 (SELECT OBJNAME,DATE(OBJCREATED),DATE(LAST_USED_TIMESTAMP),OBJTEXT 05 FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 06 WHERE OBJATTRIBUTE = 'DSPVRT' 07 AND LAST_USED_TIMESTAMP < CURRENT_TIMESTAMP - 6 MONTHS 08 UNION 09 SELECT OBJNAME,DATE(OBJCREATED),DATE(LAST_USED_TIMESTAMP),OBJTEXT 10 FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 11 WHERE OBJATTRIBUTE = 'DSPVRT' 12 AND OBJCREATED < CURRENT_TIMESTAMP - 6 MONTHS 13 AND LAST_USED_TIMESTAMP IS NULL) 14 WITH DATA ON REPLACE DELETE ROWS ;

Line 1: I have used the Create or Replace Table which will replace a file with the same name if it is present in my library.

Line 2: I am using a columns list so the names of the columns are what I want them to be.

Line 4 – 7: This is the same as the first SQL statement I showed above.

Line 8: The Union clause to join the two sets of results.

Lines 9 – 13: This is the same as the second SQL statement from earlier.

Line 10: The With data means that my Table will contain data. The On replace delete rows tells the SQL engine what to do with any existing data if the Table is being replaced.

After executing the above, I can then look my results with the following statement:

01 SELECT * FROM MYFILE.AUDIT007 02 ORDER BY 3,1

Line 2: I want to order the results by the Last used date and then the Device name.

The results are:

DEVICE CREATED LAST_USED OBJTEXT ---------- ---------- ---------- -------------------------- QPADEV0291 2022-08-05 2023-05-16 Device created for DEV750. QPADEV0292 2022-08-05 2023-05-16 Device created for DEV750. QPADEV0293 2022-08-05 2023-05-16 Device created for DEV750. QPADEV0294 2022-08-05 2023-05-16 Device created for DEV750. QPADEV0295 2022-08-05 2023-05-16 Device created for DEV750. QPADEV0300 2022-08-05 <NULL> Device created for DEV750.

Now I have my results I can either save the results from my ACS's Run SQL Scripts, RSS, or use the Generate Spreadsheet Scalar function or the CPYTOIMPF command to generate a file in the IFS that I can then email to myself.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.