The Poor Tech's Automated Phone Traffic Report

Jan 17, 2009

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>

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

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="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>
 <table>
 <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.