Using RSS or Atom to keep an eye on your company's heartbeat
Here's a novel use for RSS / Atom: providing a feed of up-to-the-minute
information from your intranet database. This is so that right on your desktop you can see,
as an example, how many sales have been made today, or whatever is applicable to
wherever you work. Plus: this will score you some points with management if you set it up
so they can see the feed from their desktop, with any luck.
As a rule, people in power love statistics. It'll give them a way of 'keeping a finger on the pulse'.
First up, to keep you interested, here's the net result we're after:
[[posterous-content:nehIEHstqqEukzneAoEt]]
The idea is that every 5 minutes, this thing will refresh, giving you an up-to-date
snapshot of how business is going. Here are some ideas:
- Sales made
- Dollars in the till
- Progress on long-running tasks
- Number of hits on your website
- Number of outstanding support tickets
First up, you'll want to create a program that'll reach into your database, grab
some statistics, and generate RSS output. As a refresher, RSS output looks like this:
<rss version="2.0"
xmlns:dc="http://purl.org/dc/elements/1.1/">
<channel>
<title>My statistics feed</title>
<link>http://abc/</link>
<description>My statistics feed</description>
<item>
<title>Today's Orders: 523</title>
<link>http://abc</link>
<dc:date>2007-01-01T12:00:00</dc:date>
<description>Today's Orders: 523</description>
</item>
</channel>
</rss>I use a short C# console-mode application for this purpose. What it does is connect to the database, pull some statistics down, and spit them out in RSS format. Note that i'm displaying the time spent generating the statistics, and i'm using (nolock) in the SQL statements. This is so that i can quickly check that my queries aren't using too much of the SQL server's time, and so that i'm not locking the tables. You could write this application in any language, really.
using System;
using System.Collections;
using System.Text;
using System.IO;using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
class Rss
{
ArrayList arr;
public Rss()
{
arr = new ArrayList();
}
public void Add(string title)
{
arr.Add(title);
}
public void AddFirst(string title)
{
arr.Insert(0, title);
}
public string Render()
{
StringBuilder sb = new StringBuilder();
sb.AppendLine("<rss version=\"2.0\"" +
" xmlns:dc=\"http://purl.org/dc/elements/1.1/\">");
sb.AppendLine(" <channel>");
sb.AppendLine(" <title>My Statistics</title>");
sb.AppendLine(" <link>http://abc/</link>");
sb.AppendLine(" <description>My Stats</description>");
foreach (string s in arr)
{
sb.AppendLine (" <item>");
sb.AppendFormat(" <title>{0}</title>", s);
sb.AppendLine();
sb.AppendFormat(" <link>http://abc/</link>", DateTime.Now);
sb.AppendLine();
sb.AppendFormat(" <dc:date>{0:yyyy-MM-ddTHH:mm:ss}</dc:date>",
DateTime.Now);
sb.AppendLine();
sb.AppendFormat(" <description>{0}</description>", s);
sb.AppendLine();
sb.AppendLine(" </item>");
}
sb.AppendLine(" </channel>");
sb.AppendLine("</rss>");
return sb.ToString();
}
}
class DataPull
{
public Rss rss;
DateTime dttm;
SqlConnection conn;
public DataPull()
{
rss = new Rss();
}
public void Execute()
{
string connstring =
"Persist Security Info=False;uid=XXX;pwd=YYY;Initial Catalog=AAA;Server=BBB;";
conn = new SqlConnection(connstring);
conn.Open();
DateTime dttm_start = DateTime.Now;
string sql;
sql = @"select count(1) from sales(nolock)";
GetValue(sql,"Sales Made: {0}");
sql = @"select sum(value) from sales(nolock)";
GetValue(sql, "Sales Value: {0:c}");
DateTime dttm_end = DateTime.Now;
TimeSpan period = dttm_end-dttm_start;
rss.AddFirst(
String.Format("Details as at: {0:d/M/yyyy h:mm tt} ({1:0.0}s)",
dttm_start, period.Milliseconds / 1000.0));
conn.Close();
}
private void GetValue(string sql,string name)
{
rss.Add(String.Format(name, ExecuteScalar(sql)));
}
private object ExecuteScalar(string sql)
{
SqlCommand comm = new SqlCommand(sql, conn);
return comm.ExecuteScalar();
}
}
class Program
{
static void Main(string[] args)
{
DataPull dp = new DataPull();
dp.Execute();
Console.WriteLine(dp.rss.Render());
}
}Once you've got your application that grabs your statistics for you, you'll want
to schedule it to run every half an hour, and redirect the output to a spot on your
web server. What i did, was to create a folder on the web server called '/rss', and
redirect the output from this program to the index.htm in that folder.
To get you started, here is a two line batch file that i made to do this:
c:\rssgenerator\rssgenerate.exe > c:\webroot\rss\index.tmp copy /y c:\webroot\rss\index.tmp c:\webroot\rss\index.htm
And here is the command used to schedule this batch to run every 5 minutes:
schtasks /create /tn rss /ru system /sc minute /mo 5 /tr c:\rssgenerator\go.bat
Okay, you should now have an RSS feed being generated from your database every 5 minutes
with up-to-date statistics. Next you'll need an RSS reader, and just point it at your web server!
I personally install Yahoo widgets, and install the 'NewsStand' widget, because its simple, clean,
and can run in the background. Plus it looks good! Make sure to set it to update every 5 minutes. And you
may have to play with Yahoo widget's proxy settings to make it work in an intranet like my situation.
Now all this is done from the perspective of an intranet, using SQL server and IIS
on Windows servers, but the general ideas will all transfer to a Unix/GNULinux setup too
with minor changes.
Drop me a line (or comment) and let me know if this idea works for you!
Thanks for reading! And if you want to get in touch, I'd love to hear from you: chris.hulbert at gmail.
Chris Hulbert
(Comp Sci, Hons - UTS)
iOS Developer (Freelancer / Contractor) in Australia.
I have worked at places such as Google, Cochlear, Assembly Payments, News Corp, Fox Sports, NineMSN, FetchTV, Coles, Woolworths, Trust Bank, and Westpac, among others. If you're looking for help developing an iOS app, drop me a line!
Get in touch:
[email protected]
github.com/chrishulbert
linkedin
