IBM i Security

Build an IBM i Security and PTF Visibility Workflow with SQL

Use SYSTOOLS.CVE_INFO and SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL to create a controlled IBM i workflow for vulnerability awareness, PTF currency, isolated partitions, ownership, evidence, and remediation tracking.

Saurabh Arora

IBM i Solution Architect | Technical Lead | Modernization & Integration Specialist

Published July 26, 20268 min read

Security teams need to know which IBM i vulnerabilities have been published. IBM i administrators need to know whether the partition’s Group PTF levels are current. These are connected questions—but they are not the same question, and neither should be reduced to a single red or green dashboard indicator.

Two SQL services provide useful building blocks:

SYSTOOLS.CVE_INFO
SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL

CVE_INFO retrieves IBM security bulletins that identify a requested IBM i release as an affected product.

GROUP_PTF_CURRENCY_LOCAL compares Group PTFs installed on the current partition with a controlled local copy of IBM’s Preventive Service Planning XML feed.

Together, they can support a practical workflow for:

vulnerability awareness
maintenance review
isolated partitions
security ownership
evidence collection
remediation planning
exception tracking
audit reporting

They do not automatically determine whether the current partition is vulnerable or whether one particular Group PTF resolves one particular CVE.

Two different questions

The first question is:

What IBM security bulletins currently identify this IBM i release as affected?

Use:

SYSTOOLS.CVE_INFO

The second question is:

Which installed Group PTF levels are behind the levels currently listed by IBM?

Use:

SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL

A mature workflow keeps the two signals separate until a person or approved process reviews the supporting IBM documentation.

What CVE_INFO provides

A basic query is:

SELECT *
FROM TABLE(
    SYSTOOLS.CVE_INFO()
);

By default, the function uses the IBM i release of the partition where the query runs.

A specific release can be requested:

SELECT *
FROM TABLE(
    SYSTOOLS.CVE_INFO('7.5')
);

Useful result columns include:

CVE_ID
SCORE
PUBLISH_DATE
TITLE
IBM_SUPPORT_URL
SUMMARY
DESCRIPTION
PRODUCT_ID
PRODUCT_NAME
IBMI_RELEASE
MODIFICATION_DATE
X_FORCE_URL
AFFECTED_PRODUCTS

The score is returned as a category:

Critical
High
Medium
Low

The function retrieves current security bulletin information from IBM Support.

It requires:

outbound connectivity to the IBM Support service used by the function
a job CCSID other than 65535
authority required by the underlying SYSTOOLS implementation

When the partition cannot reach the website, the function returns no rows.

No rows does not necessarily mean no CVEs.

It can also mean the partition could not connect to IBM Support. A production workflow must distinguish an empty security result from a failed or unavailable data source.

What CVE_INFO does not prove

A row returned by CVE_INFO means the IBM bulletin identifies the requested IBM i release as an affected product.

It does not automatically prove that:

the vulnerable licensed program is installed
the affected component is enabled
the vulnerable function is exposed
the relevant configuration exists
the partition has not already applied the corrective PTF
a mitigating control is absent
the vulnerability is exploitable in the local environment

The result should initiate triage—not complete it.

Query recent bulletins

SELECT
    CVE_ID,
    SCORE,
    PUBLISH_DATE,
    MODIFICATION_DATE,
    TITLE,
    IBM_SUPPORT_URL
FROM TABLE(
    SYSTOOLS.CVE_INFO()
)
WHERE PUBLISH_DATE
      >= CURRENT DATE - 30 DAYS
ORDER BY
    PUBLISH_DATE DESC,
    CVE_ID;

This is useful for a scheduled review, but it should not be the only query.

A bulletin can be modified after publication.

Find recently modified bulletins

SELECT
    CVE_ID,
    SCORE,
    PUBLISH_DATE,
    MODIFICATION_DATE,
    TITLE,
    IBM_SUPPORT_URL
FROM TABLE(
    SYSTOOLS.CVE_INFO()
)
WHERE MODIFICATION_DATE
      >= CURRENT DATE - 14 DAYS
ORDER BY
    MODIFICATION_DATE DESC,
    CVE_ID;

Modification tracking matters because IBM can update:

affected-product information
remediation guidance
severity
required PTFs
mitigation information
bulletin descriptions

A workflow that looks only at the original publication date can miss meaningful changes.

Prioritize by severity without discarding context

SELECT
    CVE_ID,
    SCORE,
    PUBLISH_DATE,
    MODIFICATION_DATE,
    TITLE,
    IBM_SUPPORT_URL
