PSACCESLOG - Review users who login

Here are some queries to detail who has logged into the Peoplesoft system (Web Tier)


-- Unique IP Address Count, per hour, for the last 24 hours
-- Change the "1" to be "7" for seven days of activity
select DTTM, count(*)
from (select distinct a.logipaddress "IP",
to_char(a.logindttm,'MM/DD/YYYY HH24') "DTTM"
from psaccesslog a
where a.logindttm > sysdate - 1)
group by DTTM;


-- Find the IP Activity for a individual, for the last 24 hours
-- Change the "1" to be "7" for seven days of activity
select a.*, b.oprdefndesc from psaccesslog a, psoprdefn b
where a.logindttm > sysdate - 7 -- This indicates today and the past seven days...
and a.oprid = b.oprid
and b.oprdefndesc like '%Smith%'
ORDER BY A.LOGINDTTM DESC;