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

This is a discussion on Need to query a DSPUSRPRF outfile for date greater than 90 days - IBM AS400 ; 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 ...

+ Reply to Thread
Results 1 to 5 of 5

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

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

    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

  2. Re: Need to query a DSPUSRPRF outfile for date greater than 90 days

    Chris wrote:
    > 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.


    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.

  3. Re: Need to query a DSPUSRPRF outfile for date greater than 90 days

    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/infoce...lcustomsec.htm
    http://www.itjungle.com/mpo/mpo112003-story04.html

    Regards, Chuck

    Chris wrote:
    > 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?


  4. Re: Need to query a DSPUSRPRF outfile for date greater than 90 days

    Rudy Canoza wrote:
    > Chris wrote:
    >> 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.

    >
    > 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

  5. Re: Need to query a DSPUSRPRF outfile for date greater than 90 days

    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.

+ Reply to Thread