This is a followup to a prior post about CallManager Express and Splunk integration.
One of the really handy features in Splunk is the ability to run a search in CLI and save the output in various formats, like CSV and XML. I put together a small script and an XSLT file to transform the XML output of the scripted report into an HTML document with all the important information, for use in a dashboard page.
Here is an example piece of XML from the report to show the schema:
<searchResults timestamp="01/14/2009:11:12:37">
<command>QUERY GOES HERE|replace 5206 with SomeLine in dialed_number|fields + a_date,weekday,date_hour,date_month,date_mday,dialed_number ] | outputxml</command>
<results type="message">
</results>
<results type="unknown">
<cols>
<col cd="1" dc="32">a_date</col>
<col cd="3" dc="21">date_hour</col>
<col cd="5" dc="31">date_mday</col>
<col cd="4" dc="2">date_month</col>
<col cd="6" dc="9">dialed_number</col>
<col cd="2" dc="7">weekday</col>
</cols>
<r>
<m col="1" cd="625ec574">01/14/2009</m>
<m col="3" cd="5970c7">11</m>
<m col="5" cd="5970ca">14</m>
<m col="4" cd="cda985df">january</m>
<m col="6" cd="e13fb9d">Sales</m>
<m col="2" cd="67a9b049">wednesday</m>
</r>
---ETC---
So, you see that <cols> holds your column headings and <r>s are somewhat analoguous to rows in a CSV file.
Below is a smattering of chunks of the XSL file.
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html" />
<xsl:key name="distinct-date" match="m" use="." />
<!-- <xsl:variable name="currentDate"/> -->
<xsl:template match="/">
<HTML>
<HEAD>
<TITLE>30-Day Inbound Call Report</TITLE>
</HEAD>
<BODY>
<h1>30-Day Inbound Call Report</h1>
<h2>Data accurate as of: <br /><xsl:value-of select="/searchResults/@timestamp"/></h2>
<hr />
<h3>Total calls by day of week, for the last 30 days</h3>
<tr>
<td><b>Sunday</b></td>
<td><b>Monday</b></td>
<td><b>Tuesday</b></td>
<td><b>Wednesday</b></td>
<td><b>Thursday</b></td>
<td><b>Friday</b></td>
<td><b>Saturday</b></td>
</tr>
<tr>
<td><xsl:value-of select="count(/searchResults/results/r[m='sunday'])"/></td>
<td><xsl:value-of select="count(/searchResults/results/r[m='monday'])"/></td>
<td><xsl:value-of select="count(/searchResults/results/r[m='tuesday'])"/></td>
<td><xsl:value-of select="count(/searchResults/results/r[m='wednesday'])"/></td>
<td><xsl:value-of select="count(/searchResults/results/r[m='thursday'])"/></td>
<td><xsl:value-of select="count(/searchResults/results/r[m='friday'])"/></td>
<td><xsl:value-of select="count(/searchResults/results/r[m='saturday'])"/></td>
</tr>
</table>
<hr />
<h3>Calls by day, by inbound number</h3>
<div><div style="float:left">
<table style="border:1px solid #000000;font-size:small;">
<tr>
---CREATE YOUR COLUMN HEADINGS HERE---
</tr>
<xsl:for-each select="//m[1][generate-id()=generate-id(key('distinct-date',.))]">
<xsl:variable name="currentDate" select="." />
<xsl:variable name="currentDateId" select="@cd" />
<tr>
<td>
<xsl:value-of select="$currentDate" />
</td>
<td style="background-color:lightblue;">
<xsl:value-of select="count(/searchResults/results/r[m='LINE1' and m=$currentDate])" />
</td>
---YOU GET THE IDEA---
</tr>
</xsl:for-each>
<tr>
<td colspan="9" style="background-color:#000000;height:10px;"></td>
</tr>
<tr style="border-top:2px solid #000000;">
<td>
<b>30 Day Totals</b>
</td>
<td style="background-color:lightblue;">
<b><xsl:value-of select="count(/searchResults/results/r[m='LINE1'])"/></b>
</td>
---And So On---
<td style="background-color:lightyellow;">
<b><xsl:value-of select="count(/searchResults/results/r)"/></b>
</td>
</tr>
</table>
</div>
<div style="float:left;">
<table style="font-size:small;">
<tr>
<td>---</td>
</tr>
<tr>
<td><b>Yesterday's Numbers</b></td>
</tr>
</table>
</div>
</div>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>
You can use cron to schedule and something like XML Starlet to run the transform the XML to HTML using the XSLT.
