SQL queries for the EPM reports

When the EPM generates a report, it sends one or more SQL queries to the Experience Portal database and displays the result as a EPM page. This topic shows the:

• Application Detail report query

• Application Summary report queries

• Contact Detail report query

• Contact Summary report queries

• Performance report queries

• Session Detail report query

• Session Summary report queries

	Note:
	For all reports, user-entered dates and times are converted from the local EPM server timezone to the GMT timezone prior to performing the SQL query.

Application Detail report query

SELECT * FROM vpapplog
WHERE (LogTimestamp >= 'yyyy-mm-dd hh:mm:ss.0' AND LogTimestamp <= 'yyyy-mm-dd hh:mm:ss.0')
ORDER BY logtimestamp DESC, sessionindex DESC LIMIT 10000;

Application Summary report queries

SELECT {SummarizeByField}, count(*) as TotalCount FROM  vpapplog
WHERE (LogTimestamp >= 'yyyy-mm-dd hh:mm:ss.0' AND LogTimestamp <= 'yyyy-mm-dd hh:mm:ss.0')
GROUP BY {SummarizeByField} ORDER BY TotalCount DESC;
CREATE TEMP TABLE tmpADR AS
SELECT DISTINCT sessionid, {SummarizeByField} FROM vpapplog
WHERE (LogTimestamp >= 'yyyy-mm-dd hh:mm:ss.0' AND LogTimestamp <= 'yyyy-mm-dd hh:mm:ss.0')
GROUP BY by sessionid, {SummarizeByField};
+
SELECT {SummarizeByField}, count(*)as CallCount FROM tmpADR
GROUP BY {SummarizeByField} ORDER BY CallCount desc;

Where {SummarizeByField} can be any of the following:

• loglevel

• message

• activityname

• logtype

• varname & varvalue

Contact Detail report query

SELECT * FROM  CDR
WHERE  ( (CALLSTARTDATE > yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME >= hhmmss )) AND
 ( CALLSTARTDATE < yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME <= hhmmss)) )
ORDER BY CALLSTARTDATE ASC, CALLSTARTTIME ASC, RECORDID ASC LIMIT 10000;

Contact Summary report queries

SELECT Count(DURATION) as totalcalls, Sum(DURATION) as totalsum, ENDTYPE as etype FROM CDR
WHERE  ( (CALLSTARTDATE > yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME >= hhmmss )) AND 
( CALLSTARTDATE < yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME <= hhmmss)) )
GROUP BY ENDTYPE;
SELECT Count(DURATION) as totalcalls, Sum(DURATION) as totalsum, applicationname FROM CDR
	WHERE  ( (CALLSTARTDATE > yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME >= hhmmss )) AND
 ( CALLSTARTDATE < yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME <= hhmmss)) )
GROUP BY applicationname ORDER BY Count(DURATION) desc;
SELECT Count(*) as totalcalls, Sum(DURATION) as totalsum, MPP as mppcolumn FROM  CDR
WHERE  ( (CALLSTARTDATE > yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME >= hhmmss )) AND 
( CALLSTARTDATE < yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME <= hhmmss)) )
GROUP BY MPP ORDER BY COUNT(MPP) DESC;
SELECT Count(MPP) as totalcalls, Sum(DURATION) as totalsum, MPP as mppcolumn, PORTID as portidcolumn FROM  CDR
WHERE  ( (CALLSTARTDATE > yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME >= hhmmss )) AND 
( CALLSTARTDATE < yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME <= hhmmss)) )
GROUP BY MPP, PORTID ORDER BY COUNT(MPP) DESC;
SELECT callstartdate, callstarttime, recordid FROM  CDR
WHERE ( (CALLSTARTDATE > yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME >= hhmmss )) AND 
( CALLSTARTDATE < yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME <= hhmmss)) )
ORDER BY CALLSTARTDATE ASC, CALLSTARTTIME ASC, RECORDID ASC; 
SELECT duration/60 AS BUCKET, count(duration) AS BUCKETCOUNT FROM CDR
WHERE ( (CALLSTARTDATE > yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME >= hhmmss )) AND 
( CALLSTARTDATE < yyyymmdd OR (CALLSTARTDATE = yyyymmdd AND CALLSTARTTIME <= hhmmss)) )
GROUP BY duration/60 ORDER BY BUCKET DESC;

Performance report queries

SELECT Max(Peak) as peak, sum(sum) as sum, sum(count) as count, mpp, resourceid FROM  vpperformance
WHERE  (time >='yyyy-mm-dd hh:mm:ss.0' AND time < 'yyyy-mm-dd hh:mm:ss.0')
GROUP BY mpp, resourceid ORDER BY mpp;
SELECT peak/10 as util_buckets, sum(duration) as duration FROM vpperformance
where time >='yyyy-mm-dd hh:mm:ss.0' AND time < 'yyyy-mm-dd hh:mm:ss.0' AND resourceid = ‘PORT’
GROUP BY util_buckets ORDER BY util_buckets;

Session Detail report query

SELECT * FROM SDR
WHERE ( (STARTDATE > yyyymmdd OR (STARTDATE = yyyymmdd AND STARTTIME >= hhmmss )) AND 
( STARTDATE < yyyymmdd OR (STARTDATE = yyyymmdd AND STARTTIME <= hhmmss)) )
ORDER BY STARTDATE ASC, STARTTIME ASC, RECORDID ASC LIMIT 10000;

Session Summary report queries

SELECT count(recordid) as totalcalls, avg(timetillanswer) as timetillanswer, avg(latanswer) as latanswer, 
avg(duration) as duration, avg(uttcnttot) as uttcnttot, avg(uttcnttotrec) as uttcnttotrec, avg(pagereqcachehits) as pagereqcachehits, 
avg(pagereqtotal) as pagereqtotal FROM SDR
WHERE  ( (STARTDATE > yyyymmdd OR (STARTDATE = yyyymmdd AND STARTTIME >= hhmmss )) AND 
( STARTDATE < yyyymmdd OR (STARTDATE = yyyymmdd AND STARTTIME <= hhmmss)) );
SELECT latwpage as latwpage, latwpagename as latwpagename, mpp as mpp FROM SDR
WHERE  ( (STARTDATE > yyyymmdd OR (STARTDATE = yyyymmdd AND STARTTIME >= hhmmss )) AND
 ( STARTDATE < yyyymmdd OR (STARTDATE = yyyymmdd AND STARTTIME <= hhmmss)) )
ORDER BY latwpage desc;
SELECT uttcntwpage as uttcntwpage, uttcntrecwpage as uttcntrecwpage, uttwpagename, mpp as mpp, asrpercent FROM SDR
WHERE  ( (STARTDATE > yyyymmdd OR (STARTDATE = yyyymmdd AND STARTTIME >= hhmmss )) AND
 ( STARTDATE < yyyymmdd OR (STARTDATE = yyyymmdd AND STARTTIME <= hhmmss)) )
ORDER BY asrpercent asc; 
SELECT Count(*) as totalsessions, vpid, applicationname, terminationpagenameshort 
FROM SDR WHERE ( (STARTDATE > yyyymmdd OR (STARTDATE = yyyymmdd AND STARTTIME >= hhmmss )) AND 
( STARTDATE < yyyymmdd OR (STARTDATE = yyyymmdd AND STARTTIME <= hhmmss)) ) 
GROUP BY vpid, applicationname, terminationpagenameshort order by vpid, applicationname, totalsessions desc