Need to query a DSPUSRPRF outfile for date greater than 90 days

[Chris]

Hello,

I have a outfile of a DSPUSRPRF and I would like to use this outfile
in a query to show me all user profiles that have not been used in
over 90 days for system maintenance. The field that contains the date
is UPPSOD and is displayed in the YYMMDD format. for exampe 080507
would be the date and i want to know the profiles that haven't been
used 90 days from this date. Could someone explain this in plain
english to me how it could work?

Thanks in advance.

Chris

[news.chartermi.com]

AFaIK the noted field will not provide an answer to the question of
when a user profile has last "been used". I believe that is the field
which represents the last interactive signon date, which means access to
the system by file systems, database, and non-emulator communications
[e.g. FTP, DDM, batch jobs] would not be represented. Past discussions
will suggest that DSPOBJD QSYS/*ALL *USRPRF *FULL should be used
instead, or in combination with, the DSPUSRPRF output file.

Additionally there are some security reports and tools which may
already provide what is of interest. I think those are found using the
GO SECTOOLS [and GO SECURITY ?], from which ANZPRFACT and other actions
are available.

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzamv/rzamvtoolcustomsec.htm
http://www.itjungle.com/mpo/mpo112003-story04.html

Regards, Chuck

[Karl Hanson]

Rudy Canoza wrote:

> The easiest way is to determine first what date is 90 days ago, or 90
> days earlier than whatever date you want to use. You could do this in a
> SQL statement:
>
> select current_date - 90 days from sysibm.sysdummy1
>
> - or -
>
> select date('2008-05-01') - 90 days from sysibm.sysdummy1
> (if you wanted to look back from some date other than today.)
>
>
> 90 days earlier from today, May 8, is Feb 8, so you'd convert that to
> the same format as UPPSOD, i.e. 'yymmdd' ==> 080208.
>
> Now run a SQL statement like this:
>
> select upuprf,uptext,uppsod
> from yourlib.userprofs
> where uppsod <> ' '
> and uppsod <= '080208'
> and upstat = '*ENABLED'
>
> I added that last criterion because if the profile is disabled, and the
> last signon was 90 days ago or earlier, you probably don't care about
> those...unless, perhaps, you want those in order to delete them, in
> which case change it to '*DISABLED'.
>
> You could write a SQL user-defined function (called cvt_to_ISO in the
> example below) that would convert UPPSOD to ISO date format, and then
> you would change your statement to read:
>
> select upuprf,uptext,uppsod
> from yourlib.userprofs
> where uppsod <> ' '
> and cvt_to_ISO(uppsod) <= current_date - 90 days
> and upstat = '*ENABLED'
>
> but if this is only a one-time or very infrequent task, it's probably
> not worth the bother. Note that it's important in this case either to
> exclude the records with UPPSOD = ' ', or to ensure that your UDF
> handles them by, say, returning a valid date value if the input value of
> UPPSOD is blank.

If using the UDF option, another possibility is the SQL DAYS scalar
function. This returns an integer representation. FWIW...

--
Karl Hanson

[Graybeard]

news.chartermi is correct. This represents the last interactive
logon. It does not get updated for connections from ODBC, etc.

The SECTOOLS menu option 4 does the report you ask for, but with the
same limitation.