**************************************************************************************************************
**WARNING - I'M FAR FROM A REAL DBA. THIS WILL PROBABLY DO HORRIBLE THINGS IN THE FUTURE IF YOU REPEAT IT!!!**
**************************************************************************************************************
I had already looked into dispatch rules and they don't fit the bill for us as they require submission via email. We want our users submitting via the web so that we can capture better / more info at the time of submission.
I played with the database a bit and came up with the below chunk of code. It does a join of the users and ticket tables, gathers the users company info (which was auto populated from AD) and then assigns the tech manually. I then created a trigger on the ticket table that updates on insert. This allows us to get tickets assigned to the correct tech but circumvents notifications so the techs are going to have to check their tickets manually rather than monitor email. Not ideal but at least it will get us out of the mouse trap we're in now.
Update htblticket
set agentid = '15' /*update for different tech ids*/
FROM htblticket
Join htblusers htblusers1 On htblusers1.userid = htblticket.fromuserid
where htblticket.agentid IS NULL
And htblusers1.company like 'company name from AD' /*update for different companies*/
I'd love to hear about a better way to do if anyone has ideas....