FROM TABLE(
    SYSTOOLS.CVE_INFO()
)
ORDER BY
    CASE SCORE
        WHEN 'Critical' THEN 1
        WHEN 'High'     THEN 2
        WHEN 'Medium'   THEN 3
        WHEN 'Low'      THEN 4
        ELSE 5
    END,
    PUBLISH_DATE DESC;

Severity is one triage input.

Local priority can also depend on:

internet exposure
network segmentation
business criticality
installed products
compensating controls
data classification
exploit availability
maintenance-window constraints
recovery readiness

A lower-scored bulletin affecting an exposed critical service may deserve faster action than a higher-scored bulletin affecting an unused component.

Preserve a controlled snapshot

Live results are useful, but security and audit workflows often require evidence of what was known at a specific time.

Create a snapshot table:

CREATE TABLE SECURITY.CVE_SNAPSHOT
(
    SNAPSHOT_TIMESTAMP  TIMESTAMP NOT NULL,
    SYSTEM_NAME         VARCHAR(8) NOT NULL,
    IBMI_RELEASE        CHAR(3),
    CVE_ID              VARCHAR(20),
    SCORE               VARCHAR(20),
    PUBLISH_DATE        DATE,
    MODIFICATION_DATE   DATE,
    TITLE               VARCHAR(2000),
    IBM_SUPPORT_URL     VARCHAR(200),
    SUMMARY             VARCHAR(2000)
);

Load it:

INSERT INTO SECURITY.CVE_SNAPSHOT
(
    SNAPSHOT_TIMESTAMP,
    SYSTEM_NAME,
    IBMI_RELEASE,
    CVE_ID,
    SCORE,
    PUBLISH_DATE,
    MODIFICATION_DATE,
    TITLE,
    IBM_SUPPORT_URL,
    SUMMARY
)
SELECT
    CURRENT TIMESTAMP,
    SYSTEM_NAME,
    IBMI_RELEASE,
    CVE_ID,
    SCORE,
    PUBLISH_DATE,
    MODIFICATION_DATE,
    TITLE,
    IBM_SUPPORT_URL,
    SUMMARY
FROM TABLE(
    SYSTOOLS.CVE_INFO()
)
CROSS JOIN
(
    SELECT SYSTEM_NAME
    FROM QSYS2.SYSTEM_STATUS_INFO_BASIC
) AS SYS;

Adjust the system-identification query to match the local schema and installed service level.

A snapshot supports:

change detection
audit evidence
historical reporting
bulletin modification review
ownership tracking
downstream integration

Do not copy large HTML columns unless they are required.

Detect newly observed CVEs

A controlled process can compare the latest snapshot with the previous one.

Conceptual query:

WITH SNAPSHOT_TIMES AS
(
    SELECT DISTINCT
        SNAPSHOT_TIMESTAMP
    FROM SECURITY.CVE_SNAPSHOT
),
RANKED_TIMES AS
(
    SELECT
        SNAPSHOT_TIMESTAMP,
        ROW_NUMBER() OVER(
            ORDER BY SNAPSHOT_TIMESTAMP DESC
        ) AS SNAPSHOT_SEQUENCE
    FROM SNAPSHOT_TIMES
)
SELECT
    CURR.CVE_ID,
    CURR.SCORE,
    CURR.PUBLISH_DATE,
    CURR.TITLE,
    CURR.IBM_SUPPORT_URL
FROM SECURITY.CVE_SNAPSHOT AS CURR
JOIN RANKED_TIMES AS CT
  ON CT.SNAPSHOT_TIMESTAMP =
     CURR.SNAPSHOT_TIMESTAMP
 AND CT.SNAPSHOT_SEQUENCE = 1
WHERE NOT EXISTS
(
    SELECT 1
    FROM SECURITY.CVE_SNAPSHOT AS PREV
    JOIN RANKED_TIMES AS PT
      ON PT.SNAPSHOT_TIMESTAMP =
         PREV.SNAPSHOT_TIMESTAMP
     AND PT.SNAPSHOT_SEQUENCE = 2
    WHERE PREV.CVE_ID = CURR.CVE_ID
);

A production design should also identify bulletins whose modification date, severity, title, or affected-product details changed.

Internet-connected versus isolated partitions

CVE_INFO requires connectivity from the IBM i partition running the query.

An isolated production partition may intentionally have no direct outbound internet access.

Do not weaken network controls solely to make the function work.

Possible controlled patterns include:

Run CVE_INFO on an approved connected IBM i management partition.
Store the results in a controlled enterprise security repository.
Transfer an approved snapshot to isolated environments.
Use an external security process to retrieve and review IBM bulletins.

Those are custom operational patterns—not automatic behavior provided by CVE_INFO.

