Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Bruce Hewson]

Kolusu,

Thank you  very much for your JCL.

I have tweaked it a little to modify the column layout, moving the "who did it" 
to the end of the line, and keeping "what happened" at the start.

Some minor displacement corrections also.

Very useful JCL.

Thank you very much.
Bruce


//ITSXSA3U JOB (ACCT#),'RACF USER=CSMADMIN',
// CLASS=U,
// MSGCLASS=W,
// MSGLEVEL=(1,1),
// NOTIFY=
//*
//***
//*   THIS WILL READ THE SMF EXTRACT DATASET OF RACF RELATED SMF
//*   RECORDS (TYPES 30, 80, 81, 82, 83) AND USE THE SORT TOOL
//*   TO SORT THE DATA AND GENERATE A REPORT.
//*
//*   THIS JOB WILL REPORT ALL SMF EVENTS FOR A SELECTED USERID.
//*
//***
//*
//   SET USERNAME='CSMADMIN'
//   SET REPORT='ITSXSA3.RACFICE.REPORT.CSMADMIN.REFORMAT'
//*
//*
//* Unload the SMF data for RACF records and format them using   **
//* IRRADU00 **
//*
//SMFDUMP  EXEC PGM=IFASMFDP
//SYSPRINT DD  SYSOUT=*
//ADUPRINT DD  SYSOUT=*
//SMFDATA  DD  DISP=SHR,DSN=ITSXSA3.SMFACCUM.CPBK.HB
// DD  DISP=SHR,DSN=ITSXSA3.SMFACCUM.CPRD.HB
//OUTDDDD  DSN=&IRASMF,DISP=(NEW,PASS),
// SPACE=(CYL,(100,100),RLSE),
// UNIT=(SYSDA,4),
// BLKSIZE=32760
//SMFOUT   DD  DUMMY
//SYSINDD  *
  INDD(SMFDATA,OPTIONS(DUMP))
  OUTDD(SMFOUT,TYPE(000:255))
  ABEND(NORETRY)
  USER2(IRRADU00)
  USER3(IRRADU86)
