SQL Views & Code
Friday, November 25, 2016 4:11 AM


This lesson explains how to add, access and use the SQL Ticket History View function in your SQL Server Management Studio.

Starting SQL Server Management Studio

 

Launch the SQL Server Management Studio, located in the Windows start menu.


Locating Your FocalScope SQL Server Instance


Once the SQL Management Studio starts, click on the dropdown list for the [Server name:] field and select [Browse for more...].



 

The "Browse for Servers" page opens, click on the [Network Servers] tab, expand the [Database Engine] node and select the SQL server instance you setup for FocalScope or where an existing database was created for FocalScope.


Logging in

 

The selected instance name will be in the [Server name:] field. Click [Connect] to continue. Select your login account which you set up for accessing this SQL server instance, in the [Authentication:] field and fill in the credentials (if prompted) to log in.


Executing a New Query

 

Expand the [Databases] node, select your FocalScope Database instance, right click it and select [New Query].


Calling a Specific Function


Enter the following code...

use focalscope_emm;
go
select top 128 * from dbo.vwTicketHistory;
go

The numeric value 128, after the select top code, sets the number for the amount of records you want the function to retrieve. Click [Execute] to run the function.

Please Note: "dbo.vwTicketHistory" in the above cases calls the code from the function with that name. If you enter another function name e.g. "dbo.vwEmailTicketRaw", that functions code will be called and executed. Data Output explanations & the Code for various functions are listed at the end of this lesson.




Once the code has finished executing, the results will be displayed in the middle/lower box of the screen.

Below follow Data Output explanations and the code for various functions, starting with "vwTicketHistory". Descriptions are affixed at the end of each line of code to explain what the code does and are marked in GREEN.


vwTicketHistory - Data Fields Explained + Code

vwTicketHistory provides access to the history of tickets, one row per event in the history.

To query most recent 128 records from the view run following SQL command:
select top 128 * from dbo.vwTicketHistory order by dtEvent desc;

Data Output:
List of fields in the view:
1. [Event] -- name of the event (see lookup table lkpTicketLog for full list of the events)
1.1. 'Closed'
1.2. 'Opened'
1.3. 'Assignment changed'
1.4. 'Put in ticket queue'

2. [EventCode] - numberic code of the event
3. [EventExplained] - same as [Event] but with "Assignment changed" event explained as
3.1. 'Assigned to'
3.2. 'Assignment timed out'
3.3. 'Assignment reset'

4. [dtEvent] -- date of the event, in Java date format, UTC
5. [EventTime] --date of the event as SQL server date
6. [EventOriginatorID] -- numeric ID of agent who has originated the event
7. [EventOriginatorName] -- name of agent who has originated the event
8. [AgentID] -- numeric ID of agent participating in the event (e.g. assigned to the ticket)
9. [AgentName] -- name of agent participating in the event (e.g. assigned to the ticket)
10. [TicketQueueID] - numeric ID of ticket queue involved in the event (e.g queue timed out)
11. [TicketQueueName] - name of ticket queue involved in the event (e.g queue timed out)
12. [GuidTicket] - GUID of the ticket
13. [IDTicket] - numeric ID of the ticket
14. [TicketSubject] - subject of the e-mail which has originated the ticket
15. [TicketNumber] - ticket number
16. [CurrentAssignedAgentName] - name of agent assigned to the ticket at the moment (snapshot)
17. [CurrentIDAssignedAgent] - numeric ID of agent assigned to the ticket at the moment (snapshot)

Code:
create view [vwTicketHistory]

with encryption

as

select

ln.sName as [Event] --name of event in the history

, l.nEvent as [EventCode] --numerical code of the event

, case

when l.nEvent = 3

then case

when a.sName is not null then 'Assigned to'

when isnull(l.nIdUser,0)=0 and l.nIdQueue is not null then 'Assignment timed out'

else 'Assignment reset'

end

else ln.sName

end as [EventExplained] --detailed explanation of the event

, l.dtStart as [dtEvent]-- event date in Java date format, UTC

, dbo.UnixTsToDt(l.dtStart) as [EventTime] --event date as SQL server date

, l.nIdUser as [EventOriginatorID] -- ID of the user who originated the event

, l.sUserName as [EventOriginatorName] -- name of the user who originated the event

, a.nId as [AgentID] -- ID of the agent involved into the event (e.g. assigned to the ticket)

, a.sName as [AgentName] -- Name of the agent involved into the event (e.g. assigned to the ticket)

, l.nIdQueue as [TicketQueueID] -- ID of a ticket queue connected with the event (e.g. queue timed out)

, l.sNameQueue as [TicketQueueName] -- name of a ticket queue connected with the event (e.g. queue timed out)

, t.[GuidTicket] -- global unique ID of the ticket

, t.[IDTicket] -- numerical ID of the ticket

, t.[TicketSubject] -- subject of the ticket

, t.[TicketNumber] -- ticket #

, t.[AssignedAgentName] as [CurrentAssignedAgentName] -- name of the agent assigned to the ticket at the time of query (present moment)