The source, retrieval time, transfer controls, integrity checks, and ownership should be documented.

Why Group PTF currency matters

A vulnerability bulletin may identify a corrective PTF or product update.

Administrators also need a broader maintenance view:

Is the HIPER Group current?
Is the Security Group current?
Is the Db2 Group current?
Is the HTTP Server Group current?
Is the Java Group current?
Are newer Group PTF levels available?
Are current levels already staged for the next IPL?

Group PTF currency helps identify maintenance gaps before they become emergency changes.

Use a local PSP feed

For a partition that cannot connect directly to IBM’s Preventive Service Planning feed, download the XML feed through an approved connected device or system.

IBM’s documented feed is:

https://public.dhe.ibm.com/services/us/igsc/PSP/xmldoc.xml

Transfer the file to a protected IFS path, for example:

/security/psp/xmldoc.xml

Then query:

SELECT *
FROM TABLE(
    SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL(
        '/security/psp/xmldoc.xml'
    )
)
ORDER BY
    PTF_GROUP_LEVEL_AVAILABLE
    - PTF_GROUP_LEVEL_INSTALLED DESC;

The function compares the local XML information with Group PTF detail on the current partition.

Enforce feed freshness

The function accepts a second argument that specifies the oldest acceptable modification timestamp for the IFS file.

Example:

SELECT *
FROM TABLE(
    SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL(
        '/security/psp/xmldoc.xml',
        CURRENT TIMESTAMP - 3 DAYS
    )
);

If the file is older than the accepted timestamp, the function issues an error and returns no results.

The default freshness expectation is:

Current date minus seven days

This is important because an old XML file can make an outdated maintenance comparison appear current.

Important Group PTF result values

PTF_GROUP_CURRENCY can contain:

INSTALLED LEVEL IS CURRENT
CURRENT AT THE NEXT IPL
UPDATE AVAILABLE
PSP INFORMATION NOT AVAILABLE

Other useful columns include:

PTF_GROUP_ID
PTF_GROUP_TITLE
PTF_GROUP_LEVEL_INSTALLED
PTF_GROUP_LEVEL_AVAILABLE
LAST_UPDATED_BY_IBM
PTF_GROUP_RELEASE
PTF_GROUP_STATUS_ON_SYSTEM
PTF_GROUP_APPLY_TIMESTAMP

These values answer different questions.

For example:

UPDATE AVAILABLE

means IBM lists a newer Group PTF level.

It does not mean every PTF in that newer level is urgent.

CURRENT AT THE NEXT IPL

means the current available level is ready to become active at the next IPL.

It does not mean the function is already active.

Show only maintenance gaps

SELECT
    PTF_GROUP_ID,
    PTF_GROUP_TITLE,
    PTF_GROUP_LEVEL_INSTALLED,
    PTF_GROUP_LEVEL_AVAILABLE,
    PTF_GROUP_CURRENCY,
    PTF_GROUP_STATUS_ON_SYSTEM,
    PTF_GROUP_APPLY_TIMESTAMP,
    LAST_UPDATED_BY_IBM
FROM TABLE(
    SYSTOOLS.GROUP_PTF_CURRENCY_LOCAL(
        '/security/psp/xmldoc.xml',
        CURRENT TIMESTAMP - 3 DAYS
    )
)
WHERE PTF_GROUP_CURRENCY
      <> 'INSTALLED LEVEL IS CURRENT'
ORDER BY
    PTF_GROUP_LEVEL_AVAILABLE
    - PTF_GROUP_LEVEL_INSTALLED DESC,
    PTF_GROUP_ID;

Review CURRENT AT THE NEXT IPL separately from groups that have not yet been loaded.

Snapshot Group PTF currency

CREATE TABLE SECURITY.PTF_CURRENCY_SNAPSHOT
(
    SNAPSHOT_TIMESTAMP          TIMESTAMP NOT NULL,
    SYSTEM_NAME                 VARCHAR(8) NOT NULL,
    PTF_GROUP_ID                CHAR(7),
    PTF_GROUP_TITLE             VARCHAR(1000),
    PTF_GROUP_LEVEL_INSTALLED   INTEGER,
    PTF_GROUP_LEVEL_AVAILABLE   INTEGER,
    PTF_GROUP_CURRENCY          VARCHAR(46),
    PTF_GROUP_STATUS_ON_SYSTEM  VARCHAR(20),
    PTF_GROUP_APPLY_TIMESTAMP   TIMESTAMP(0),
    LAST_UPDATED_BY_IBM         DATE,
    PSP_FILE_PATH               VARCHAR(1024),
    PSP_FILE_TIMESTAMP          TIMESTAMP
);

