PC Availability inside MMU

MMU has a number of PC Drop-ins scattered throughout its five campuses where students use the computers for a variety of purposes. One problem students always have is finding a free computer, so we looked for a simple solution that displays how
many free pcs they would find in the various drop-ins; below is a screen shot from the myMMU student portal showing usage one Friday morning.

number of free pcs image

The success of our solution relied heavily on colleagues in Information Communications and Technology Services (ICTS) who needed to deploy the login and logout scripts and set the relevant group policy settings so that these scripts executed properly. LRT worked very closely with Matt Hicks from ICTS who identified the various polices that needed changing.

The availability count goes down every time a student logs into a computer and increases every time they logout, shutdown or power-down a PC. So while it will never be completely accurate, it does give students a good indication of which drop-in computer suite to head for.

The mechanics of the system are very simple; at is heart is a SQL database with three tables; usage, stu_services and PCHistory

Usage is the main table with the usage data and has just four columns

Inuse Yes or No
Location A short description of the room
Hostname The hostname of the PC
RID A unique room identifier

Stu_services is a table we use to feed our campusM mobile application and will be used for widgets we develop for the W2C project. It has five columns

RID A unique room identifier
Info Short description of Printers and scanners available
Category PC availability
Long longitude of the building containing the Drop-in
Lat latitude of the building containing the Drop-in

PCHistory contains snapshots of usage at points in the day and it has only three columns

Location A short description of the room
Free PCs The number of free PCs
Date Date and time of the snap shot

The computers in the Drop-ins run two small programs that update the usage table called Inuse.exe and Notinuse.exe. We query this table from a SharePoint WebPart to display the latest PC availability in our myMMU student portal, and publish the current PC availability using a REST web-service which is called by the campusM mobile application and will soon be consumed by our widgets and used to power large information screens.

The Programs
Both the inuse and notinuse are very small console programs that just update the sql database and the code is very simple

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Data.SqlClient;
  6. using System.Net;
  7.  
  8. namespace inuse
  9. {
  10. class Program
  11. {
  12. static void Main(string[] args)
  13. {
  14. // Get the hostname
  15.  
  16. string hostname = Environment.MachineName;
  17.  
  18. // Lets open the SQl connection
  19.  
  20. SqlConnection myConnection = new SqlConnection("user id=id;" +
  21. "password=password;server=sqlserver;" +
  22. "database=pcusage; " +
  23. "Trusted_Connection=False;" +
  24. "connection timeout=5");
  25. try
  26. {
  27. myConnection.Open();
  28. SqlCommand myCommand = new SqlCommand("UPDATE usage SET InUse = 'Yes' where hostname ='"+ hostname +"'",myConnection);
  29. myCommand.ExecuteNonQuery();
  30.  
  31. }
  32. catch (Exception e)
  33. {
  34. Console.WriteLine(e.ToString());
  35. }
  36.  
  37. try
  38. {
  39. myConnection.Close();
  40. }
  41. catch (Exception e)
  42. {
  43. Console.WriteLine(e.ToString());
  44. }
  45.  
  46. }
  47. }
  48. }

The code for not-in-use is same expect it set the inuse variable to No

The reasoning for two programs that do very similar things was driven by two over riding needs.
Minimise the effect on the log on process
Make the logic of deploying the applications easy to follow

The programs will time out if they can’t connect to the database within 5 seconds, it then does a very quick update and exits

Having two programs helped no end in the deployment and testing phase

How did we deploy it?

Matt Hicks from ICTS worked out the details of the deployment phase. MMU is currently running Novell but we are in the process of moving to AD. For our Zen based management we did the following: -
Both of the programs, induse.exe and notinuse.exe were stored on a drive on a fileserver that students had read access to. Matt created a Zen workstation policy for those computers we wanted to monitor and in that policy created four schedules
• Login
• Logout
• Shutdown
• Power off
For the login event the inuse program was run.
And for the other events the notinuse program was run.

