Click here to close now.


You will be redirected in 30 seconds or close now.

ColdFusion Authors: Yakov Fain, Maureen O'Gara, Nancy Y. Nee, Tad Anderson, Daniel Kaar

Related Topics: ColdFusion

ColdFusion: Article

Using Querysims to Analyze Log Files

Using Querysims to Analyze Log Files

Query simulations, or querysims, are a means of simulating returned records from a database when no database exists. This article explores a method of using the <cf_querysim> tag to create an easy approach to custom log file processing.

Querysims 101
The <cf_querysim> custom tag was developed by Hal Helms as a tool to make development of Fusebox applications less linear. The idea was to disconnect the front-end CFML development from the back-end database and query development. To do this, <cf_querysim> provides a way to generate ColdFusion recordsets without querying a database. Instead, lines of text data are converted into a recordset.

As an example, let's imagine we're building a site that needs to display a list of employees. We need to retrieve each employee's first and last names, employee identification number, department, and supervisor ID number from the database. A <cfquery> to satisfy this requirement is shown in Listing 1. All the listings in this article have Fusedoc blocks at the top to document the function of the template. More information on Fusedoc can be found at or

Listing 2 details dspEmployees.cfm, a template that produces a table based on the data returned by qryGetEmployees.cfm.

To tie the two together, we include them in a calling template, exampleOne.cfm, shown in Listing 3. This technique separates the back-end data portion of the code from the front-end display portion, another idea used extensively in Fusebox.

The end result, produced by running exampleOne.cfm, is shown in Figure 1. This is familiar territory for most Cold-Fusion developers. The twist comes when we want to develop and test the display component of this example before the database exists. This allows us to continue development regardless of whether there's a database yet.

To accomplish this goal, we need a way to make qryGetEmployees.cfm produce output just as though the database was done. This is where the querysim custom tag comes in. Listing 4 shows a version of qryGetEmployees.cfm that creates a querysim of desired data. The first line inside the <cf_querysim> tag defines the name of the recordset that will be produced, the second line specifies field names, and the remaining lines specify the data.

When we run exampleOne.cfm using the new querysim, the output looks exactly the same as it did in Figure 1. The querysim has taken away the need for the database.

Common Uses for Querysims
As shown in the previous examples, querysims were developed to allow developers to get on with the work of creating an application's front end without having a complete database on hand. This means that the project's participants can work in parallel, reducing the calendar time required to build the application. ColdFusion coders can work on their side of the application, supported by querysims to represent live data, while database developers work independently on the back end. As query files are written, using SQL, they're put in the application in place of the querysims that stand in their stead.

Querysims can be useful in other ways as well. For example, most of us have had to build a form to be used to add and edit data. When adding a record, we need a blank form. When editing a record, we need the form to populate with data from the database. One typical solution to this problem is to create conditional logic for each input on the form, populating the input with data if a record is available, otherwise leaving the input without a value.

Querysims make this task much more manageable. We start with the idea that a form is always in edit mode. The only difference between creating a new record and editing an existing record is that, in the case of creating, we're really editing a record with all blank fields. So we create a single piece of conditional logic at the top of the form that checks to see if we're editing a record. If not, we create a recordset using <cf_querysim>. This recordset has one record with all blank fields. This way, the code that displays the record's values for editing won't throw an error for a creation action - the recordset always exists, regardless of whether we're editing an existing record or creating a new one. Listing 5 shows a simple example of this technique.

Notice that there is no conditional logic inside the form in Listing 5. All the work is done by the querysim. Regardless of whether we're creating or editing a user, we always deal with a recordset, so there's no need for cluttered conditional logic.

Parallel development and form manipulation are powerful uses of querysims, but something came up that led me to explore more ways to take advantage of them.

The Problem
Now that we've had a quick tour of querysims, I'll get into the subject problem for this article. I recently had a request to create a project status page for one of my clients. The request was to provide daily status reports on the project using a Web page.

The restrictions on creating such a page were interesting, though. The client asked that it be quick, easy, cheap, and attractive. Quick means "Don't spend much of my money putting it together," easy means "Don't spend much of my money by making it time-consuming to update," cheap means "Don't spend much of my money," and attractive means "You're not allowed to shove a plain text page at me."

