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

Hi Scott,

I'm curious why for me, the joblog spooled file doesn't show the SQL command in character but instead shows it in hex. Please see below.

Thank you. Glenn :-)

Message . . . . : -RUNSQL
SQL(X'8995A28599A3408995A396408986A2A285819983884B9985A2A493A3A2402540A28593
8583A34083A499998595A340A3899485A2A38194974081A240A3899485D686E285819983886B
40814B5C2540408699969440A381829385404D2540404040404098A2A8A2F24B8986A26D9682
918583A36DA2A381A389A2A38983A24D254040404040404040A2A38199A36D9781A3886D9581
9485407E6E407D617D6B254040404040404040A2A482A39985856D8489998583A396998985A2
407E6E407DE8C5E27D6B2540404040404040409682918583A36DA3A897856D9389A2A3407E6E
407D5CC1D3D3C4C9D9405CC1D3D3E2E3D4C6405CD5D6D8E2E8E27D6B25404040404040404089
87959699856D8599999699A2407E6E407DE8C5E27D2540404040404040405D5D408125404040
4040404040A68885998540A4979785994D9781A3886D958194855D4093899285407D6CD3D6C7
F4D16C7D40') COMMIT(*NONE)

@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