When we move fully to AD we will have to change the mode of deployment, so any AD experts out there please leave us a comment to tell us how we can do the same…

How do we display the information?

We display the available PCs in two places, the first one is on the Student portal as a Sharepoint Webpart and the second we make use of a web service that talks to our SQL database and feeds our CampusM application.

Again the code for the web part is very simple we just talk to the SQL server and display the results as a label within the web part

  1. using System;
  2. using System.Runtime.InteropServices;
  3. using System.Web.UI;
  4. using System.Web.UI.WebControls;
  5. using System.Web.UI.WebControls.WebParts;
  6. using System.Xml.Serialization;
  7. using System.Data.SqlClient;
  8.  
  9. using Microsoft.SharePoint;
  10. using Microsoft.SharePoint.WebControls;
  11. using Microsoft.SharePoint.WebPartPages;
  12.  
  13. namespace freepcs
  14. {
  15. [Guid("")]
  16. public class freepcs : System.Web.UI.WebControls.WebParts.WebPart
  17. {
  18. public freepcs()
  19. {
  20. }
  21.  
  22. private string mailerror(string error)
  23. {
  24. // Build the email subject string
  25. System.Text.StringBuilder subject = new System.Text.StringBuilder();
  26. subject.Append("My.mmu.ac.uk Web part error report from web part freepcs ");
  27.  
  28. // Get the current user
  29. string whoami = System.Web.HttpContext.Current.User.Identity.Name;
  30.  
  31. // get the current date and time and convert to string to enable emailing
  32. DateTime timenow = DateTime.Now;
  33. String result = Convert.ToString(timenow);
  34.  
  35. // Build the email message string
  36. System.Text.StringBuilder message = new System.Text.StringBuilder();
  37. message.Append("User at the time of the error was : ");
  38. message.AppendLine(whoami);
  39. message.Append("Date and time of the problem was: ");
  40. message.AppendLine(result);
  41. message.Append("error message is : ");
  42. message.AppendLine(error);
  43.  
  44. // Construct the message header
  45. System.Collections.Specialized.StringDictionary messageHeader =
  46. new System.Collections.Specialized.StringDictionary();
  47. messageHeader.Add("to", "icts-help@mmu.ac.uk;mle-team@mmu.ac.uk");
  48. messageHeader.Add("from", "webpart error@mmu.ac.uk");
  49. messageHeader.Add("subject", subject.ToString());
  50. messageHeader.Add("content-type", "text/plain");
  51.  
  52. // Send the email
  53. Microsoft.SharePoint.Utilities.SPUtility.SendEmail(
  54. SPContext.Current.Web, messageHeader, message.ToString());
  55.  
  56. string mailsent = "true";
  57. return mailsent;
  58. }
  59.  
  60. protected override void CreateChildControls()
  61. {
  62. base.CreateChildControls();
  63. SqlConnection myConnection = new SqlConnection("user id=id;" +
  64. "password=password;server=servername" +
  65. "database=pcusage; " +
  66. "connection timeout=10");
  67. try
  68. {
  69. myConnection.Open();
  70. SqlDataReader myReader = null;
  71.  
  72. SqlCommand myCommand = new SqlCommand("select Location, Sum(free) as 'Free', Sum(pool) as 'Pool' FROM (select Location, count(*) as 'free', 0 As 'pool' from usage where InUse='NO' Group BY Location UNION select Location, 0 as 'free', count(*) as 'pool' from usage Group BY Location) status GROUP BY Location", myConnection);
  73.  
  74. myReader = myCommand.ExecuteReader();
  75.  
  76. //String results = "
  77.  
  78. *
  79. ";
  80. while (myReader.Read())
  81. {
  82. String location = myReader.GetString(0);
  83. int free = myReader.GetInt32(1);
  84. int pool = myReader.GetInt32(2);
  85. FreePCs.Text += "
  86.  
  87. o " + location + " has " + free + " free from " + pool + " machines.
  88.  
  89. ";
  90. }
  91. FreePCs.Text += "
  92.  
  93. ";
  94.  
  95. this.Controls.Add(FreePCs);
  96. }
  97. catch (Exception ex)
  98. {
  99. String mailsent = mailerror(ex.Message);
  100.  
  101. Label Error = new Label();
  102. Error.Text = " Sorry I can't talk to the database holding the information of free computers at the moment, please try later";
  103. this.Controls.Add(Error);
  104. }
  105. finally
  106. {
  107. myConnection.Close();
  108. }
  109.  
  110. }
  111. }
  112. }