For a bunch of developers, this should be an easy request. After all, everyone on my team can write HTML, so it would be an easy matter to pop up a page of HTML and let everyone edit it daily to add their progress notes. We certainly could have gone this way, but this particular client has a habit of changing his mind, particularly where layout-related things are concerned. So I fully expected him to change his mind at some point about how he wanted these daily updates presented. That, combined with my ingrained Fusebox thinking that tells me to separate data from process and presentation, led me to consider something different.

The Solution: Idea One
The approach was simply to create a query file with a querysim in it to contain the daily update log. The querysim would present the log data for a display file to render for the user. With this approach, if the presentation requirements changed, we could just change the display file. In addition, we'd be able to use the same query file as input to a variety of displays, just in case things got interesting.

The query file I worked up is shown in Listing 6. I refer to this as "Idea One" as it became the foundation for more ideas in the same vein.

Listing 7 shows the display file I used to process the log, and Figure 2 shows the log displayed in a browser, again using a calling file (ideaOne.cfm) to pull together the query and display files.

Left at this point, the solution might have been fine. However, the ways of Fusebox, once learned, aren't easily ignored. Having developers editing the log data right in the querysim definition made me a little nervous. Everyone on the project knew better than to mess around with the CFML and to simply edit the data inside the <cf_querysim> tag, but on the off chance that someone would slip a finger and accidentally delete the starting bracket on the </cf_querysim> closing tag, I decided I needed to keep the data somewhere other than embedded directly in the <cf_querysim> tag. Enter Idea Two.

The Solution: Idea Two
Probably the simplest part of the solution, Idea Two represents the true power of this approach. The idea is simple: separate the data from the <cf_querysim> tag through the use of <cfinclude>. Using this idea, the qryWorkLog.cfm file became two files. The first is qryWorkLog2.cfm, which is just qryWorkLog.cfm with a small modification to remove the data and replace it with a <cfinclude> tag. The second is WorkLog.txt, which contains the data removed from qryWorkLog.cfm. These two files are shown in Listings 8 and 9.

The end result is the same output as shown in Figure 2. Nothing has really changed about the data or how it's presented. On the back end, though, we now have a standalone text file that can be edited without fear of breaking the querysim code.

Having implemented this solution, I looked at WorkLog.txt and realized it was nothing more than a simple log file, much like those generated by Web servers. That realization led me back to some discussions from various listservs and newsgroups about Web statistics packages and parsing server logs. It occurred to me that the use of querysims represented an easy way to import a server log into a CF recordset for further processing. And so we go on to The Next Idea.

The Next Idea: Server Logs to Recordsets
The records in a querysim data file are pipe-delimited. That is, each field is separated from the next by a vertical pipe (or bar) character. Most server logs simply have spaces between fields, making them problematic to parse efficiently. In order to use the querysim tag, I'd have to take one of two approaches. I could either modify the querysim tag to parse the server log, or I could modify the server log to comply with the querysim tag's requirements. Because spaces aren't particularly good delimiters to begin with, I decided on the latter approach.

Fortunately, I do most of my work on servers that run Apache, so modifying the server log was really very simple. I went into the Apache configuration file, httpd.conf, and added the following line along with the other LogFormat lines:

LogFormat "%h|%l|%u|%t|\"%r\"|%>s|%b" pipedcommon

This defines a new log format called "pipedcommon", which is identical to the common server log format except that it uses pipes instead of spaces between fields. I then modified the CustomLog directive to use this new log format:

CustomLog logs/access.log pipedcommon

A quick restart of Apache and it was ready to go. Every request to the server causes a line to be written to the access log, so I made a few page requests to add lines to a new log file, creating the file in Listing 10.

Then I took a copy of the log file over to my ColdFusion test directory, where I had a new file waiting for it. This file, qryWebLog.cfm, is shown in Listing 11. It's identical in concept to the qryWorkLog2.cfm file seen in Listing 8, but the querysim has a different name and the field headings are altered to match the format of the server's access log. In addition, I've added a <cfdump> tag to the end of the file to quickly show that the server log has indeed been processed into a recordset.

For live use I created a display file (dspWebLog.cfm) and a calling file (nextIdea.cfm), similar to the examples shown earlier, to create an attractive display of the access log's data. These files are shown in Listings 12 and 13, and the output from running nextIdea.cfm is shown in Figure 3.

As you can see, the Web log is neatly displayed in the browser window, in just the format I specified. This is the launching point for whatever sort of log analysis you might wish to perform. Particularly with ColdFusion's query-of-query capability, you could do just about any sort of analysis you might want on this recordset.

Other Applications for Querysims
As you think about querysims, of course, more and more uses for them become apparent. You can take advantage of <cf_querysim> any time you might want to convert text data into a recordset without worrying about a custom parser.

For example, you might want to create a bulk loader for text data. With <cf_querysim> loading the data into a recordset for you, loading the data into a database becomes a simple matter of looping over the recordset with a <cfquery> to insert the data. No doubt your imagination will be able to come up with its own uses for this extraordinarily useful custom tag.

More Stories By Jeff Peters

Jeff Peters works for Open Source Data Integration Software company XAware.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

@ThingsExpo Stories
Electric power utilities face relentless pressure on their financial performance, and reducing distribution grid losses is one of the last untapped opportunities to meet their business goals. Combining IoT-enabled sensors and cloud-based data analytics, utilities now are able to find, quantify and reduce losses faster – and with a smaller IT footprint. Solutions exist using Internet-enabled sensors deployed temporarily at strategic locations within the distribution grid to measure actual line loads.
The Internet of Everything is re-shaping technology trends–moving away from “request/response” architecture to an “always-on” Streaming Web where data is in constant motion and secure, reliable communication is an absolute necessity. As more and more THINGS go online, the challenges that developers will need to address will only increase exponentially. In his session at @ThingsExpo, Todd Greene, Founder & CEO of PubNub, will explore the current state of IoT connectivity and review key trends and technology requirements that will drive the Internet of Things from hype to reality.
The Internet of Things (IoT) is growing rapidly by extending current technologies, products and networks. By 2020, Cisco estimates there will be 50 billion connected devices. Gartner has forecast revenues of over $300 billion, just to IoT suppliers. Now is the time to figure out how you’ll make money – not just create innovative products. With hundreds of new products and companies jumping into the IoT fray every month, there’s no shortage of innovation. Despite this, McKinsey/VisionMobile data shows "less than 10 percent of IoT developers are making enough to support a reasonably sized team....
Today air travel is a minefield of delays, hassles and customer disappointment. Airlines struggle to revitalize the experience. GE and M2Mi will demonstrate practical examples of how IoT solutions are helping airlines bring back personalization, reduce trip time and improve reliability. In their session at @ThingsExpo, Shyam Varan Nath, Principal Architect with GE, and Dr. Sarah Cooper, M2Mi's VP Business Development and Engineering, will explore the IoT cloud-based platform technologies driving this change including privacy controls, data transparency and integration of real time context w...
SYS-CON Events announced today that IBM Cloud Data Services has been named “Bronze Sponsor” of SYS-CON's 17th Cloud Expo, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. IBM Cloud Data Services offers a portfolio of integrated, best-of-breed cloud data services for developers focused on mobile computing and analytics use cases.
There will be 20 billion IoT devices connected to the Internet soon. What if we could control these devices with our voice, mind, or gestures? What if we could teach these devices how to talk to each other? What if these devices could learn how to interact with us (and each other) to make our lives better? What if Jarvis was real? How can I gain these super powers? In his session at 17th Cloud Expo, Chris Matthieu, co-founder and CTO of Octoblu, will show you!
Developing software for the Internet of Things (IoT) comes with its own set of challenges. Security, privacy, and unified standards are a few key issues. In addition, each IoT product is comprised of at least three separate application components: the software embedded in the device, the backend big-data service, and the mobile application for the end user's controls. Each component is developed by a different team, using different technologies and practices, and deployed to a different stack/target - this makes the integration of these separate pipelines and the coordination of software upd...
As a company adopts a DevOps approach to software development, what are key things that both the Dev and Ops side of the business must keep in mind to ensure effective continuous delivery? In his session at DevOps Summit, Mark Hydar, Head of DevOps, Ericsson TV Platforms, will share best practices and provide helpful tips for Ops teams to adopt an open line of communication with the development side of the house to ensure success between the two sides.
Mobile messaging has been a popular communication channel for more than 20 years. Finnish engineer Matti Makkonen invented the idea for SMS (Short Message Service) in 1984, making his vision a reality on December 3, 1992 by sending the first message ("Happy Christmas") from a PC to a cell phone. Since then, the technology has evolved immensely, from both a technology standpoint, and in our everyday uses for it. Originally used for person-to-person (P2P) communication, i.e., Sally sends a text message to Betty – mobile messaging now offers tremendous value to businesses for customer and empl...
The IoT market is on track to hit $7.1 trillion in 2020. The reality is that only a handful of companies are ready for this massive demand. There are a lot of barriers, paint points, traps, and hidden roadblocks. How can we deal with these issues and challenges? The paradigm has changed. Old-style ad-hoc trial-and-error ways will certainly lead you to the dead end. What is mandatory is an overarching and adaptive approach to effectively handle the rapid changes and exponential growth.
SYS-CON Events announced today that Sandy Carter, IBM General Manager Cloud Ecosystem and Developers, and a Social Business Evangelist, will keynote at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA.
The IoT is upon us, but today’s databases, built on 30-year-old math, require multiple platforms to create a single solution. Data demands of the IoT require Big Data systems that can handle ingest, transactions and analytics concurrently adapting to varied situations as they occur, with speed at scale. In his session at @ThingsExpo, Chad Jones, chief strategy officer at Deep Information Sciences, will look differently at IoT data so enterprises can fully leverage their IoT potential. He’ll share tips on how to speed up business initiatives, harness Big Data and remain one step ahead by apply...
WebRTC converts the entire network into a ubiquitous communications cloud thereby connecting anytime, anywhere through any point. In his session at WebRTC Summit,, Mark Castleman, EIR at Bell Labs and Head of Future X Labs, will discuss how the transformational nature of communications is achieved through the democratizing force of WebRTC. WebRTC is doing for voice what HTML did for web content.
"Matrix is an ambitious open standard and implementation that's set up to break down the fragmentation problems that exist in IP messaging and VoIP communication," explained John Woolf, Technical Evangelist at Matrix, in this interview at @ThingsExpo, held Nov 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA.
WebRTC has had a real tough three or four years, and so have those working with it. Only a few short years ago, the development world were excited about WebRTC and proclaiming how awesome it was. You might have played with the technology a couple of years ago, only to find the extra infrastructure requirements were painful to implement and poorly documented. This probably left a bitter taste in your mouth, especially when things went wrong.
The broad selection of hardware, the rapid evolution of operating systems and the time-to-market for mobile apps has been so rapid that new challenges for developers and engineers arise every day. Security, testing, hosting, and other metrics have to be considered through the process. In his session at Big Data Expo, Walter Maguire, Chief Field Technologist, HP Big Data Group, at Hewlett-Packard, will discuss the challenges faced by developers and a composite Big Data applications builder, focusing on how to help solve the problems that developers are continuously battling.
Nowadays, a large number of sensors and devices are connected to the network. Leading-edge IoT technologies integrate various types of sensor data to create a new value for several business decision scenarios. The transparent cloud is a model of a new IoT emergence service platform. Many service providers store and access various types of sensor data in order to create and find out new business values by integrating such data.
There are so many tools and techniques for data analytics that even for a data scientist the choices, possible systems, and even the types of data can be daunting. In his session at @ThingsExpo, Chris Harrold, Global CTO for Big Data Solutions for EMC Corporation, will show how to perform a simple, but meaningful analysis of social sentiment data using freely available tools that take only minutes to download and install. Participants will get the download information, scripts, and complete end-to-end walkthrough of the analysis from start to finish. Participants will also be given the pract...
WebRTC: together these advances have created a perfect storm of technologies that are disrupting and transforming classic communications models and ecosystems. In his session at WebRTC Summit, Cary Bran, VP of Innovation and New Ventures at Plantronics and PLT Labs, will provide an overview of this technological shift, including associated business and consumer communications impacts, and opportunities it may enable, complement or entirely transform.
SYS-CON Events announced today that Dyn, the worldwide leader in Internet Performance, will exhibit at SYS-CON's 17th International Cloud Expo®, which will take place on November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. Dyn is a cloud-based Internet Performance company. Dyn helps companies monitor, control, and optimize online infrastructure for an exceptional end-user experience. Through a world-class network and unrivaled, objective intelligence into Internet conditions, Dyn ensures traffic gets delivered faster, safer, and more reliably than ever.