I have begun an attempt to query HEAT for any useful statistics that could aid us in our mission. Since HEAT has a unique way of structuring its data in its semi-relational model it has been challenging to pull helpful information. One primary reason for this is that the important information we would want to know about (e.g. sites, problem/request details, etc…) are in free-text fields which cannot be queried easily. Another obstacle is that there seem to be a variety of methods currently (historically) used to categorize Web Site specific events. I spent a few hours this week getting to know the database structure and I should be able to provide some statistics (qualified) by early next week. If anyone has suggestions on the type of information/stats we could pull please send them my way. For now I will look for the obvious stats and we can drill into HEAT further as we discover more about HEAT’s dB and where the data is stored.
I know that all tickets with a Category “Web Site” and Call Type “1. http://www.middlebury.edu” have a field on their detail screen labeled “Full URL”. I don’t know what any of these fields are in the database, sadly. However if you could figure out what those fields were, you might be able to find a lot of the library related tickets by querying something like:
SELECT * FROM tickets WHERE Category = “Web Site” and Call_Type = “1. http://www.middlebury.edu” AND Full_URL LIKE ‘%academics/lis%’;
Again, I’m sure it’s way more complicated than that due to the “relational” structure of HEAT’s DB.
The Call Type posted above is wrong because WordPress added a “http://” in front of the www address when I submitted my comment! Also, it occurred to me that almost all of the ticket you’ll pull out with that call type will be issues specific to Microsoft Content Management Server, which might not be helpful for us.
Yes – you are correct. HEAT seems to have some unique ways it files its data in its database. I am attempting to create joins on these child tables to get more information from them. I am hoping I can get some real information from the system within the next few days or so. Thanks Ian.
I’d be happy to help with getting some data out of Heat. I’ve created reports against it in the past. I’ll be back on Monday. Let me know if you want some help.
The good news is that I am confident I know how to query HEAT, but the bad news is that it will take some time to get meaningful information from the data. In working on my part of the Annual Report I came up with this information from FY ’09:
HEAT ticket breakdown:
Category = Web Site Total = 790
Breakdown:
http://www.middlebury.edu 378
cat.middlebury.edu 28
Segue, MOTS, Concerto 81
community.middlebury.edu 16
go.middlebury.edu 25
Other Website Issues 160
Library Systems 48
Alumni Event Form 27
Student Group Website 6
There is much more I can find, but it is a time issue at the moment. I will continue early next week to see what I can find.
-jim