As well as showing the usage via our portal we also use campusM on mobile devices. We feed their systems by a web service that talks to our SQL database and returns some xml that is used by campus

The code for that is straightforward as well
The interface section of the web service can be seen below

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Runtime.Serialization;
  5. using System.ServiceModel;
  6. using System.Text;
  7. using System.Xml;
  8. using System.Xml.Serialization;
  9. using System.ServiceModel.Web;
  10.  
  11. namespace WcfService4
  12. {
  13. // NOTE: If you change the interface name "IService1" here, you must also update the reference to "IService1" in Web.config.
  14. [ServiceContract]
  15. [XmlSerializerFormat]
  16. public interface IService1
  17. {
  18.  
  19. [OperationContract, WebGet(UriTemplate = "getPcAvailability", ResponseFormat = WebMessageFormat.Xml)]
  20. PcAvailability getPcAvailability();
  21. }
  22.  
  23. // Use a data contract as illustrated in the sample below to add composite types to service operations.
  24. [XmlRoot (Namespace = "http://web services server", ElementName="rooms")]
  25. public class PcAvailability
  26. {
  27. [DataMember(Name = "last-updated", Order = 1)]
  28. [XmlElement(ElementName="last-updated", Order=1)]
  29. public string lastUpdated { get; set; }
  30.  
  31. [DataMember(Order = 2)]
  32. [XmlElement(ElementName="room", Order=2)]
  33. public List rooms = new List();
  34. }
  35.  
  36. [DataContract (Name="room", Namespace = "http://web services server ")]
  37. public class Room
  38. {
  39. [DataMember (Order=1)]
  40. [XmlAttribute]
  41. public string free { get; set; }
  42.  
  43. [DataMember(Order = 2)]
  44. [XmlAttribute]
  45. public string seats { get; set; }
  46.  
  47. [DataMember(Order =3)]
  48. [XmlAttribute]
  49. public string location { get; set; }
  50.  
  51. [DataMember (Order =4)]
  52. [XmlAttribute]
  53. public string rid { get; set; }
  54.  
  55. [DataMember(Order =5)]
  56. [XmlAttribute]
  57. public string info { get; set; }
  58. }
  59. }
