Skip to content

Instantly share code, notes, and snippets.

  • Star 5 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save forstie/9662d4c302f5224c66b7a4c409141a2c to your computer and use it in GitHub Desktop.
The request from a client was to provide an SQL approach to search all of the IFS, finding any object that has "log4j" in its name, and producing an SQL table with the search results.
--
-- Subject: Find objects that have the string "log4j" in their name (on IBM i 7.1 or IBM i 7.2)
-- Author: Scott Forstie
-- Date : December 17, 2021
-- What to do on IBM i releases when ifs_object_statistics, and other SQL services do not exist
--
--
stop;
--
--
-- Note 1, if you have iASPs, you would need to repeat the search for each iASP, specifying the iASP namespace on the SBMJOB
-- So, instead of searching from the root ('/'), you would initiate the search from '/IASPNAME' and have INLASPGRP(IASPNAME)
--
-- Note 2, this search avoids QSYS (library based) objects. If you have a requirement to include those objects in the search, remove the OMIT parameter.
--
-- Note 3, this search is intended to be initiated by someone who has *ALLOBJ special authority.
--
-- Note 4, this search will likely be long running, which is why is it submitted to QBATCH.
-- As the search will touch all objects in the IFS, it is recommended that the search be done outside of a performance critical window.
--
-- Establish a library to hold the search results
--
cl: crtlib IFSSEARCH;
cl:GRTOBJAUT OBJ(IFSSEARCH) OBJTYPE(*LIB) USER(*PUBLIC) AUT(*EXCLUDE);
--
-- If you've run this before, you have to delete this work files before running them again
--
drop table IFSSEARCH.IFSINFO2O;
drop table IFSSEARCH.IFSINFO2D;
--
-- Submit a job to batch that does the searching
--
cl: SBMJOB CMD(RTVDIRINF DIR('/') INFFILEPFX(IFSINFO2) INFLIB(IFSSEARCH) OMIT('/QSYS')) JOB(IFSSEARCH) INLASPGRP(*CURRENT) JOBMSGQFL(*PRTWRAP) LOG(4 00 *SECLVL);
--
-- Use WRKACTJOB SBS(QBATCH) to follow the job execution... wait for it to complete
--
stop;
--
-- Establish a table to hold the search results
--
drop table ifssearch.results;
create table ifssearch.results as
(select QEZDIRNAM1 as IFS_DIRECTORY,
QEZOBJNAM as IFS_OBJECT_NAME,
QEZOBJTYPE as IFS_OBJECT_TYPE,
qezown as owner,
o.*
from IFSSEARCH.IFSINFO2O O
inner join IFSSEARCH.IFSINFO2D D
on O.QEZDIRIDX = D.QEZDIRIDX
where upper(QEZOBJNAM) like '%LOG4J%')
with data;
cl:GRTOBJAUT OBJ(IFSSEARCH/RESULTS) OBJTYPE(*FILE) USER(*PUBLIC) AUT(*EXCLUDE);
drop table IFSSEARCH.IFSINFO2O; -- delete work file
drop table IFSSEARCH.IFSINFO2D; -- delete work file
--
-- View the results
--
select *
from ifssearch.results;
--
-- Subject: Find objects that have the string "log4j" in their name
-- Author: Scott Forstie
-- Date : December 13, 2021
-- Features Used : This Gist uses ifs_object_statistics, job_info, RUNSQL, and SBMJOB
--
--
stop;
--
--
-- Note 1, if you have iASPs, you would need to repeat the search for each iASP, specifying the iASP namespace on the SBMJOB
-- So, instead of searching from the root ('/'), you would initiate the search from '/IASPNAME' and have INLASPGRP(IASPNAME)
--
-- Note 2, this search avoid QSYS (library based) objects. If you have a requirement to include those objects in the search, remove *NOQSYS.
--
-- Note 3, this search is intended to be initiated by someone who has *ALLOBJ special authority.
--
-- Note 4, this search will likely be long running, which is why is it submitted to QBATCH.
-- As the search will touch all objects in the IFS, it is recommended that the search be done outside of a performance critical window.
--
-- Establish a library to hold the search results
--
cl: crtlib IFSSEARCH;
cl:GRTOBJAUT OBJ(IFSSEARCH) OBJTYPE(*LIB) USER(*PUBLIC) AUT(*EXCLUDE);
--
-- Establish a table to hold the search results
--
create table ifssearch.results as (
select current timestamp as timeOfSearch, a.*
from table (
qsys2.ifs_object_statistics(
start_path_name => '/',
subtree_directories => 'YES',
object_type_list => '*ALLDIR *ALLSTMF *NOQSYS',
ignore_errors => 'YES'
)) a
where upper(path_name) like '%LOG4J%'
) with no data;
cl:GRTOBJAUT OBJ(IFSSEARCH/RESULTS) OBJTYPE(*FILE) USER(*PUBLIC) AUT(*EXCLUDE);
--
-- Submit a job to batch that does the searching
--
cl: SBMJOB CMD(RUNSQL SQL('insert into ifssearch.results
select current timestamp as timeOfSearch, a.*
from table (
qsys2.ifs_object_statistics(
start_path_name => ''/'',
subtree_directories => ''YES'',
object_type_list => ''*ALLDIR *ALLSTMF *NOQSYS'',
ignore_errors => ''YES''
)) a
where upper(path_name) like ''%LOG4J%'' ')
commit(*NONE)) JOB(IFSSEARCH) INLASPGRP(*CURRENT) JOBMSGQFL(*PRTWRAP) LOG(4 00 *SECLVL);
stop;
--
-- If you see rows returned by this query, the batch job has not yet completed
--
select *
from table (
qsys2.job_info(job_user_filter => '*ALL', job_submitter_filter => '*JOB')
);
--
-- Finally, review the results of the search
--
select * from ifssearch.results;
@GlennGundermann
Copy link

Most of my 43 findings show log4j ver 1, which if I understand correctly, is not vulnerable.
I have two entries for the XANALYSIS product, currently owned by Fresche, using ver 2, which is a concern.
I'd be curious to hear from others what they are finding.

@forstie
Copy link
Author

forstie commented Dec 17, 2021

Hi Glenn,
For CL commands, if there is an unprintable character in the string, the job log will show the literal as hex.
In the output above, there is more than one X'25' character shown, which is a line feed.
Best, Scott

@bobcozzi
Copy link

Note that SQL Tools function IFS_LIST() can be installed and run on V7R2 and later and has a free trial period automatically, so you can install and use it for this purpose on V7R2. Here's the link to the documentation for the IFS_LIST Table Function. It is substantially similar syntax to the IFS_OBJECT_STATS function Scott showed.

@thebeardedgeek
Copy link

Scott,
This worked great for me. For systems that did not have IFS_OBJECT_STATISTICS I wrote my own "utility" for:
https://gist.github.com/thebeardedgeek/284e627d2d67ecb917fda48ac7056aaf

@forstie
Copy link
Author

forstie commented Dec 18, 2021

Nice going Matt.
I had several clients ask me for a technique that could be used on IBM i 7.2 and IBM i 7.1.
They could use your solution, or here is my alternate solution. (code shared above)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment