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