, t.[IDAssignedAgent] as [CurrentIDAssignedAgent] -- ID of the agent assigned to the ticket at the time of query (present moment)

from

vwTicketLog l

join lkpTicketLog ln on (ln.nId = l.nEvent)

join vwEmailTicketRaw t on (t.IDTicket = l.nIdThread)

left join tblUser a on (a.usId = l.usAgent)

go


vwEmailTicketRaw - Data Fields Explained + Code

vwEmailTicketRaw provides access to raw ticket & e-mail data, one row per e-mail.

To query most recent 128 records from the view run following SQL command:
select top 128 * from dbo.vwEmailTicketRaw order by dtEmailCreated desc;

Data Output:
List of fields in the view:
1. [EmailClassRaw] -- code of e-mail class (see next field EmailClass for details)
2. [EmailClass] --name of e-mail class
2.1. 'I' - incoming
2.2. 'O' - outgoing
2.3. 'POST' - post to folder
2.4. 'SMS' - SMS
2.5. 'AR' - auto reply
2.6. 'DRAFT' - drafg
2.7. 'O(ERR)' - outgoing with SMTP error

3. [TicketBaseStateRaw] -- ticket state code, see next field for details
4. [TicketBaseState] - ticket state name
4.1. 'Open'
4.2. 'Closed'
4.3. 'Deleted'

5. [TicketSubStateRaw] --ticket custom state (ID of list tab), -1 stands for [OnHold], see next field for details
6. [TicketSubState] --name of ticket custom state
6.1. 'On Hold'
6.2. ID of a custom list tab

7. [EmailImportanceRaw] --code of email importance, see next field for details
8. [EmailImportance] - name of email importance
8.1. 'Low'
8.2. 'High'
8.3. 'Normal'

9. fHasAttachements -- boolean, indicating whether e-mail has attachments
10. [EmailFollowupStatusRaw] --code of the follow up status, see next field for details
11. [EmailFollowupStatus]
11.1. 'purple'
11.2. 'orange'
11.3. 'green'
11.4. 'goldenrod'
11.5. 'blue'
11.6. 'darkred'

12. [TicketNumber] --ticket number
13. [AssignedAgentName] -- name of agent assigned to the ticket
14. [EmailSubject] -- e-mail subject
15. [EmailFromHeader] -- e-mail from field
16. [EmailToHeader] -- e-mail to field
17. [dtEmailSentDate] -- email sent date, in Java date format (milliseconds since 1 Jan 1970), UTC
18. [EmailSentDate] -- email sent date, in SQL server date format, UTC
19. [dtSentDateOfFirstEmailInTicket] -- sent date of first email in the ticket, in Java date format, UTC
20. [SentDateOfFirstEmailInTicket] --sent date of first email in the ticket as SQL server date
21. [dtSentDateOfRootEmailInTicket] --sent date of root email in the ticket, in Java date format, UTC
22. [TicketSubject] --subject of the message which started the ticket
23. [dtEmailCreated] --date when e-mail has been received, in Java date format, UTC
24. [EmailCreatedDate] --date when e-mail has been received,SQL server date
25. [dtTicketLastModified] --date when last e-mail in the has been received, in Java date format, UTC
26. [TicketLastModifiedDate] --date when last e-mail in the has been received as SQL server date
27. [dtLastSentDateInTicket] --date of last sent e-mail in the ticket Java date, UTC
28. [LastSentDateInTicket] --date of last sent e-mail in the ticket as SQL server date
29. [EmailGuidInTicket] - GUID of the e-mail
30. [EmailIdInTicket] - numeric ID of the e-mail in ticket
31. [ShortPathInTicket] - path in the ticket (for [By Thread] view)
32. [IDFolder] - numeric ID of the folder which e-mail belongs to
33. [GuidFolder] - GUID of the folder which e-mail belongs to
34. [IDEmail] - global numeric ID of the e-mail
35. [IDTicket] - global numeric ID of the ticket
36. [IDAssignedAgent] - numeric ID of agent assigned to the ticket
37. [ColorTagAssignedByRules] - color assigned to the e-mail by e-mail processing rules
38. [BeingRepliedBy] - name of agent replying to the e-mail
39. [SLAColor] - color assigned to the e-mail by SLA module
40. [dtTicketCreated] - date when ticket has been created, in Java date format, UTC
41. [TicketCreatedDate] - date when ticket has been created as SQL server date
42. [GuidEmail] - GUID of the email
43. [GuidParentEmail] - GUID of the parent email in ticket
44. [IDParentEmail] - numeric ID of the parent email in ticket
45. [PathInTicket] --materialized path inside ticket to implement [By Thread] view
46. [NoOfSubEmails] - number of e-mails linked to this one in the ticket
47. [GuidTicket] - GUID of the ticket
48. [LevelInTicket] - numeric value indicating depth in [By Thread] view

Code:
SET ANSI_NULLS ON

go

SET QUOTED_IDENTIFIER ON

go

SET ANSI_PADDING ON

go



IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwEmailTicketRaw]'))

begin

drop view [dbo].[vwEmailTicketRaw];

end;

go

create view [vwEmailTicketRaw]

with encryption

as

select

