Verint SQL Queries

SQL Querys

USE CentralContact
SELECT 
start_time, 
end_time, 
dbs_id, 
audio_module_no, 
audio_ch_no,
extension,
dnis_code,
ani,
switch_id,
Switch_call_id
 
FROM dbo.Sessions 
 
WHERE 
 
start_time > GetDate()-1
AND SWITCH_ID IS NULL
 
ORDER BY extension
NO CTI SQL Queries
USE CentralContact
SELECT 
extension, COUNT(*) AS COUNT
 
FROM dbo.Sessions 
 
WHERE 
 
start_time > GetDate()-1
AND SWITCH_ID IS NULL
 
GROUP BY extension
ORDER BY COUNT DESC
USE CentralContact
SELECT 
extension, COUNT(*) AS COUNT
 
FROM dbo.Sessions 
 
WHERE 
 
start_time > GetDate()-30
AND Switch_call_id LIKE 'SIP%'
 
GROUP BY extension
ORDER BY COUNT DESC

Shows which calls are not tagged with an Agent Name

USE CentralContact
SELECT 
extension
 
FROM dbo.Sessions 
 
WHERE 
 
start_time > GetDate()-20
AND agent_id = '0'
AND extension LIKE '7%'
 
GROUP BY extension
ORDER BY extension

User <> Groups

USE CentralContact
SELECT     
Users.user_name, 
Users.role_id, 
Groups.group_name, 
Groups_to_users.group_id, 
Users.pbx_id, 
Users.winnt_login, 
Users.user_id
 
FROM         
Groups_to_users 
INNER JOIN
Groups ON Groups_to_users.group_id = Groups.group_id 
 
INNER JOIN
Users ON Groups_to_users.user_id = Users.user_id
USER <> specific ROLES
USE BPMAINDB
SELECT P.FIRSTNAME AS 'First Name', P.LASTNAME AS 'Last Name', B.USERNAME AS 'User Name', B.EMPLOYEEID AS 'Employee ID', A.NAME AS 'Role Name'
FROM AUTHACCESSRIGHT AA
INNER JOIN AUTHROLE A
ON AA.AUTHROLEID = A.ID
INNER JOIN BPUSER B
ON AA.BPUSERID = B.ID
INNER JOIN PERSON P
ON B.EMPLOYEEID = P.ID 
WHERE A.NAME = 'Basic Replay Role'
GROUP BY B.USERNAME, P.FIRSTNAME, P.LASTNAME, B.EMPLOYEEID, A.NAME
ORDER BY P.LASTNAME
USER <> All ROLES
USE BPMAINDB
SELECT P.FIRSTNAME AS 'First Name', P.LASTNAME AS 'Last Name', B.USERNAME AS 'User Name', B.EMPLOYEEID AS 'Employee ID', A.NAME AS 'Role Name'
FROM AUTHACCESSRIGHT AA
INNER JOIN AUTHROLE A
ON AA.AUTHROLEID = A.ID
INNER JOIN BPUSER B
ON AA.BPUSERID = B.ID
INNER JOIN PERSON P
ON B.EMPLOYEEID = P.ID 
--WHERE A.NAME = 'Basic Replay Role'
GROUP BY B.USERNAME, P.FIRSTNAME, P.LASTNAME, B.EMPLOYEEID, A.NAME
ORDER BY P.LASTNAME
USE BPMAINDB
SELECT P.firstname AS 'first name', p.lastname AS 'last name',
b.username AS 'username', b.employeeid AS 'employee id', a.name AS 'role name',
CASE eam.ISSUPERVISOR WHEN 1 THEN 'True' ELSE 'False' END AS 'Supervisor',
CASE eam.ISTEAMLEAD WHEN 1 THEN 'True' ELSE 'False' END AS 'TeamLead',
qmf.fromvalue AS 'Restriction From', qmf.tovalue AS 'Restriction To'
FROM AUTHACCESSRIGHT AA
INNER JOIN AUTHROLE a ON aa.AUTHROLEID = a.ID
INNER JOIN BPUSER b ON AA.BPUSERID = b.ID
INNER JOIN PERSON p ON b.EMPLOYEEID = p.ID
INNER JOIN EMPLOYEEAM eam ON p.ID = eam.id
INNER JOIN EMPLOYEEQMFILTER qmf ON p.ID = qmf.employeeid
--where a.name = 'Basic Replay Role'
GROUP BY b.USERNAME,p.firstname,p.LASTNAME,b.EMPLOYEEID,a.NAME,eam.ISSUPERVISOR,
eam.ISTEAMLEAD, qmf.FROMVALUE,qmf.TOVALUE

Script to collect login / logput events on Citrix servers

@echo off
 
:SORTDATE Variable
 
 
FOR /f "tokens=1-4 delims=/ " %%A IN ('date /t') DO (
SET nday=%%A
SET nmonth=%%B
SET nyear=%%C
)
SET SORTDATE=%nyear%%nmonth%%nday%
 
FOR /f "tokens=1-2 delims=: " %%A IN ('time /t') DO (
SET HOUR=%%A
SET MIN=%%B
)
 
SET SORTTIME=%hour%%min%
SET TIMESTAMP=%sortdate%-%sorttime%
 
 
:GatherLogs
 
SET outputfile=VerintUserLoginoutDetails-%sortdate%.txt
 
echo. ===== START OF log file FILTER - Capture Started at %date%-%time% ===== > %outputfile%
FOR /F %%i IN (CitrixServers.conf) DO (
echo Gathering Logs FROM %%i ...
findstr "sendLogonLogoffMessage" \\%%i\d$\CaptureService\Logs\captureservice\captureservice*.log >> %outputfile%
)
echo. ===== END OF log file FILTER - Capture Completed at %date%-%time% ===== >> %outputfile%
 
echo.
echo Finished!!!
pause
  • vendors/verint/sql.txt
  • Last modified: 2018/08/01 09:00
  • by gerardorourke