Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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;
@forstie
Copy link
Author

forstie commented Dec 15, 2021

You could also try a directed query as a 2nd data point.

select current timestamp as timeOfSearch, a.*
from table (
qsys2.ifs_object_statistics(
start_path_name => '/dirname',
subtree_directories => 'NO',
object_type_list => '*ALLDIR *ALLSTMF *NOQSYS',
ignore_errors => 'NO'
)) a
where upper(path_name) like '%LOG4J%'

@forstie
Copy link
Author

forstie commented Dec 15, 2021

Do you have option 26 (DB2 Symmetric Multiprocessing)?
If yes, what setting is in place when you execute the query?

@alphusr
Copy link

alphusr commented Dec 15, 2021

i realized this needs IBM i 7.3.

@forstie
Copy link
Author

forstie commented Dec 15, 2021

Correct, this technique requires IBM i 7.3 or higher.
For older versions of IBM i, there's a technique that could work on 7.2 found here.
https://www.ibm.com/support/pages/retrieving-information-about-objects-ifs

Also, I published two RTVDIRINF based example in Insert from Examples in ACS's Run SQL Scripts.
Enter RTVDIRINF in the search bar.

@JEDOLL
Copy link

JEDOLL commented Dec 15, 2021

In Scott's 7.3 example:
For those who don't know this already: if your "PATH_NAME" returns "*POINTER" like mine did, you can CAST that similarly to this:
...cast(path_name as char(100) ccsid 37 ) as PATH_NAME...
either in the INSERT INTO for the file, in which case you might have to adjust Scott's table definition, or in the query of the RESULTS table.

@mgarczynski
Copy link

mgarczynski commented Dec 15, 2021

So I have the condition where Path_Name is *POINTER. I tried to add the cast(path_name as char(100) ccsid 37 ) as PATH_NAME and getting an error. I tried to use it in the RESULTS table query a few ways and keep getting Keyword CAST not expected. Could you provide an updated statement. Thanks

@forstie
Copy link
Author

forstie commented Dec 15, 2021

For those seeing *POINTER, can you shift from STRSQL to ACS's Run SQL Scripts?
Casting to a character type is possible, but tricky. For example, using CHAR(100) means that if the path_name is longer than 100, you won't see the full detail.
Also, its not uncommon that 5250 sessions are using job CCSID 65535, which can result in character conversion failures (SQL0332).

Run SQL Scripts has none of these issues.

@mgarczynski
Copy link

mgarczynski commented Dec 15, 2021

@forstie
Copy link
Author

forstie commented Dec 15, 2021

@mgarczynski
Copy link

mgarczynski commented Dec 15, 2021

@GlennGundermann
Copy link

GlennGundermann commented Dec 16, 2021

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

GlennGundermann commented Dec 16, 2021

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

bobcozzi commented Dec 18, 2021

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

thebeardedgeek commented Dec 18, 2021

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