nCId as [EmailClassRaw] --see next field EmailClass for details

, (case nCId

when 2000 then 'I'

when 2001 then 'O'

when 2002 then 'POST'

when 2006 then 'SMS'

when 2007 then 'AR'

when 2004 then 'DRAFT'

when 2009 then 'O(ERR)'

else cast(nCId as varchar(8))

end) as [EmailClass]

, nTicketState as [TicketBaseStateRaw] --see next field for details

, (case

when nTicketState = 2000 then 'Open'

when nTicketState = 3000 then 'Closed'

when nTicketState<0 then 'Deleted'

end) as [TicketBaseState]

, nTcktSubState as [TicketSubStateRaw] --ticket custom state (tab), -1 stands for [OnHold], see next field for details

, (case nTcktSubState

when -1 then 'On Hold'

else cast(nTcktSubState as varchar(8))

end) as [TicketSubState]

, dbo.StrThreadStateRep(nTicketState, nTcktSubState) [TicketStateFull]

, nImportance as [EmailImportanceRaw] --see next field for details

, (case nImportance

when -1 then 'Low'

when 1 then 'High'

else 'Normal'

end) as [EmailImportance]

, fHasAttachements

, nFollowUp as [EmailFollowupStatusRaw] --see next field for details

, (case nFollowUp

when 1 then'purple'

when 2 then 'orange'

when 3 then 'green'

when 4 then 'goldenrod'

when 5 then 'blue'

when 6 then 'darkred'

end) as [EmailFollowupStatus]

, nTicketNo as [TicketNumber]

, sAgentName as [AssignedAgentName]

, sSubject as [EmailSubject]

, sbFrom as [EmailFromHeader]

, sbTo as [EmailToHeader]

, dtMsgorig as [dtEmailSentDate] --Java date format (milliseconds since 1 Jan 1970), UTC

, dbo.UnixTsToDt(dtMsgorig) as [EmailSentDate] --SQL server date, UTC

, dtThreadOrig as [dtSentDateOfFirstEmailInTicket] --Java date format, UTC

, dbo.UnixTsToDt(dtThreadOrig) as [SentDateOfFirstEmailInTicket] --SQL server date

, dtThreadRoot as [dtSentDateOfRootEmailInTicket] --Java date format, UTC

, sThreadSubject as [TicketSubject] --subject of the message which started the ticket

, dtMsgCreate as [dtEmailCreated] --Java date format, UTC

, dbo.UnixTsToDt(dtMsgCreate) as [EmailCreatedDate] --SQL server date

, dtThreadLA as [dtTicketLastModified]

, dbo.UnixTsToDt(dtThreadLA) as [TicketLastModifiedDate]

, dtThMsgCreate as [dtLastSentDateInTicket] --Java date, UTC

, dbo.UnixTsToDt(dtThMsgCreate) [LastSentDateInTicket] --Java date, UTC

, usLink [EmailGuidInTicket]

, nIdLink [EmailIdInTicket]

, sPathOrd [ShortPathInTicket]

, nIdFolder [IDFolder]

, usFolder as [GuidFolder]

, nIdEmail [IDEmail]

, nIdThread [IDTicket]

, nIdAgent [IDAssignedAgent]

, sColorTag [ColorTagAssignedByRules]

, sLockedBy [BeingRepliedBy]

, sSlaColor [SLAColor]

, dtThreadCreate [dtTicketCreated] --Java date, UTC

, dbo.UnixTsToDt(dtThreadCreate) as [TicketCreatedDate]

, usId as [GuidEmail]

, usParent as [GuidParentEmail]

, nIdParent [IDParentEmail]

, sPath as [PathInTicket] --materialized path inside ticket to implement [By Thread] view

, cChildren as [NoOfSubEmails]

, usThread as [GuidTicket]

, nLevel as [LevelInTicket]

from

vwConvThread with(NOLOCK, NOEXPAND)

go



IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwEmailTicketFolder]'))

begin

drop view [dbo].[vwEmailTicketFolder];

end;

go


vwEmailTicketFolder - Data Fields Explained + Code

vwEmailTicketFolder is the same as vwEmailTicketRaw above and only adds 2 new fields that explain where an e-mail resides, in the folder hierarchy.

To query most recent 128 records from the view run following SQL command:
select top 128 * from dbo.vwEmailTicketFolder order by dtEmailCreated desc;

Data Output:
List of fields in the view:
1. All fields belonging to vwEmailTicketRaw (see section above)
2. [EmailFolderName] - name of folder(ticketbox) which e-mail belongs to
3. [ParentFolderPath] - full path to the parent folder of the ticketbox which e-mail belongs to

Code:
create view [vwEmailTicketFolder]

with encryption

as

select

c.*

, dbo.FormatFolderName(f.sName, f.usProfile) as [EmailFolderName] --name of the folder

, dbo.FormatFolderName(h.sDispPath, h.usProfile) as [ParentFolderPath] --full path to to the parent folder

from

vwEmailTicketRaw c

join vwFolderActive f on (f.nId = c.[IDFolder])

left join vwFolderHier h on (h.nId = f.nIdParent)

go