Assign Root cause by subject

[ITfoam]

If, you are like me, and generate multiple tickets for new hires and terminations, then you know the pain when you close the ticket and have to set the root cause.

We wrote the following SQL query to set these values for us. Since we use SQL Express, I trigger this by a schedule task every hour using the SQLCMD.

I am going to add a count statement in later, which will exit, if there are no new ticket.

My new hire and termination tickets are always formatted the same, this is via a powershell script.
Termination: Associate - Application
New Hire: Associate - Application

My Root Cause Category is always New Hire / Termination
Application Category is the case statement

IF OBJECT_ID('tempdb..#myLStemp') IS NOT NULL
DROP TABLE #myLStemp

SELECT [T4].* INTO #myLStemp FROM
(
SELECT [lansweeperdb].[dbo].[htblticket].[ticketid],[subject]
FROM [lansweeperdb].[dbo].[htblticket] left join [lansweeperdb].[dbo].[htblticketcustomfield] on [lansweeperdb].[dbo].[htblticketcustomfield].ticketid = [lansweeperdb].[dbo].[htblticket].ticketid
WHERE ([subject] LIKE '%termination:%' OR [subject] LIKE '%new hire:%') and [lansweeperdb].[dbo].[htblticketcustomfield].ticketid is null

) AS [T4]


insert into [lansweeperdb].[dbo].[htblticketcustomfield]
(
ticketid,
fieldid,
data,
tickettypefieldid
)
select ticketid,'27','','32' from #myLStemp
union
select ticketid,'1','Yes','1' from #myLStemp
union
select ticketid,'2','New Hire / Termination','2' from #myLStemp
union
select ticketid,'204',
case when [subject] like '%Network Account' then 'Active Directory'
when [subject] like '%Badge' then 'Badge'
when [subject] like '%AX Account%' then 'AX Account'
when [subject] like '%Email' then 'Email'
when [subject] like '%PC%' then 'PC Setup'
when [subject] like '%Door Access' then 'Door System'
else 'Other'
end
,'47' from #myLStemp