/*
//*
//* create the consolidated report for an user based on the  **
//* Event type using DFSORT. The username can be dynamically **
//* passed. Look at the SET statment up above for the username   **
//* The following Event Types are reported   **
//* ADDUSER/ALTUSER/CONNECT/PASSWORD/PERMIT/RALTER/RDEFINE   **
//*
//UAREPORT EXEC PGM=SORT,PARM='JP1""'
//*
//PRINTDD DSN=,
//DISP=(NEW,CATLG,DELETE),
//UNIT=3390,
//SPACE=(TRK,(15,15))
//*
//SYSOUT   DD SYSOUT=*
//SYMNOUT  DD SYSOUT=*
//SYMNAMES DD *
RDW,1,4,BI
EVENT_TYPE,*,8,CH
SKIP,1
EVENT_QUAL,*,8,CH
SKIP,1
TIME_WRITTEN,*,8,CH
SKIP,1
DATE_WRITTEN,*,10,CH
SKIP,1
SYSTEM_SMFID,*,4,CH
SKIP,1
VIOLATION,*,4,CH
SKIP,1
USER_NDFND,*,4,CH
SKIP,1
USER_WARNING,*,4,CH
SKIP,1
EVT_USER_ID,*,8,CH
SKIP,1
EVT_GRP_ID,*,8,CH
SKIP,1
AUTH_NORMAL,*,4,CH
SKIP,1
AUTH_SPECIAL,*,4,CH
SKIP,1
AUTH_OPER,*,4,CH
SKIP,1
AUTH_AUDIT,*,4,CH
SKIP,1
AUTH_EXIT,*,4,CH
SKIP,1
AUTH_FAILSFT,*,4,CH
SKIP,1
AUTH_BYPASS,*,4,CH
SKIP,1
AUTH_TRUSTED,*,4,CH
SKIP,1
LOG_CLASS,*,4,CH
SKIP,1
LOG_USER,*,4,CH
SKIP,1
LOG_SPECIAL,*,4,CH
SKIP,1
LOG_ACCESS,*,4,CH
SKIP,1
LOG_RACINIT,*,4,CH
SKIP,1
LOG_ALWAYS,*,4,CH
SKIP,1
LOG_CMDVIOL,*,4,CH
SKIP,1
LOG_GLOBAL,*,4,CH
SKIP,1
TERM_LEVEL,*,3,CH
SKIP,1
BACKOUT_FAIL,*,4,CH
SKIP,1
PROF_SAME,*,4,CH
SKIP,1
TERM,*,8,CH
SKIP,1
JOB_NAME,*,8,CH
SKIP,1
READ_TIME,*,8,CH
SKIP,1
READ_DATE,*,10,CH
SKIP,1
SMF_USER_ID,*,8,CH
SKIP,1
LOG_LEVEL,*,4,CH
SKIP,1
LOG_VMEVENT,*,4,CH
SKIP,1
LOG_LOGOPT,*,4,CH
SKIP,1
LOG_SECL,*,4,CH
SKIP,1
LOG_COMPATM,*,4,CH
SKIP,1
LOG_APPLAUD,*,4,CH
SKIP,1
LOG_NONOMVS,*,4,CH
SKIP,1
LOG_OMVSNPRV,*,4,CH
SKIP,1
AUTH_OMVSSU,*,4,CH
SKIP,1
AUTH_OMVSSYS,*,4,CH
SKIP,1
USR_SECL,*,8,CH
SKIP,1
RACF_VERSION,*,4,CH
//SORTIN   DD DISP=SHR,DSN=&IRASMF
/*
//SYSINDD *
  OPTION VLSCMP
  INCLUDE COND=(01,8192,SS,EQ,JP1,AND,
   (EVENT_TYPE,EQ,C'ADDUSER ',OR,
EVENT_TYPE,EQ,C'ALTUSER ',OR,
EVENT_TYPE,EQ,C'CONNECT ',OR,
EVENT_TYPE,EQ,C'PASSWORD',OR,
EVENT_TYPE,EQ,C'PERMIT  ',OR,
EVENT_TYPE,EQ,C'RALTER  ',OR,
EVENT_TYPE,EQ,C'RDEFINE '))

  INREC BUILD=(01,1000)   $ BUILD REQD DATA

  SORT FIELDS=(EVENT_TYPE,A)  $ SORT EVENT TYPE

  OUTREC IFTHEN=(WHEN=(5,8,CH,EQ,C'ADDUSER'),
OVERLAY=(1001:508,008,$ USERID
 1011:08X,$ OWNER
 1021:08X,$ CLASS
 1031:35X,$ RESOURCE
 1071:295,020,$ USER NAME
 1095:517,138)),  $ KEYWORDS

 IFTHEN=(WHEN=(5,8,CH,EQ,C'ALTUSER'),
OVERLAY=(1001:522,008,$ USERID
 1011:286,008,$ OWNER
 1021:08X,$ CLASS
 1031:35X,$ RESOURCE
 1071:295,020,$ USER NAME
 1095:531,127)),  $ KEYWORDS

 IFTHEN=(WHEN=(5,8,CH,EQ,C'CONNECT'),

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Sri h Kolusu]

Elardus,

Thank you for your kind words

Kolusu



From:   Elardus Engelbrecht <elardus.engelbre...@sita.co.za>
To: IBM-MAIN@LISTSERV.UA.EDU
Date:   12/15/2017 01:02 AM
Subject:        Re: DFSORT: RACFICE query - how to extract all commands 
containing some text value
Sent by:IBM Mainframe Discussion List <IBM-MAIN@LISTSERV.UA.EDU>



Sri h Kolusu wrote:

>>>Elardus : Careful, Sri will SORT you out... ;-D
>Nooo. I am not a RACF expert, So I have to agree with Robert. 

Hahaha, and I am not a SORT expert. ;-)


>You can try the JCL that I sent offline and see if that works

Amazing example. ONE pass with SS statement and various type of selection.

I also tested out the SS with a string like ABC, it will catch ids ABCD, 
ABC123, ABC, etc. Amazing.

I am amazed how you use Symbolic names and then re-use them in the BUILD 
statement.

So instead using 055:,008, 

... this is one looking better: 055:EVT_USER_ID,

So, I can then use the symbolic list in a dataset and have all my jobs 
refer to that. One place to change if the IRRADU00 output changes in the 
future.


Ok, what I am currently doing is this:

I use a COPY and then using OUTFILE to direct the output to different Temp 
datasets (think a 'splice' in other way) based on selection criterias. 
Then the DISPLAY is used to make the output pretty and place them 
somewhere else for distribution.

With this setup I could also do an ONE-PASS of the IRRADU00 or IRRDBU00 
outputs and produces various reports with ONE jobstep.


//TOOLIN DD * 
  COPYFROM(INDD) USING(ALSS) 
  DISPLAY FROM(TEMPAA) LIST(PRINTAA) - 
   TITLE('ADDUSER')-
   .
  DISPLAY FROM(TEMPAW) LIST(PRINTAW) - 
TITLE('ALTUSER')- 
.
... etc ...

//ALSSCNTL DD * 
  OPTION  VLSHRT 
  OPTION DYNSPC=512,SIZE=E9,MAINSIZE=MAX 
  SORT   FIELDS=(32,10,CH,A,23,8,CH,A,63,8,CH,A) 
  OUTFIL FNAMES=TEMPAA, 
 INCLUDE=(5,8,CH,EQ,C'ADDUSER')
   OUTFIL FNAMES=TEMPAW, 
 INCLUDE=(5,8,CH,EQ,C'ALTUSER')
   ... etc ...

Many thanks Sri for your excellent example. 

And thanks to Bruce to start the whole story.

Thank too to Robert S Hansel for assisting all of us.

Now, today is Friday and I think everything is SORTed out.

Groete / Greetings
Elardus Engelbrecht

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN






--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Elardus Engelbrecht]

Sri h Kolusu wrote:

>>>Elardus : Careful, Sri will SORT you out... ;-D
>Nooo. I am not a RACF expert, So I have to agree with Robert. 

Hahaha, and I am not a SORT expert. ;-)


>You can try the JCL that I sent offline and see if that works

Amazing example. ONE pass with SS statement and various type of selection.

I also tested out the SS with a string like ABC, it will catch ids ABCD, 
ABC123, ABC, etc. Amazing.

I am amazed how you use Symbolic names and then re-use them in the BUILD 
statement.

So instead using 055:,008,   

... this is one looking better: 055:EVT_USER_ID,

So, I can then use the symbolic list in a dataset and have all my jobs refer to 
that. One place to change if the IRRADU00 output changes in the future.


Ok, what I am currently doing is this:

I use a COPY and then using OUTFILE to direct the output to different Temp 
datasets (think a 'splice' in other way) based on selection criterias. Then the 
DISPLAY is used to make the output pretty and place them somewhere else for 
distribution.

With this setup I could also do an ONE-PASS of the IRRADU00 or IRRDBU00 outputs 
and produces various reports with ONE jobstep.


//TOOLIN DD * 
  COPYFROM(INDD) USING(ALSS)  
  DISPLAY FROM(TEMPAA) LIST(PRINTAA) -
   TITLE('ADDUSER')-
   .
  DISPLAY FROM(TEMPAW) LIST(PRINTAW) -   
TITLE('ALTUSER')-  
.
... etc ...

//ALSSCNTL DD *  
  OPTION  VLSHRT 
  OPTION DYNSPC=512,SIZE=E9,MAINSIZE=MAX 
  SORT   FIELDS=(32,10,CH,A,23,8,CH,A,63,8,CH,A) 
  OUTFIL FNAMES=TEMPAA,  
 INCLUDE=(5,8,CH,EQ,C'ADDUSER')
   OUTFIL FNAMES=TEMPAW,  
 INCLUDE=(5,8,CH,EQ,C'ALTUSER')
   ... etc ...

Many thanks Sri for your excellent example. 

And thanks to Bruce to start the whole story.

Thank too to Robert S Hansel for assisting all of us.

Now, today is Friday and I think everything is SORTed out.

Groete / Greetings
Elardus Engelbrecht

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Sri h Kolusu]

>>> Bruce  My final JCL:-

Bruce,

I sent you a JCL offline which does all the reporting as well as uses 
symbols and the userids can be dynamically passed.  I don't have an input 
data that has all the events that you are looking for.

>>> Martin Packer : This, I think, could use some Symbols love.

I agree and I also removed the individual sorts for each event type  and 
generated the report using the traditional reporting feature SECTIONS 
along with symbols :)

>> Robert Hansel : Kolusu, I would not recommend the use of RACFRW. It was 
stabilized in 1992 and won't report on the use of newer command operands 
such as NOEXPIRE. Bruce is better off using ICETOOL.

Thank you. Since OP is only interested in few of the event types , I just 
passed it on to him.

>>Elardus : Careful, Sri will SORT you out... ;-D

Nooo. I am not a RACF expert, So I have to agree with Robert. You can try 
the JCL that I sent offline and see if that works

Thanks,
Kolusu
DFSORT Development
IBM Corporation

IBM Mainframe Discussion List <IBM-MAIN@LISTSERV.UA.EDU> wrote on 
12/13/2017 10:53:45 PM:

> From: Bruce Hewson <bruce_hew...@hotmail.com>
> To: IBM-MAIN@LISTSERV.UA.EDU
> Date: 12/13/2017 10:54 PM
> Subject: Re: DFSORT: RACFICE query - how to extract all commands 
> containing some text value
> Sent by: IBM Mainframe Discussion List <IBM-MAIN@LISTSERV.UA.EDU>
> 
> Thanks all, especially to Kolusu & Elardus.
> 
> My final JCL:-



--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Elardus Engelbrecht]

Robert S. Hansel (RSH) wrote:

>Kolusu,

Careful, Sri will SORT you out... ;-D


>I would not recommend the use of RACFRW. It was stabilized in 1992 and won't 
>report on the use of newer command operands such as NOEXPIRE. Bruce is better 
>off using ICETOOL.

Or, better - lock up program RACFRW in PROGRAM Class, Profile RACFRW with no 
access at all.

ICETOOL is indeed better, since it produces ONE line per event where RACFRW 
produces several hard to read lines per event.
You can sort your records using Date in this -MM-DD format.

A nice feature is the BREAK(column,chars,CH) BTITLE('???') in ICETOOL 
statements. AFAIK, that is not possible with RACFRW.  

I am now wondering, can RACFRW report on RACDCERT commands and usage of Custom 
Fields for userids?



>Our presentation on DFSORT and ICETOOL and its use with RACF might be of help. 
>You'll find it on our "RACF Center" webpage along with other useful RACF 
>information.
>http://www.rshconsulting.com/racfres.htm

I have a quick look there and see (on page 51) another method to use symbolics 
which I missed initially. Thanks.

Groete / Greetings
Elardus Engelbrecht

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Robert S. Hansel (RSH)]

Kolusu,
I would not recommend the use of RACFRW. It was stabilized in 1992 and won't 
report on the use of newer command operands such as NOEXPIRE. Bruce is better 
off using ICETOOL.

Bruce,
Our presentation on DFSORT and ICETOOL and its use with RACF might be of help. 
You'll find it on our "RACF Center" webpage along with other useful RACF 
information.

http://www.rshconsulting.com/racfres.htm

Regards, Bob

Robert S. Hansel
Lead RACF Specialist
RSH Consulting, Inc. *** Celebrating our 25th Year ***
617-969-8211
www.linkedin.com/in/roberthansel
http://twitter.com/RSH_RACF
www.rshconsulting.com

Upcoming RSH RACF Training - WebEx
- RACF Audit & Compliance Roadmap - FEB 5-9, 2018
- RACF Level I Administration - APR 3-6, 2018
- RACF Level II Administration - JUN 4-8, 2018
- RACF Level III Admin, Audit, & Compliance - FEB 26-MAR 2, 2018
- RACF - Securing z/OS UNIX  - APR 23-27, 2018


-Original Message-
Date:Wed, 13 Dec 2017 10:20:41 -0700
From:Sri h Kolusu <skol...@us.ibm.com>
Subject: Re: DFSORT: RACFICE query - how to extract all commands containing 
some text value

Bruce,

You extract the SMF80 records and process it thru DFSORT.  You can also 
use RACFRW to report 

https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.1.0/com.ibm.zos.v2r1.icha800/racfrw.htm

Example : 

This file produces a report of failed logons for user Smith. 

RACFRW TITLE('REPORT ON FAILED LOGONS FOR USER SMITH')
 SELECT PROCESS  USER(DUMMY)
  EVENT LOGON
SUMMARY USER NEWPAGE
END

You can use EVENT to list all of your event and generate a report

https://www.ibm.com/support/knowledgecenter/SSLTBW_2.1.0/com.ibm.zos.v2r1.icha800/racfrw.htm#racfrw__eventsub

If you still need help please feel free to send your questions to DFSORT 
hotline (dfs...@us.ibm.com) along with a sample input file and desired 
output report.

Thank you Lizette for directing the users to our hotline

Thanks,
Kolusu
DFSORT Development
IBM Corporation



From:   Bruce Hewson <bruce_hew...@hotmail.com>
To: IBM-MAIN@LISTSERV.UA.EDU
Date:   12/13/2017 02:50 AM
Subject:DFSORT: RACFICE query - how to extract all commands 
containing some text value
Sent by:IBM Mainframe Discussion List <IBM-MAIN@LISTSERV.UA.EDU>



A query for the ICETOOL/RACFICE experts,

I have been asked to extract all RACF commands issued relating to a 
specific USERID.

I am not knowledgable enough with ICETOOLS  to code this myself quickly.

The current RACFICE examples do not provide a sample for this.

example.

Extract and report all commands,  ADDUSER/ALTUSER/PASSWORD/PERMIT that 
reference USER(xyzzy)


Hopefully someone can help.

Thanks
Bruce Hewson

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Martin Packer]

This, I think, could use some Symbols love.

Cheers, Martin

Martin Packer

> On 14 Dec 2017, at 05:54, Bruce Hewson  wrote:
>
> Thanks all, especially to Kolusu & Elardus.
>
> My final JCL:-
>
> //***
> //EXTRACT  EXEC PGM=ICETOOL
> //IRRADU00 DD DISP=OLD,DSN=XYZZY.RACFICE.IRRADU00.ALL
> //EXTRACT  DD DISP=OLD,DSN=XYZZY.RACFICE.IRRADU00.EXTRACT
> //PRINTDD DISP=(MOD,DELETE),
> //UNIT=3390,
> //LRECL=500,RECFM=FB,BLKSIZE=0,
> //SPACE=(TRK,(15,15)),
> //DSN=XYZZY.RACFICE.REPORT.CSMADMIN
> //TOOLMSG  DD DUMMY
> //DFSMSG   DD DUMMY
> //SYSINDD *
> /*
> //*--
> //TOOLIN   DD *
> COPYFROM(IRRADU00) TO(EXTRACT) USING(RACF)
> /*
> //*--
> //RACFCNTL DD *
> OUTFILE FNAMES=EXTRACT,
>  INCLUDE=(1,8192,SS,EQ,C'XYZZY')
> OPTION VLSCMP
> /*
> //REPORT   EXEC PGM=ICETOOL
> //EXTRACT  DD DISP=OLD,DSN=XYZZY.RACFICE.IRRADU00.EXTRACT
> //TEMP0001 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))
> //TEMP0002 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))
> //TEMP0003 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))
> //TEMP0004 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))
> //TEMP0005 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))
> //TEMP0006 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))
> //TEMP0007 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))
> //PRINTDD DISP=(MOD,CATLG),
> //UNIT=3390,
> //LRECL=500,RECFM=FB,BLKSIZE=0,
> //SPACE=(TRK,(15,15)),
> //DSN=XYZZY.RACFICE.REPORT.CSMADMIN
> //TOOLMSG  DD DUMMY
> //DFSMSG   DD DUMMY
> //SYSINDD *
> /*
> //*--
> //TOOLIN   DD *
> SORTFROM(EXTRACT) TO(TEMP0001) USING(ADDU)
> DISPLAY FROM(TEMP0001) LIST(PRINT)  -
> NOCC-
> WIDTH(500)  -
> TITLE('RACF ADDUSER Command Report')-
> DATE(4MD/)  -
> TIME(24:)   -
> PAGE-
> BLANK   -
> ON(32,10,CH)   HEADER('DATE')   -
> ON(23,8,CH)HEADER('TIME')   -
> ON(43,4,CH)HEADER('SMFID')  -
> ON(14,8,CH)HEADER('RESULT') -
> ON(5,8,CH) HEADER('COMMAND')-
> ON(508,8,CH)   HEADER('USER ID')-
> ON(517,138,CH) HEADER('KEYWORDS')   -
> ON(63,8,CH)HEADER('ISSUER') -
> ON(72,8,CH)HEADER('GROUP')  -
> ON(175,8,CH)   HEADER('TERMINAL')   -
> ON(184,8,CH)   HEADER('JOB NAME')   -
> ON(295,20,CH)  HEADER('USER NAME')
> SORTFROM(EXTRACT) TO(TEMP0002) USING(ALTU)
> DISPLAY FROM(TEMP0002) LIST(PRINT)  -
> NOCC-
> WIDTH(500)  -
> TITLE('RACF ALTUSER Command Report')-
> DATE(4MD/)  -
> TIME(24:)   -
> PAGE-
> BLANK   -
> ON(32,10,CH)   HEADER('DATE')   -
> ON(23,8,CH)HEADER('TIME')   -
> ON(43,4,CH)HEADER('SMFID')  -
> ON(14,8,CH)HEADER('RESULT') -
> ON(5,8,CH) HEADER('COMMAND')-
> ON(522,8,CH)   HEADER('USER ID')-
> ON(286,8,CH)   HEADER('OWNER  ')-
> ON(531,127,CH) HEADER('KEYWORDS USED')  -
> ON(63,8,CH)HEADER('ISSUER') -
> ON(72,8,CH)HEADER('GROUP')  -
> ON(175,8,CH)   HEADER('TERMINAL')   -
> ON(184,8,CH)   HEADER('JOB NAME')   -
> ON(295,20,CH)  HEADER('USER NAME')
> SORTFROM(EXTRACT) TO(TEMP0003) USING(CONU)
> DISPLAY FROM(TEMP0003) LIST(PRINT)  -
> NOCC-
> WIDTH(500)  -
> TITLE('RACF CONNECT Command Report')-
> DATE(4MD/)  -
> TIME(24:)   -
> PAGE-
> BLANK   -
> ON(32,10,CH)   HEADER('DATE')   -
> ON(23,8,CH)HEADER('TIME')   -
> ON(43,4,CH)HEADER('SMFID')  -
> ON(14,8,CH)HEADER('RESULT') -
> ON(5,8,CH) HEADER('COMMAND')-
> ON(498,8,CH)   HEADER('USER ID')-
> ON(507,138,CH) HEADER('KEYWORDS USED')  -
> ON(63,8,CH)HEADER('ISSUER') -
> 

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Bruce Hewson]

Thanks all, especially to Kolusu & Elardus.

My final JCL:-

//***
//EXTRACT  EXEC PGM=ICETOOL  
//IRRADU00 DD DISP=OLD,DSN=XYZZY.RACFICE.IRRADU00.ALL
//EXTRACT  DD DISP=OLD,DSN=XYZZY.RACFICE.IRRADU00.EXTRACT
//PRINTDD DISP=(MOD,DELETE), 
//UNIT=3390, 
//LRECL=500,RECFM=FB,BLKSIZE=0,  
//SPACE=(TRK,(15,15)),   
//DSN=XYZZY.RACFICE.REPORT.CSMADMIN  
//TOOLMSG  DD DUMMY  
//DFSMSG   DD DUMMY  
//SYSINDD *  
/*   
//*--
//TOOLIN   DD *  
 COPYFROM(IRRADU00) TO(EXTRACT) USING(RACF)  
/*   
//*--
//RACFCNTL DD *  
 OUTFILE FNAMES=EXTRACT, 
  INCLUDE=(1,8192,SS,EQ,C'XYZZY')
 OPTION VLSCMP   
/*   
//REPORT   EXEC PGM=ICETOOL  
//EXTRACT  DD DISP=OLD,DSN=XYZZY.RACFICE.IRRADU00.EXTRACT
//TEMP0001 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))  
//TEMP0002 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))  
//TEMP0003 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))  
//TEMP0004 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))  
//TEMP0005 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))  
//TEMP0006 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))  
//TEMP0007 DD DISP=(NEW,DELETE,DELETE),SPACE=(0,(1000,350))  
//PRINTDD DISP=(MOD,CATLG),  
//UNIT=3390, 
//LRECL=500,RECFM=FB,BLKSIZE=0,  
//SPACE=(TRK,(15,15)),   
//DSN=XYZZY.RACFICE.REPORT.CSMADMIN  
//TOOLMSG  DD DUMMY  
//DFSMSG   DD DUMMY  
//SYSINDD *  
/*   
//*--
//TOOLIN   DD *  
 SORTFROM(EXTRACT) TO(TEMP0001) USING(ADDU)  
 DISPLAY FROM(TEMP0001) LIST(PRINT)  -   
 NOCC-   
 WIDTH(500)  -   
 TITLE('RACF ADDUSER Command Report')-   
 DATE(4MD/)  -   
 TIME(24:)   -   
 PAGE-   
 BLANK   -   
 ON(32,10,CH)   HEADER('DATE')   -   
 ON(23,8,CH)HEADER('TIME')   -   
 ON(43,4,CH)HEADER('SMFID')  -   
 ON(14,8,CH)HEADER('RESULT') -   
 ON(5,8,CH) HEADER('COMMAND')-   
 ON(508,8,CH)   HEADER('USER ID')-   
 ON(517,138,CH) HEADER('KEYWORDS')   -   
 ON(63,8,CH)HEADER('ISSUER') -   
 ON(72,8,CH)HEADER('GROUP')  -   
 ON(175,8,CH)   HEADER('TERMINAL')   -   
 ON(184,8,CH)   HEADER('JOB NAME')   -   
 ON(295,20,CH)  HEADER('USER NAME')  
 SORTFROM(EXTRACT) TO(TEMP0002) USING(ALTU)  
 DISPLAY FROM(TEMP0002) LIST(PRINT)  -   
 NOCC-   
 WIDTH(500)  -   
 TITLE('RACF ALTUSER Command Report')-   
 DATE(4MD/)  -   
 TIME(24:)

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Sri h Kolusu]

Bruce,

You extract the SMF80 records and process it thru DFSORT.  You can also 
use RACFRW to report 

https://www.ibm.com/support/knowledgecenter/en/SSLTBW_2.1.0/com.ibm.zos.v2r1.icha800/racfrw.htm

Example : 

This file produces a report of failed logons for user Smith. 

RACFRW TITLE('REPORT ON FAILED LOGONS FOR USER SMITH')
 SELECT PROCESS  USER(DUMMY)
  EVENT LOGON
SUMMARY USER NEWPAGE
END

You can use EVENT to list all of your event and generate a report

https://www.ibm.com/support/knowledgecenter/SSLTBW_2.1.0/com.ibm.zos.v2r1.icha800/racfrw.htm#racfrw__eventsub

If you still need help please feel free to send your questions to DFSORT 
hotline (dfs...@us.ibm.com) along with a sample input file and desired 
output report.

Thank you Lizette for directing the users to our hotline

Thanks,
Kolusu
DFSORT Development
IBM Corporation



From:   Bruce Hewson 
To: IBM-MAIN@LISTSERV.UA.EDU
Date:   12/13/2017 02:50 AM
Subject:DFSORT: RACFICE query - how to extract all commands 
containing some text value
Sent by:IBM Mainframe Discussion List 



A query for the ICETOOL/RACFICE experts,

I have been asked to extract all RACF commands issued relating to a 
specific USERID.

I am not knowledgable enough with ICETOOLS  to code this myself quickly.

The current RACFICE examples do not provide a sample for this.

example.

Extract and report all commands,  ADDUSER/ALTUSER/PASSWORD/PERMIT that 
reference USER(xyzzy)


Hopefully someone can help.

Thanks
Bruce Hewson

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN






--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Lizette Koehler]

Two suggestions

The RACF List might be helpful with this question. To join, if you have not 
done so, use this URL

RACFhttp://www.listserv.uga.edu/archives/racf-l.html


Next, there is a DFSORT HOTLINE from IBM who can always help with DFSORT 
coding.  Send an email to dfs...@us.ibm.com

Hope this helps

Lizette


> -Original Message-
> From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On
> Behalf Of Bruce Hewson
> Sent: Wednesday, December 13, 2017 2:49 AM
> To: IBM-MAIN@LISTSERV.UA.EDU
> Subject: DFSORT: RACFICE query - how to extract all commands containing some
> text value
> 
> A query for the ICETOOL/RACFICE experts,
> 
> I have been asked to extract all RACF commands issued relating to a specific
> USERID.
> 
> I am not knowledgable enough with ICETOOLS  to code this myself quickly.
> 
> The current RACFICE examples do not provide a sample for this.
> 
> example.
> 
> Extract and report all commands,  ADDUSER/ALTUSER/PASSWORD/PERMIT that
> reference USER(xyzzy)
> 
> 
> Hopefully someone can help.
> 
> Thanks
> Bruce Hewson
> 

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Ron hawkins]

Bruce,

You used to have access to SAS and MXG. Have you looked at this as an 
alternative to processing RACFICE?

If you use the TYPS80A member, I think you can piece together what you are 
looking for. For example, the ADDUSER command is type80 subtype 10, and 
therefore TYPE8010 in the PDB.

Some simple SQL WHERE statements against the relevant text fields looking for 
the would quickly find what you are looking for.

Ron

-Original Message-
From: IBM Mainframe Discussion List [mailto:IBM-MAIN@LISTSERV.UA.EDU] On Behalf 
Of Bruce Hewson
Sent: Wednesday, December 13, 2017 1:49 AM
To: IBM-MAIN@LISTSERV.UA.EDU
Subject: [IBM-MAIN] DFSORT: RACFICE query - how to extract all commands 
containing some text value

A query for the ICETOOL/RACFICE experts,

I have been asked to extract all RACF commands issued relating to a specific 
USERID.

I am not knowledgable enough with ICETOOLS  to code this myself quickly.

The current RACFICE examples do not provide a sample for this.

example.

Extract and report all commands,  ADDUSER/ALTUSER/PASSWORD/PERMIT that 
reference USER(xyzzy)


Hopefully someone can help.

Thanks
Bruce Hewson

--
For IBM-MAIN subscribe / signoff / archive access instructions, send email to 
lists...@listserv.ua.edu with the message: INFO IBM-MAIN

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Martin Packer]

I don't think he'll mind me mentioning it but Mark Nelson was the 
originator of RACFICE - but that was LONG ago.

He might well have a view.

And the advice to learn some DFSORT is good.

Cheers, Martin

Martin Packer

zChampion, Systems Investigator & Performance Troubleshooter, IBM

+44-7802-245-584

email: martin_pac...@uk.ibm.com

Twitter / Facebook IDs: MartinPacker

Blog: 
https://www.ibm.com/developerworks/mydeveloperworks/blogs/MartinPacker

Podcast Series (With Marna Walle): https://developer.ibm.com/tv/mpt/or 
  
https://itunes.apple.com/gb/podcast/mainframe-performance-topics/id1127943573?mt=2


Youtube channel: https://www.youtube.com/channel/UCu_65HaYgksbF6Q8SQ4oOvA



From:   Elardus Engelbrecht <elardus.engelbre...@sita.co.za>
To: IBM-MAIN@LISTSERV.UA.EDU
Date:   13/12/2017 11:18
Subject:    Re: DFSORT: RACFICE query - how to extract all commands 
containing some text value
Sent by:IBM Mainframe Discussion List <IBM-MAIN@LISTSERV.UA.EDU>



Bruce Hewson wrote:

>A query for the ICETOOL/RACFICE experts,

>I have been asked to extract all RACF commands issued relating to a 
specific USERID.
>I am not knowledgable enough with ICETOOLS  to code this myself quickly.

You will have to learn it. Or post your queries on RACF-L. Perhaps someone 
from DFSORT support chimes in here.


This is a two step thing. You first use RACF SMF Unload and then you use 
the ICETOOL job.

This process is hungry hungry HUNGRY on disk space, because the compressed 
SMF records are expanded. Bit fields are expanded in a full 4 bytes per 
bit. say x'01' is expanded into 'YES ' and x'00' to 'NO  '.

Or you can go the DB2 way. I have not done that way.


>The current RACFICE examples do not provide a sample for this.

I am not surprised, but I started my life from that RACFICE examples and 
moved on.

Perhaps there are DB2 samples for that, I simply don't know or just don't 
care.


>Extract and report all commands,  ADDUSER/ALTUSER/PASSWORD/PERMIT that 
reference USER(xyzzy)

Please note: There are two USER fields involved in a RACF command - Issuer 
and subject of the id. 

Issuer is on a specific column, but the subject Userid is in a specific 
column or specific field depending on the command used.

For commands like PERMIT, you won't get a specific id, but you will need 
to examine the 'Keywords Used' and then there you examine the field.


Example for ADDUSER, I have this:

//TOOLIN   DD * 
 SORTFROM(INDD) TO(TEMP0001) USING(ADD1)
 DISPLAY FROM(TEMP0001) LIST(PRINT1)  - 
 NOCC - 
 WIDTH(500)   - 
 TITLE('RACF ADDUSER Report') - 
 DATE(4MD/)   - 
 TIME(24:)- 
 PAGE - 
 BLANK- 
 ON(63,8,CH) HEADER('Issuer')  -
 ON(295,20,CH) HEADER('User Name') -
 ON(72,8,CH) HEADER('Group')   -
 ON(32,10,CH) HEADER('Date')   -
 ON(23,8,CH) HEADER('Time')-
 ON(508,8,CH) HEADER('User Id')-
 ON(43,4,CH) HEADER('SMFID')   -
 ON(175,8,CH) HEADER('Terminal')   -
 ON(184,8,CH) HEADER('Jobname ')   -
 ON(14,8,CH) HEADER('Result')  -
 ON(517,200,CH) HEADER('Keywords') 

//ADD1CNTL   DD * 
 SORT FIELDS=(5,8,CH,A) 
 INCLUDE COND=(5,8,CH,EQ,C'ADDUSER ',AND,
  32,10,CH,GE,C'2017-01',AND,
 508,3,CH,EQ,C'ABC') 
 OPTION VLSHRT 


For ALTUSER I have this example:

 SORTFROM(INDD) TO(TEMP0001) USING(ULDF) 
 DISPLAY FROM(TEMP0001) LIST(PRINT) - 
 NOCC - 
 WIDTH(500)   - 
 TITLE('Group Special Users Activity') -
 DATE(4MD/)   - 
 TIME(24:)- 
 PAGE - 
 BLANK- 
 ON(63,8,CH)HEADER('Issuer')- 
 ON(295,20,CH)  HEADER('User Name') - 
 ON(72,8,CH)HEADER('Group') - 
 ON(32,10,CH)   HEADER('Date')  - 
 ON(23,8,CH)HEADER('Time')  - 
 ON(522,8,CH)   HEADER('User Id')   - 
 ON(43,4,CH)HEADER('SMFID') - 
 ON(175,8,CH)   HEADER('Terminal')  - 
 ON(184,8,CH)   HEADER('Job Name')  - 
 ON(286,8,CH)   HEADER('Owner  ')   - 
 ON(14,8,CH)HEADER('Result')- 
 ON(531,200,CH) HEADER('Keywords Used') 
//ULDFCNTL   DD * 
 SORT FIELDS=(5,8,CH,A) 
 INCLUDE COND=(5,8,CH,EQ,C'ALTUSER ',AND, 
   32,7,CH,GE,C'2017-01',AND, 
   522,3,CH,EQ,C'ABC') 
 OPTION VLSHRT 

... and so on ...

Good luck. You will really need it! Just shout again if you need more 
help.

Groete / Greetings
Elardus Engelbrecht

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN




Unless stated otherwise above:
IBM United Kingdom Limited - 

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Elardus Engelbrecht]

Radoslaw Skorupka wrote:

>Use SS. Of course I don't mean bad guys from WWII times, this is SubString. ;-)

>You can filter (INCLUDE/OMIT) any record containing given string (userid) 
>wherever in the record or some field of the record.

Yes, I forgot about that advanced helpful command SS! It is a long time ago, I 
used that little gem. ;-)

Here is a crude ugly example:

//SELECT   EXEC PGM=ICETOOL 
//TOOLMSG  DD SYSOUT=*  
//DFSMSG   DD SYSOUT=*  
//PRINTDD SYSOUT=*,LRECL=8192   
//INVOER   DD DISP=SHR,DSN=???.RACF.ALLCMDS
//TEMP0001 DD DISP=(NEW,DELETE),SPACE=(CYL,(100,100,0)),
// UNIT=3390
//TOOLIN   DD * 
 COPYFROM(INVOER) TO(PRINT) USING(TELB) 
//TELBCNTL   DD *   
OPTION VLSCMP   
 OUTFILE FNAMES=PRINT,  
  INCLUDE=(1,8192,SS,EQ,C'')

This will pick up and "copy/print" any line containing that search argument.

Groete / Greetings
Elardus Engelbrecht

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[R.S.]

Use SS. Of course I don't mean bad guys from WWII times, this is 
SubString. ;-)
You can filter (INCLUDE/OMIT) any record containing given string 
(userid) wherever in the record or some field of the record.



--
Radoslaw Skorupka
Lodz, Poland







W dniu 2017-12-13 o 10:48, Bruce Hewson pisze:

A query for the ICETOOL/RACFICE experts,

I have been asked to extract all RACF commands issued relating to a specific 
USERID.

I am not knowledgable enough with ICETOOLS  to code this myself quickly.

The current RACFICE examples do not provide a sample for this.

example.

Extract and report all commands,  ADDUSER/ALTUSER/PASSWORD/PERMIT that 
reference USER(xyzzy)


Hopefully someone can help.

Thanks
Bruce Hewson

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
.




==


   --
Treść tej wiadomości może zawierać informacje prawnie chronione Banku 
przeznaczone wyłącznie do użytku służbowego adresata. Odbiorcą może być jedynie 
jej adresat z wyłączeniem dostępu osób trzecich. Jeżeli nie jesteś adresatem 
niniejszej wiadomości lub pracownikiem upoważnionym do jej przekazania 
adresatowi, informujemy, że jej rozpowszechnianie, kopiowanie, rozprowadzanie 
lub inne działanie o podobnym charakterze jest prawnie zabronione i może być 
karalne. Jeżeli otrzymałeś tę wiadomość omyłkowo, prosimy niezwłocznie 
zawiadomić nadawcę wysyłając odpowiedź oraz trwale usunąć tę wiadomość 
włączając w to wszelkie jej kopie wydrukowane lub zapisane na dysku.

This e-mail may contain legally privileged information of the Bank and is 
intended solely for business use of the addressee. This e-mail may only be 
received by the addressee and may not be disclosed to any third parties. If you 
are not the intended addressee of this e-mail or the employee authorized to 
forward it to the addressee, be advised that any dissemination, copying, 
distribution or any other similar activity is legally prohibited and may be 
punishable. If you received this e-mail by mistake please advise the sender 
immediately by using the reply facility in your e-mail software and delete 
permanently this e-mail including any copies of it either printed or saved to 
hard drive.

mBank S.A. z siedzibą w Warszawie, ul. Senatorska 18, 00-950 Warszawa, 
www.mBank.pl, e-mail: kont...@mbank.plsąd Rejonowy dla m. st. Warszawy XII 
Wydział Gospodarczy Krajowego Rejestru Sądowego, nr rejestru przedsiębiorców 
KRS 025237, NIP: 526-021-50-88. Według stanu na dzień 01.01.2016 r. kapitał 
zakładowy mBanku S.A. (w całości wpłacony) wynosi 168.955.696 złotych.
   


--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN

Re: DFSORT: RACFICE query - how to extract all commands containing some text value

[Elardus Engelbrecht]

Bruce Hewson wrote:

>A query for the ICETOOL/RACFICE experts,

>I have been asked to extract all RACF commands issued relating to a specific 
>USERID.
>I am not knowledgable enough with ICETOOLS  to code this myself quickly.

You will have to learn it. Or post your queries on RACF-L. Perhaps someone from 
DFSORT support chimes in here.


This is a two step thing. You first use RACF SMF Unload and then you use the 
ICETOOL job.

This process is hungry hungry HUNGRY on disk space, because the compressed SMF 
records are expanded. Bit fields are expanded in a full 4 bytes per bit. say 
x'01' is expanded into 'YES ' and x'00' to 'NO  '.

Or you can go the DB2 way. I have not done that way.


>The current RACFICE examples do not provide a sample for this.

I am not surprised, but I started my life from that RACFICE examples and moved 
on.

Perhaps there are DB2 samples for that, I simply don't know or just don't care.


>Extract and report all commands,  ADDUSER/ALTUSER/PASSWORD/PERMIT that 
>reference USER(xyzzy)

Please note: There are two USER fields involved in a RACF command - Issuer and 
subject of the id. 

Issuer is on a specific column, but the subject Userid is in a specific column 
or specific field depending on the command used.

For commands like PERMIT, you won't get a specific id, but you will need to 
examine the 'Keywords Used' and then there you examine the field.


Example for ADDUSER, I have this:

//TOOLIN   DD * 
 SORTFROM(INDD) TO(TEMP0001) USING(ADD1)
 DISPLAY FROM(TEMP0001) LIST(PRINT1)  - 
 NOCC - 
 WIDTH(500)   - 
 TITLE('RACF ADDUSER Report') - 
 DATE(4MD/)   - 
 TIME(24:)- 
 PAGE - 
 BLANK- 
 ON(63,8,CH) HEADER('Issuer')  -
 ON(295,20,CH) HEADER('User Name') -
 ON(72,8,CH) HEADER('Group')   -
 ON(32,10,CH) HEADER('Date')   -
 ON(23,8,CH) HEADER('Time')-
 ON(508,8,CH) HEADER('User Id')-
 ON(43,4,CH) HEADER('SMFID')   -
 ON(175,8,CH) HEADER('Terminal')   -
 ON(184,8,CH) HEADER('Jobname ')   -
 ON(14,8,CH) HEADER('Result')  -
 ON(517,200,CH) HEADER('Keywords')  

//ADD1CNTL   DD *
 SORT FIELDS=(5,8,CH,A)  
 INCLUDE COND=(5,8,CH,EQ,C'ADDUSER ',AND,
  32,10,CH,GE,C'2017-01',AND,
 508,3,CH,EQ,C'ABC') 
 OPTION VLSHRT   


For ALTUSER I have this example:

 SORTFROM(INDD) TO(TEMP0001) USING(ULDF)
 DISPLAY FROM(TEMP0001) LIST(PRINT) -   
 NOCC - 
 WIDTH(500)   - 
 TITLE('Group Special Users Activity') -
 DATE(4MD/)   - 
 TIME(24:)- 
 PAGE - 
 BLANK- 
 ON(63,8,CH)HEADER('Issuer')-   
 ON(295,20,CH)  HEADER('User Name') -   
 ON(72,8,CH)HEADER('Group') -   
 ON(32,10,CH)   HEADER('Date')  -   
 ON(23,8,CH)HEADER('Time')  -   
 ON(522,8,CH)   HEADER('User Id')   -   
 ON(43,4,CH)HEADER('SMFID') -   
 ON(175,8,CH)   HEADER('Terminal')  -   
 ON(184,8,CH)   HEADER('Job Name')  -   
 ON(286,8,CH)   HEADER('Owner  ')   -   
 ON(14,8,CH)HEADER('Result')-   
 ON(531,200,CH) HEADER('Keywords Used') 
//ULDFCNTL   DD *   
 SORT FIELDS=(5,8,CH,A) 
 INCLUDE COND=(5,8,CH,EQ,C'ALTUSER ',AND,   
   32,7,CH,GE,C'2017-01',AND,   
   522,3,CH,EQ,C'ABC')  
 OPTION VLSHRT  

... and so on ...

Good luck. You will really need it! Just shout again if you need more help.

Groete / Greetings
Elardus Engelbrecht

--
For IBM-MAIN subscribe / signoff / archive access instructions,
send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN