In an earlier post we talked about using SQL Server queries against the underlying database to pull statistics out of Ilien SysAid. Today we’ll see a more complicated example.

First, we’ve linked SysAid to our Windows Active Directory data, so that SysAid knows our users and the departments to which they are assigned. It isn’t immediately obvious to me, but users are listed in the table sysaid_user, and departments are listed in the SysAid table cust_values, with a list_name of ‘departments’.

Now that I’ve captured that information in this blog post, maybe I’ll remember it the next time I need it.

We want a report of SysAid service requests that were requested by members of the accounting department. We want the list arranged so the most urgent issues are near the top, and the oldest issues within each urgency band are shown first.

Here’s the query:
[sql]
select
depts.value_caption as department,
id,
title,
case urgency
when 1 then ‘urgent’
when 2 then ‘very high’
when 3 then ‘high’
when 4 then ‘normal’
when 5 then ‘low’
else ‘?’
end as Urgency,
Responsibility,
Request_User,
datediff(day, insert_time, current_timestamp) as DaysOld
from service_req req
join sysaid_user usr
on usr.user_name=req.request_user
join
(
select *
from cust_values
where list_name = ‘departments’
) depts
on depts.value_key=usr.department
where status in (1,2,5,6)
and depts.value_caption=’Accounting’
order by
req.Urgency,
DaysOld desc
;
[/sql]
And here’s what the results look like:

Sample output

Sample output

I hope this is helpful to someone.

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available