Store:

the system identity
query timestamp
local XML path
XML modification timestamp
installed level
available level
apply state

This creates evidence of both the system state and the data source used for comparison.

Do not join CVEs to Group PTFs by assumption

A tempting design is:

Critical CVE + Security Group behind = CVE unresolved

That conclusion is not reliable.

Reasons include:

the bulletin may apply to a licensed program outside the Security Group
the corrective fix may be an individual PTF
the fix may be delivered through Db2, HTTP, Java, BRMS, Open Source, or another group
the relevant product may not be installed
the corrective PTF may already be applied even when another Group PTF is behind
the Group PTF may be current while a separately delivered product update is missing
the bulletin may describe configuration mitigations in addition to fixes

Use the IBM support URL returned by CVE_INFO to identify the actual remediation.

Build a triage table

CREATE TABLE SECURITY.CVE_TRIAGE
(
    SYSTEM_NAME          VARCHAR(8) NOT NULL,
    CVE_ID               VARCHAR(20) NOT NULL,
    DETECTED_TIMESTAMP   TIMESTAMP NOT NULL,
    SCORE                VARCHAR(20),
    STATUS               VARCHAR(30) NOT NULL,
    OWNER                VARCHAR(128),
    AFFECTED_COMPONENT   VARCHAR(256),
    COMPONENT_INSTALLED  CHAR(1),
    COMPONENT_ENABLED    CHAR(1),
    LOCAL_EXPOSURE       VARCHAR(30),
    IBM_REMEDIATION      VARCHAR(2048),
    REQUIRED_FIX         VARCHAR(256),
    FIX_STATUS           VARCHAR(30),
    MITIGATION           VARCHAR(2048),
    TARGET_DATE          DATE,
    EXCEPTION_REFERENCE  VARCHAR(128),
    EVIDENCE_LOCATION    VARCHAR(1024),
    LAST_REVIEWED        TIMESTAMP,
    PRIMARY KEY
    (
        SYSTEM_NAME,
        CVE_ID
    )
);

Possible statuses:

NEW
UNDER REVIEW
NOT APPLICABLE
MITIGATED
FIX PLANNED
FIX STAGED
REQUIRES IPL
REMEDIATED
RISK ACCEPTED

Avoid a single Boolean vulnerable flag when the assessment has not been completed.

Create a maintenance action table

CREATE TABLE SECURITY.PTF_ACTION
(
    SYSTEM_NAME          VARCHAR(8) NOT NULL,
    PTF_GROUP_ID         CHAR(7) NOT NULL,
    INSTALLED_LEVEL      INTEGER,
    AVAILABLE_LEVEL      INTEGER,
    ACTION_STATUS        VARCHAR(30) NOT NULL,
    OWNER                VARCHAR(128),
    CHANGE_REFERENCE     VARCHAR(128),
    PLANNED_LOAD_DATE    DATE,
    PLANNED_APPLY_DATE   DATE,
    IPL_REQUIRED         CHAR(1),
    VALIDATION_PLAN      VARCHAR(2048),
    ROLLBACK_PLAN        VARCHAR(2048),
    LAST_REVIEWED        TIMESTAMP,
    PRIMARY KEY
    (
        SYSTEM_NAME,
        PTF_GROUP_ID
    )
);

The CVE triage table tracks security-bulletin assessment.

The PTF action table tracks maintenance execution.

Link them only when the IBM bulletin or approved technical analysis identifies the relationship.

Operational workflow

A practical workflow is:

1. Retrieve the current IBM CVE bulletin list.
2. Verify that retrieval succeeded.
3. Snapshot the results.
4. Identify new and modified bulletins.
5. Assign a security or platform owner.
6. Read the IBM support bulletin.
7. Determine whether the affected product is installed.
8. Determine whether the affected function is used or exposed.
9. Identify the exact IBM remediation or mitigation.
10. Compare installed PTF and product levels.
11. Create a change or exception record.
12. Load and test the required maintenance.
13. Apply it through the approved maintenance process.
14. Validate the affected function.
15. preserve evidence and close the triage record.

PTF currency should run on its own schedule:

1. Obtain the current PSP XML feed through an approved channel.
2. Verify source and integrity.
3. transfer it to the protected IFS path.
4. Enforce a freshness threshold.
5. Run GROUP_PTF_CURRENCY_LOCAL.
6. Snapshot the result.
7. Separate current, staged, and update-available groups.
8. Assign maintenance ownership.
9. Plan load, apply, IPL, and validation activity.

Add failure-state monitoring

A security dashboard should distinguish:

