I must admit that it’s been a while since I’ve posted anything in this blog. To be honest, it’s mainly because there’s been nothing remarkable to post about. Nothing anyone would benefit from knowing, really.
It’s even to the point that my role involves much more than just programming these days – recently I have been involved in reviewing web statistics packages in the hope of landing on a solution. Well, the solution is found in SmarterStats – quite a remarkable web statistics package that does most things. It even has an API.
About that API – it has a largely undocumented ‘Query’ webservice. It is not enabled by default, but once enabled it allows you to query the statistics data via a web service.
To enable the service, you will need to create an authorisation key. A good one to use is a UUID. Then, you need to define that key in [SmarterStats_Root]\MRS\App_Data\Config\AppConfig.xml:
<LocalHostDeleted>false</LocalHostDeleted>
++ <WebServiceAuthorizationCode>nnnnnnnn-nnnn-nnnn-nnnnnnnnnnnnnnnn</WebServiceAuthorizationCode>
<ExpirationNotification />
Then you can access the service via http://localhost:9999/Services/Query.asmx, WSDL path http://localhost:9999/Services/Query.asmx?wsdl
The query service uses some form of psuedo-sql that runs a query against what looks like a function result, ie. SELECT * FROM fTableName(siteId, dateFrom, dateTo, maxItems, extraParam**) WHERE blah=blah ORDER BY blah.
** Parameters maxItems and extraParam are undocumented and were found by chance. MaxItems is pretty obvious, it’s the number of records to retrieve. Use extraParam to filter certain queries by page. Only certain queries support the extra parameter so try them out.
A list of tables is available in the file [SmarterStats_Root]\MRS\App_Data\Config\ReportConfig.xml – you can copy this to your development directory and then use it to generate a list of available reports.
Here are some example reports you can call using the web service (assume site id = 1):
- Get Daily Site Traffic
SELECT * FROM fActivityTotalTrend(1, '2012-01-01T00:00:00', '2012-01-27T00:00:00', 50)
- Get Daily traffic to home page
SELECT * FROM fDailyActivityForFile(1, '2012-01-01T00:00:00', '2012-01-27T00:00:00', 20, '/')
- All pages, ordered by popularity
SELECT * FROM fTopPages(1, '2012-01-01T00:00:00', '2012-01-27T00:00:00')
- Top 10 most popular pages
SELECT * FROM fTopPages(1, '2012-01-01T00:00:00', '2012-01-27T00:00:00', 10)
- Web browsers
SELECT * FROM fBrowsers(1, '2012-01-01T00:00:00', '2012-01-27T00:00:00')
- Visitors by City
SELECT * FROM fGeographicCountryByCity(1, '2012-01-01T00:00:00', '2012-01-27T00:00:00')
- Visitors by City, to a certain URL
SELECT * FROM fGeographicsByFile(1, '2012-01-01T00:00:00', '2012-01-27T00:00:00', 20, '/some/url/')
Calling the web service from coldfusion is pretty easy:
<cfsetting enablecfoutputonly="true">
<!--- 'static' service parameters --->
<cfset strUsername = "username">
<cfset strPassword = "password">
<cfset strAuthCode = "nnnnnnnn-nnnn-nnnn-nnnnnnnnnnnnnnnn">
<cfset strWebServiceUrl = "http://127.0.0.1:9999/Services/Query.asmx?wsdl">
<!--- initialise web service --->
<cfset objWebService = createObject("webservice", strWebServiceUrl)>
<!--- query parameters (you can pass these in from a form) --->
<cfset strReport = "fTopPages">
<cfset numSiteId = 1>
<cfset dateFrom = createDate(year(now()), month(now()), 1)>
<cfset dateTo = createDate(year(now()), month(now()), day(now()))>
<cfset numRows = 20>
<!--- compose query --->
<cfset strQuery = "SELECT * FROM #strReport#(#numSiteId#, '#dateformat(dateFrom,'yyyy-mm-dd')#T00:00:00', '#dateformat(dateTo,'yyyy-mm-dd')#T00:00:00')">
<!--- execute query --->
<cfset dsResult = objWebservice.executeQuery(strUsername, strPassword, strAuthCode, numSiteId, strQuery, numRows)>
<!--- convert to query --->
<cfset qryResult = datasetToQuery(dsResult, 'Table1')><!--- version 7: it is 'Table1', version 6: it is 'results' --->
<!--- output result --->
<cfdump var="#qryResult#">
<!--- FUNCTION TO CONVERT .NET DATASET TO QUERY (added here for convenience - move to helper class) --->
<cffunction name="datasetToQuery" access="public" returntype="query" output="false">
<cfargument type="any" name="dataset" required="true">
<cfargument type="string" name="tablename" required="true">
<!--- dataset has 2 nodes: 1) Column definitions 2) Data --->
<cfset var qryResult = "">
<cfset var lstColumns = "">
<cfset var lstTypes = "">
<cfset var aryDataset = ARGUMENTS.dataset.get_any()>
<cfset var aryColumns = XmlSearch(aryDataset[1].getAsString(), "/xs:schema/xs:element[@name='#ARGUMENTS.tablename#']/xs:complexType/xs:sequence/xs:element")>
<cfset var aryData = XmlSearch(aryDataset[2].getAsString(), "/diffgr:diffgram/NewDataSet/#ARGUMENTS.tablename#")>
<cfset var i = 0>
<cfset var c = 0>
<!--- get columns --->
<cfloop from="1" to="#arrayLen(aryColumns)#" index="i">
<cfset lstColumns = listAppend(lstColumns, aryColumns[i].xmlAttributes.name)>
<cfswitch expression="#aryColumns[i].xmlAttributes.type#">
<cfcase value="xs:double,xs:long">
<cfset lstTypes = listAppend(lstTypes, 'double')>
</cfcase>
<cfcase value="xs:date">
<cfset lstTypes = listAppend(lstTypes, 'timestamp')>
</cfcase>
<cfdefaultcase>
<cfset lstTypes = listAppend(lstTypes, 'varchar')>
</cfdefaultcase>
</cfswitch>
</cfloop>
<!--- create query object --->
<cfset qryResult = queryNew(lstColumns, lstTypes)>
<!--- populate query --->
<cfloop from="1" to="#arrayLen(aryData)#" index="i">
<cfset queryAddRow(qryResult)>
<cfloop from="1" to="#arrayLen(aryData[i].xmlChildren)#" index="c">
<cfset querySetCell(qryResult, aryData[i].xmlChildren[c].xmlName, aryData[i].xmlChildren[c].xmlText)>
</cfloop>
</cfloop>
<cfreturn qryResult>
</cffunction>
<cfsetting enablecfoutputonly="false">
All this was tested in SmarterStats 7.