And the code that does the work is again very straightforward

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Runtime.Serialization;
  5. using System.ServiceModel;
  6. using System.Text;
  7. using System.Data.SqlClient;
  8.  
  9. namespace WcfService4
  10. {
  11. // NOTE: If you change the class name "Service1" here, you must also update the reference to "Service1" in Web.config and in the associated .svc file.
  12. public class Service1 : IService1
  13. {
  14. public PcAvailability getPcAvailability()
  15. {
  16. PcAvailability pcAvailabilty = new PcAvailability();
  17. DateTime dt = DateTime.Now;
  18. pcAvailabilty.lastUpdated = dt.ToShortTimeString() + " on " + dt.ToLongDateString();
  19.  
  20. SqlConnection myConnection = new SqlConnection("user id=ID;" +
  21. "password=password;server=SQL Server;" +
  22. "database=pcusage; " +
  23. "connection timeout=10");
  24. try
  25. {
  26. myConnection.Open();
  27. SqlDataReader myReader = null;
  28.  
  29. //SqlCommand myCommand = new SqlCommand("select Location, Sum(free) as 'Free', Sum(pool) as 'Pool' FROM (select Location, count(*) as 'free', 0 As 'pool' from usage where InUse='NO' Group BY Location UNION select Location, 0 as 'free', count(*) as 'pool' from usage Group BY Location) status GROUP BY Location", myConnection);
  30. SqlCommand myCommand = new SqlCommand("" +
  31. "SELECT rid " +
  32. ", location " +
  33. ", info " +
  34. ", SUM(free) as 'free' " +
  35. ", SUM(pool) as 'pool' " +
  36. "FROM " +
  37. "( " +
  38. "SELECT usage.rid as rid " +
  39. ", usage.Location as location " +
  40. ", stu_services.info as info " +
  41. ", count(*) as 'free' " +
  42. ", 0 as 'pool' " +
  43. "FROM usage " +
  44. "INNER JOIN stu_services " +
  45. "ON usage.rid = stu_services.rid " +
  46. "WHERE InUse='NO' " +
  47. "GROUP BY usage.rid " +
  48. ", usage.Location " +
  49. ", stu_services.info " +
  50. "UNION " +
  51. "SELECT usage.rid as rid " +
  52. ", usage.location as location " +
  53. ", stu_services.info as info " +
  54. ", 0 as 'free' " +
  55. ", count(*) as 'pool' " +
  56. "FROM usage " +
  57. "INNER JOIN stu_services " +
  58. "ON usage.rid = stu_services.rid " +
  59. "GROUP BY usage.rid " +
  60. ", usage.Location " +
  61. ", stu_services.info " +
  62. ") status " +
  63. "GROUP BY rid " +
  64. ", location " +
  65. ", info ", myConnection);
  66.  
  67. myReader = myCommand.ExecuteReader();
  68.  
  69. while (myReader.Read())
  70. {
  71. Room room = new Room();
  72. room.location = myReader["location"].ToString().Trim();
  73. room.free = myReader["free"].ToString();
  74. room.seats = myReader["pool"].ToString();
  75. room.rid = myReader["rid"].ToString().Trim();
  76. room.info = myReader["info"].ToString().Trim();
  77. pcAvailabilty.rooms.Add(room);
  78. }
  79. myReader.Close();
  80. myConnection.Close();
  81. }
  82. catch (Exception ex)
  83. {
  84. Room room = new Room();
  85. room.location = "Sorry, we currently have a problem showing PC availability";
  86. pcAvailabilty.rooms.Add(room);
  87. }
  88.  
  89. return pcAvailabilty;
  90. }
  91. }
  92. }
The webservice returns the following XML

<?xml version="1.0" encoding="utf-8" ?>
<rooms xmlns="http://apis.mmu.ac.uk" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<last-updated>11:57 on 15 October 2010</last-updated>
<room free="87" seats="149" location="MMUBS Drop-in" rid="a4.000" info="4 B&W & colour printers" />
<room free="43" seats="62" location="ART Drop-in" rid="C104" info="B&W Colour Printers" />
</rooms>

The format of the XML was first developed at Sheffield University in partnership with oMbiel and implemented the same way at MMU to enable us to use existing CampusM PC availability services

To make sure the data is reasonably accurate a SQL script is run at 3am to set all the PCs to not in use.

To allow our colleagues in ICTS to get a feel for how busy the drop-in computer suites are, a snap shot of the number of free PCs is taken every hour on the half hour and the results stored in the SQL table PCHistory. All the ICTS mangers have a spreadsheet that connects to this table allowing them to follow trends and identify under and over use of Drop-ins

This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

3 Responses to PC Availability inside MMU

  1. Sarah Davies says:

    Thanks very much for this really informative post! It’s such a nice example of a fairly simple but really useful service. Great example of working with IT services to deliver something that works for students, but also gives the university information that can feed into their admin and planning.

  2. Pingback: Web Services Update | JISC W2C Project Blog

  3. Pingback: Widget development | JISC W2C Project Blog

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>