CVE query succeeded and returned rows
CVE query succeeded and returned zero rows
CVE source unavailable
PTF XML feed current
PTF XML feed stale
PTF comparison succeeded
PTF comparison failed

Without source-health indicators, an empty panel can create false confidence.

A simple control table can capture execution state:

CREATE TABLE SECURITY.FEED_RUN
(
    RUN_ID              BIGINT
                        GENERATED ALWAYS AS IDENTITY,
    FEED_NAME           VARCHAR(50) NOT NULL,
    SYSTEM_NAME         VARCHAR(8) NOT NULL,
    START_TIMESTAMP     TIMESTAMP NOT NULL,
    END_TIMESTAMP       TIMESTAMP,
    STATUS              VARCHAR(20) NOT NULL,
    ROW_COUNT           INTEGER,
    SOURCE_TIMESTAMP    TIMESTAMP,
    ERROR_SQLCODE       INTEGER,
    ERROR_SQLSTATE      CHAR(5),
    ERROR_MESSAGE       VARCHAR(2048)
);

Alert on actionable change

Avoid alerting every day for the same unchanged condition.

Good alert triggers include:

a newly observed CVE
a bulletin severity increase
a modified bulletin requiring new action
a newly available Group PTF level
a Group PTF remaining staged without an IPL beyond the approved window
a stale PSP XML feed
repeated failure to retrieve security data
an overdue CVE triage record
an overdue maintenance action
an exception approaching expiration

Alerts should identify:

What changed
Which system is affected
Who owns the review
The supporting IBM link
The required response time

Design for multiple partitions

A central repository can combine snapshots from:

DEV
TEST
UAT
PROD
DR
HA nodes
Management partitions

Use a stable system identity.

Do not rely only on a friendly environment name that can be duplicated or changed.

Include values such as:

system name
partition identifier
serial number where appropriate
IBM i release
environment
business owner
technical owner
data classification
internet-connectivity classification

This supports risk-based prioritization.

Keep evidence for audits

Useful evidence includes:

CVE snapshot timestamp
IBM bulletin URL
affected-product assessment
installed-product evidence
exact corrective PTF or update
Group PTF snapshot
change record
test results
IPL evidence
post-maintenance validation
approved exception
closure approval

A screenshot alone is weak evidence because it can omit query parameters, source timestamps, or system identity.

Structured snapshots are easier to review and reproduce.

Authority and data protection

Both services are delivered in SYSTOOLS as examples whose authority requirements depend on the interfaces used in their implementation.

Review the extracted SQL source before production adoption.

Also protect:

snapshot tables
bulletin descriptions
system inventory
maintenance status
exception records
IFS XML feeds
automated report output

Security-status information can itself be sensitive because it reveals maintenance gaps and system details.

Job CCSID requirement

Both documented services require a job CCSID other than:

A scheduled job using CCSID 65535 can fail even when the same query works interactively.

The scheduler design should explicitly control:

execution profile
job description
job CCSID
library list
current library
ASP group
network route
IFS authority
result repository

Connected workflow example

A connected IBM i management partition can:

Run CVE_INFO daily
Snapshot new and modified bulletins
Download the PSP XML feed
Distribute the approved XML file internally
Collect Group PTF snapshots from managed partitions
Create security and maintenance work items

An isolated production partition can:

Receive the approved PSP XML file
Run GROUP_PTF_CURRENCY_LOCAL
Publish its PTF snapshot to the controlled repository
Receive reviewed CVE assignments through the existing security process

This maintains isolation while still improving visibility.

What not to automate blindly

Do not automatically:

mark every CVE as locally exploitable
install every available PTF immediately
approve an IPL without application coordination
connect isolated production directly to the internet
close a CVE solely because one Group PTF is current
interpret an empty CVE result as proof of no exposure
grant broad authority so a report can run
expose raw security status to all users
create endless duplicate tickets for unchanged findings

Automation should improve evidence and workflow discipline.

It should not replace technical assessment.

Detailed SQL references

For focused implementation examples, see:

Final takeaway

CVE_INFO answers:

What IBM security bulletins identify this IBM i release as affected?

GROUP_PTF_CURRENCY_LOCAL answers:

How do installed Group PTF levels compare with an approved local copy of IBM’s current PSP information?

Neither function independently answers:

Is this partition vulnerable, and has the exact corrective action been completed?

That answer requires a controlled workflow combining:

Current IBM information
Reliable source-health checks
Local product and configuration assessment
Exact remediation mapping
Maintenance planning
Validation
Ownership
Evidence

The real security improvement is not another dashboard.

It is a repeatable process that converts IBM information into verified action.

Comments

Share your thoughts, questions, or real-world IBM i experiences related to this article.