masmatc
asked on
SQL Query for count function on multiple tables
Hello experts - I need to build a SQL query against an Oracle Database (two tables) that would do the following:
- count the number of sucessful hits (authentication success) to a particular application for each day and sorted by Department, Departmentcode, and day of the month.
such that output of the query looks like:
Department code Day Portal App1 App2
Marketting 36 YY-MM-DD 134 54 89
Finance 16 YY-MM-DD 3 5 3
Development 12 YY-MM-DD 3 76 0
There are two tables of interest one that contains the audit information (audit) and the other that contains the department information (user).
Here is the audit table information:
CREATE TABLE AUDIT
( "EVENTDATEANDTIME" TIMESTAMP (6),
"SERVERID" NVARCHAR2(255),
"EVENTNAME" NVARCHAR2(255),
"URL" NVARCHAR2(255),
"OPERATION" NVARCHAR2(255),
"TARGETUSERDN" NVARCHAR2(255),
"IPADDRESS" NVARCHAR2(255),
"USERPROFILEATTRS" NVARCHAR2(255),
)
The sample data from audit table looks like this:
EVENTDATEANDTIME = 18-AUG-10 07.33.01.000000000 PM
SERVERID = myserver1
EVENTNAME = AUTHN_SUCCESS
URL = xyz.mydomain.com%2Fportal% 2Flang%2Fe n-us%2defa ult.aspx
OPERATION = GET
TARGETUSERDN = uid=markuser1,ou=people,o= mycomany,c =com
IPADDRESS = 10.8.146.134
USERPROFILEATTRS = uid=markuser1
EVENTDATEANDTIME = 19-AUG-10 05.30.09.000000000 PM
SERVERID = myserver2
EVENTNAME = AUTHN_SUCCESS
URL = xyz.mydomain.com%2FAPP1%2F lang%2Fen- us%2defaul t.aspx
OPERATION = GET
TARGETUSERDN = uid=finuser1,ou=people,o=m ycomany,c= com
IPADDRESS = 10.8.146.130
USERPROFILEATTRS = uid=finuser1
Here is the sample user table information:
CREATE TABLE user
( userid varchar2(50) not null,
department_name varchar2(50) not null,
department_code number(6) not null,
)
The sample data from user table looks like this:
userid = markuser1
Department_name = Marketting
Department_code = 36
userid = finuser1
Department_name = Finance
Department_code = 12
Thanks,
- count the number of sucessful hits (authentication success) to a particular application for each day and sorted by Department, Departmentcode, and day of the month.
such that output of the query looks like:
Department code Day Portal App1 App2
Marketting 36 YY-MM-DD 134 54 89
Finance 16 YY-MM-DD 3 5 3
Development 12 YY-MM-DD 3 76 0
There are two tables of interest one that contains the audit information (audit) and the other that contains the department information (user).
Here is the audit table information:
CREATE TABLE AUDIT
( "EVENTDATEANDTIME" TIMESTAMP (6),
"SERVERID" NVARCHAR2(255),
"EVENTNAME" NVARCHAR2(255),
"URL" NVARCHAR2(255),
"OPERATION" NVARCHAR2(255),
"TARGETUSERDN" NVARCHAR2(255),
"IPADDRESS" NVARCHAR2(255),
"USERPROFILEATTRS" NVARCHAR2(255),
)
The sample data from audit table looks like this:
EVENTDATEANDTIME = 18-AUG-10 07.33.01.000000000 PM
SERVERID = myserver1
EVENTNAME = AUTHN_SUCCESS
URL = xyz.mydomain.com%2Fportal%
OPERATION = GET
TARGETUSERDN = uid=markuser1,ou=people,o=
IPADDRESS = 10.8.146.134
USERPROFILEATTRS = uid=markuser1
EVENTDATEANDTIME = 19-AUG-10 05.30.09.000000000 PM
SERVERID = myserver2
EVENTNAME = AUTHN_SUCCESS
URL = xyz.mydomain.com%2FAPP1%2F
OPERATION = GET
TARGETUSERDN = uid=finuser1,ou=people,o=m
IPADDRESS = 10.8.146.130
USERPROFILEATTRS = uid=finuser1
Here is the sample user table information:
CREATE TABLE user
( userid varchar2(50) not null,
department_name varchar2(50) not null,
department_code number(6) not null,
)
The sample data from user table looks like this:
userid = markuser1
Department_name = Marketting
Department_code = 36
userid = finuser1
Department_name = Finance
Department_code = 12
Thanks,
Try this, it works for me. We can modify it if you need any modifications.
select department_name, department_code, day, sum(succ_count) success_count
from (
select u.department_name, u.department_code, to_char(eventdateandtime,' yy-mm-dd') Day,
case when EVENTNAME = 'AUTHN_SUCCESS' then 1 else 0 end succ_count
from user_tbl u, audit_tbl a
where u.userid = substr(USERPROFILEATTRS,5)
order by u.department_name, u.department_code, to_date(to_char(eventdatea ndtime,'yy -mm-dd'),' yy-mm-dd')
)
group by department_name, department_code, day ;
select department_name, department_code, day, sum(succ_count) success_count
from (
select u.department_name, u.department_code, to_char(eventdateandtime,'
case when EVENTNAME = 'AUTHN_SUCCESS' then 1 else 0 end succ_count
from user_tbl u, audit_tbl a
where u.userid = substr(USERPROFILEATTRS,5)
order by u.department_name, u.department_code, to_date(to_char(eventdatea
)
group by department_name, department_code, day ;
output screenshot
ee-d.doc
ee-d.doc
ASKER
Thank you nav_kum_v, really appreciate your help.
in terms of the more than 1 date for each department in your output, the answer is yes because I guess the idea is to do some analysis on how many people are using the applications from each department every day as oppose to calling help desk (for example).
I am going to try your query now and let you know the results.
Thanks again.
in terms of the more than 1 date for each department in your output, the answer is yes because I guess the idea is to do some analysis on how many people are using the applications from each department every day as oppose to calling help desk (for example).
I am going to try your query now and let you know the results.
Thanks again.
Fine then, the query i gave groups it by date as well. We can modify it further if it needs to take care of any other cases into account after your testing feedback.
ASKER
nav_kum_v - the query works on my system as well so thank you.
Now if I may ask, how do we get the stats based on per application. Right now we are getting all the authentication success i.e. AUTHN_SUCCESS regardless of the application being requested.
so to give it a try I modified your query a bit to get the stats on portal application only and get the results but how can I do this for all the applications.
here is the modified query;
select
department_name,
department_code, day,
sum(succ_count) Portal
from (
select u.department_name, u.department_code, to_char(eventdateandtime,' yy-mm-dd') Day,
case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%portal%' then 1 else 0 end succ_count
from user_tbl u, audit_tbl a
where u.userid = substr(USERPROFILEATTRS,5)
order by u.department_name, u.department_code, to_date(to_char(eventdatea ndtime,'yy -mm-dd'),' yy-mm-dd')
)
group by department_name, department_code, day
This modified query has given me an output of
DEPARTMENT_NAME DEPARTMENT_CODE DAY PORTAL
-------------------------- ---------- ---------- ---- ---------------------- -------- ----------------------
Finance 12 10-08-19 0
Marketting 36 10-08-18 1
Can you please help me again to modified such that we get per application like:
DEPARTMENT_NAME DEPARTMENT_CODE DAY PORTAL APP1 APP2
-------------------------- ---------- ---------- ---- ---------------------- -------- -------------------------- ---------- -----
Finance 16 YY-MM-DD 0 14 4
Marketting 36 YY-MM-DD 1 123 43
Thank you.
Now if I may ask, how do we get the stats based on per application. Right now we are getting all the authentication success i.e. AUTHN_SUCCESS regardless of the application being requested.
so to give it a try I modified your query a bit to get the stats on portal application only and get the results but how can I do this for all the applications.
here is the modified query;
select
department_name,
department_code, day,
sum(succ_count) Portal
from (
select u.department_name, u.department_code, to_char(eventdateandtime,'
case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%portal%' then 1 else 0 end succ_count
from user_tbl u, audit_tbl a
where u.userid = substr(USERPROFILEATTRS,5)
order by u.department_name, u.department_code, to_date(to_char(eventdatea
)
group by department_name, department_code, day
This modified query has given me an output of
DEPARTMENT_NAME DEPARTMENT_CODE DAY PORTAL
--------------------------
Finance 12 10-08-19 0
Marketting 36 10-08-18 1
Can you please help me again to modified such that we get per application like:
DEPARTMENT_NAME DEPARTMENT_CODE DAY PORTAL APP1 APP2
--------------------------
Finance 16 YY-MM-DD 0 14 4
Marketting 36 YY-MM-DD 1 123 43
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
nav_kum_v - the modified query works like a charm so thanks for your help on this. Much appreciated.
I been just informed that the Department name and Department code in production environment are on a different Database i.e. the audit table belongs to one DB and the user table belong to a different DB. I need to capture the department name and department code in the audit table itself. So from the application auditing configuration perspective it allows me to capture different user attributes under USERPROFILEATTRS column of the audit table such that the data is populated as
USERPROFILEATTRS = uid=markuser1 departmentname=Marketting Departmentcode=36
Note: the attributes are seprated with a space seperator in the database column.
Can we modified the SQL query now so that it retrieves data from one table and produce the same output?
I can create a new question now because I have asked enough help for the points allocated on this one. I would appreciate if you can help me on that one too. Please advice and I will create a new question.
Thanks again.
I been just informed that the Department name and Department code in production environment are on a different Database i.e. the audit table belongs to one DB and the user table belong to a different DB. I need to capture the department name and department code in the audit table itself. So from the application auditing configuration perspective it allows me to capture different user attributes under USERPROFILEATTRS column of the audit table such that the data is populated as
USERPROFILEATTRS = uid=markuser1 departmentname=Marketting Departmentcode=36
Note: the attributes are seprated with a space seperator in the database column.
Can we modified the SQL query now so that it retrieves data from one table and produce the same output?
I can create a new question now because I have asked enough help for the points allocated on this one. I would appreciate if you can help me on that one too. Please advice and I will create a new question.
Thanks again.
Yes do so. Please help to create a new question. Good you have considered my time. Thanks,
but do give a link to the new question here, so i can post the answer there which i already have. i do not post many things in one question as that creates confusion for the readers. Hope you can understand.
Thanks,
but do give a link to the new question here, so i can post the answer there which i already have. i do not post many things in one question as that creates confusion for the readers. Hope you can understand.
Thanks,
With a single table, we can get the output you wanted in many different ways. i mean we can come up with many queries which can produce the same output.
Since i do not know whether you are on oracle 10g and above, i did not go for using regular expressions to get the department and deparment id from this field "USERPROFILEATTRS" though it is very much possible.
I have used substr/instr which work in all oracle database versions. Try the below :
select
department_name,
department_code,
day1,
sum(succ_por_count) Portal,
sum(succ_app1_count) App1,
sum(succ_app2_count) App2
from (
select
substr(USERPROFILEATTRS,in str(userpr ofileattrs ,'=',1,2)+ 1,
instr(substr(USERPROFILEAT TRS,instr( userprofil eattrs,'=' ,1,2)+1),' ')-1) department_name,
substr(userprofileattrs,in str(userpr ofileattrs ,'=',-1)+1 ) department_code,
to_char(eventdateandtime,' yy-mm-dd') Day1,
case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%portal%' then 1 else 0 end succ_por_count,
case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%APP1%' then 1 else 0 end succ_app1_count,
case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%APP2%' then 1 else 0 end succ_app2_count
from audit_tbl
order by department_name, department_code, to_date(to_char(eventdatea ndtime,'yy -mm-dd'),' yy-mm-dd')
)
group by department_name, department_code, day1;
Since i do not know whether you are on oracle 10g and above, i did not go for using regular expressions to get the department and deparment id from this field "USERPROFILEATTRS" though it is very much possible.
I have used substr/instr which work in all oracle database versions. Try the below :
select
department_name,
department_code,
day1,
sum(succ_por_count) Portal,
sum(succ_app1_count) App1,
sum(succ_app2_count) App2
from (
select
substr(USERPROFILEATTRS,in
instr(substr(USERPROFILEAT
substr(userprofileattrs,in
to_char(eventdateandtime,'
case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%portal%' then 1 else 0 end succ_por_count,
case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%APP1%' then 1 else 0 end succ_app1_count,
case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%APP2%' then 1 else 0 end succ_app2_count
from audit_tbl
order by department_name, department_code, to_date(to_char(eventdatea
)
group by department_name, department_code, day1;
ok, here we go with another version. Try this as well and use which ever you like :
select
substr(USERPROFILEATTRS,in str(userpr ofileattrs ,'=',1,2)+ 1,
instr(substr(USERPROFILEAT TRS,instr( userprofil eattrs,'=' ,1,2)+1),' ')-1) department_name,
substr(userprofileattrs,in str(userpr ofileattrs ,'=',-1)+1 ) department_code,
to_char(eventdateandtime,' yy-mm-dd') Day1,
sum(case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%portal%' then 1 else 0 end ) succ_por_count,
sum(case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%APP1%' then 1 else 0 end ) succ_app1_count,
sum (case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%APP2%' then 1 else 0 end ) succ_app2_count
from audit_tbl
group by substr(USERPROFILEATTRS,in str(userpr ofileattrs ,'=',1,2)+ 1,
instr(substr(USERPROFILEAT TRS,instr( userprofil eattrs,'=' ,1,2)+1),' ')-1) ,
substr(userprofileattrs,in str(userpr ofileattrs ,'=',-1)+1 ),
to_char(eventdateandtime,' yy-mm-dd')
order by department_name, department_code,
to_date(to_char(eventdatea ndtime,'yy -mm-dd'),' yy-mm-dd') ;
Thanks
select
substr(USERPROFILEATTRS,in
instr(substr(USERPROFILEAT
substr(userprofileattrs,in
to_char(eventdateandtime,'
sum(case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%portal%' then 1 else 0 end ) succ_por_count,
sum(case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%APP1%' then 1 else 0 end ) succ_app1_count,
sum (case when EVENTNAME = 'AUTHN_SUCCESS' and url like '%APP2%' then 1 else 0 end ) succ_app2_count
from audit_tbl
group by substr(USERPROFILEATTRS,in
instr(substr(USERPROFILEAT
substr(userprofileattrs,in
to_char(eventdateandtime,'
order by department_name, department_code,
to_date(to_char(eventdatea
Thanks
ASKER
Nav_kum_v - I have created a new query at
https://www.experts-exchange.com/questions/26425329/Need-help-in-updating-a-SQL-query-to-retrieve-multiple-data-from-a-signle-column.html
I will update his one and awards the points so thanks again for being patient with me on this one.
The updated query has worked but if I may ask :) we need to modify a little bit to support all needs. The details are on the other link.
Thanks,
https://www.experts-exchange.com/questions/26425329/Need-help-in-updating-a-SQL-query-to-retrieve-multiple-data-from-a-signle-column.html
I will update his one and awards the points so thanks again for being patient with me on this one.
The updated query has worked but if I may ask :) we need to modify a little bit to support all needs. The details are on the other link.
Thanks,
ASKER
Nav_kum_v was excellent in helping out with this query and his solution works every time without any issues. Great job.
Department code Day Portal App1 App2
Marketting 36 10-08-18 134 54 89
Marketting 36 10-08-19 133 53 88
Finance 16 YY-MM-DD 3 5 3
Development 12 YY-MM-DD 3 76 0
I mean more than 1 date for each department in your output ?