SQL to get the list of users connected to the environment in last 30 days, with connections count.

-
SELECT TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD'),A.OPRID, sum(1) "Connections"
      FROM PSACCESSLOG A
     WHERE TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD') >= TO_CHAR(SYSDATE - 30,'YYYY-MM-DD')
     GROUP BY TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD'),A.OPRID
     ORDER BY TO_CHAR(A.LOGINDTTM,'YYYY-MM-DD') DESC, A.OPRID;

Comments

Post a Comment

Popular posts from this blog

BI Publisher: If Condition with sub-string in rtf template

-
 <?xdofx:if substr(ACAD_PLAN,1,1) = 'C'  then 'Los Angeles City College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'H'  then 'Los Angeles Harbor College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'M'  then 'Los Angeles Mission College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'P'  then 'Pierce College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'S'  then 'Los Angeles Southwest College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'V'  then 'Los Angeles Valley College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'W'  then 'West Los Angeles College' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'T'  then 'LA Trade-Technical Colleg' end if?>  <?xdofx:if substr(ACAD_PLAN,1,1) = 'E'  then 'East Los Angeles College' end if?>;
Read more