The Poor Tech's Automated Phone Traffic Report

Automating inbound call reporting with Splunk and XML/XSL

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 type="unknown">
         <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>

         <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>

So, you see that holds your column headings and 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="" version="1.0">
   <xsl:output method="html" />

   <xsl:key name="distinct-date" match="m" use="." />

<!-- <xsl:variable name="currentDate"/> -->
   <xsl:template match="/">
            <TITLE>30-Day Inbound Call Report</TITLE>


 <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>
 <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>
 <hr />
 <h3>Calls by day, by inbound number</h3>
            <div><div style="float:left">
            <table style="border:1px solid #000000;font-size:small;">
                  ---CREATE YOUR COLUMN HEADINGS HERE---

               <xsl:for-each select="//m[1][generate-id()=generate-id(key('distinct-date',.))]">
                  <xsl:variable name="currentDate" select="." />

                  <xsl:variable name="currentDateId" select="@cd" />

                        <xsl:value-of select="$currentDate" />

                     <td style="background-color:lightblue;">
                        <xsl:value-of select="count(/searchResults/results/r[m='LINE1' and m=$currentDate])" />

                     ---YOU GET THE IDEA---

               <td colspan="9" style="background-color:#000000;height:10px;"></td>
               <tr style="border-top:2px solid #000000;">
                   <b>30 Day Totals</b>
                  <td style="background-color:lightblue;">
                   <b><xsl:value-of select="count(/searchResults/results/r[m='LINE1'])"/></b>
                  ---And So On---

                  <td style="background-color:lightyellow;">
                   <b><xsl:value-of select="count(/searchResults/results/r)"/></b>

 <div style="float:left;">
 <table style="font-size:small;">
 <td><b>Yesterday's Numbers</b></td>


You can use cron to schedule and something like XML Starlet to run the transform the XML to HTML using the XSLT.