Welcome!

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 www.fusebox.org or www.halhelms.com.

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
Software AG helps organizations transform into Digital Enterprises, so they can differentiate from competitors and better engage customers, partners and employees. Using the Software AG Suite, companies can close the gap between business and IT to create digital systems of differentiation that drive front-line agility. We offer four on-ramps to the Digital Enterprise: alignment through collaborative process analysis; transformation through portfolio management; agility through process automation and integration; and visibility through intelligent business operations and big data.
There will be 50 billion Internet connected devices by 2020. Today, every manufacturer has a propriety protocol and an app. How do we securely integrate these "things" into our lives and businesses in a way that we can easily control and manage? Even better, how do we integrate these "things" so that they control and manage each other so our lives become more convenient or our businesses become more profitable and/or safe? We have heard that the best interface is no interface. In his session at Internet of @ThingsExpo, Chris Matthieu, Co-Founder & CTO at Octoblu, Inc., will discuss how these devices generate enough data to learn our behaviors and simplify/improve our lives. What if we could connect everything to everything? I'm not only talking about connecting things to things but also systems, cloud services, and people. Add in a little machine learning and artificial intelligence and now we have something interesting...
Last week, while in San Francisco, I used the Uber app and service four times. All four experiences were great, although one of the drivers stopped for 30 seconds and then left as I was walking up to the car. He must have realized I was a blogger. None the less, the next car was just a minute away and I suffered no pain. In this article, my colleague, Ved Sen, Global Head, Advisory Services Social, Mobile and Sensors at Cognizant shares his experiences and insights.
We are reaching the end of the beginning with WebRTC and real systems using this technology have begun to appear. One challenge that faces every WebRTC deployment (in some form or another) is identity management. For example, if you have an existing service – possibly built on a variety of different PaaS/SaaS offerings – and you want to add real-time communications you are faced with a challenge relating to user management, authentication, authorization, and validation. Service providers will want to use their existing identities, but these will have credentials already that are (hopefully) irreversibly encoded. In his session at Internet of @ThingsExpo, Peter Dunkley, Technical Director at Acision, will look at how this identity problem can be solved and discuss ways to use existing web identities for real-time communication.
Can call centers hang up the phones for good? Intuitive Solutions did. WebRTC enabled this contact center provider to eliminate antiquated telephony and desktop phone infrastructure with a pure web-based solution, allowing them to expand beyond brick-and-mortar confines to a home-based agent model. It also ensured scalability and better service for customers, including MUY! Companies, one of the country's largest franchise restaurant companies with 232 Pizza Hut locations. This is one example of WebRTC adoption today, but the potential is limitless when powered by IoT. Attendees will learn real-world benefits of WebRTC and explore future possibilities, as WebRTC and IoT intersect to improve customer service.
From telemedicine to smart cars, digital homes and industrial monitoring, the explosive growth of IoT has created exciting new business opportunities for real time calls and messaging. In his session at Internet of @ThingsExpo, Ivelin Ivanov, CEO and Co-Founder of Telestax, will share some of the new revenue sources that IoT created for Restcomm – the open source telephony platform from Telestax. Ivelin Ivanov is a technology entrepreneur who founded Mobicents, an Open Source VoIP Platform, to help create, deploy, and manage applications integrating voice, video and data. He is the co-founder of TeleStax, an Open Source Cloud Communications company that helps the shift from legacy IN/SS7 telco networks to IP-based cloud comms. An early investor in multiple start-ups, he still finds time to code for his companies and contribute to open source projects.
The Internet of Things (IoT) promises to create new business models as significant as those that were inspired by the Internet and the smartphone 20 and 10 years ago. What business, social and practical implications will this phenomenon bring? That's the subject of "Monetizing the Internet of Things: Perspectives from the Front Lines," an e-book released today and available free of charge from Aria Systems, the leading innovator in recurring revenue management.
The Internet of Things will put IT to its ultimate test by creating infinite new opportunities to digitize products and services, generate and analyze new data to improve customer satisfaction, and discover new ways to gain a competitive advantage across nearly every industry. In order to help corporate business units to capitalize on the rapidly evolving IoT opportunities, IT must stand up to a new set of challenges.
There’s Big Data, then there’s really Big Data from the Internet of Things. IoT is evolving to include many data possibilities like new types of event, log and network data. The volumes are enormous, generating tens of billions of logs per day, which raise data challenges. Early IoT deployments are relying heavily on both the cloud and managed service providers to navigate these challenges. In her session at 6th Big Data Expo®, Hannah Smalltree, Director at Treasure Data, to discuss how IoT, Big Data and deployments are processing massive data volumes from wearables, utilities and other machines.
All major researchers estimate there will be tens of billions devices – computers, smartphones, tablets, and sensors – connected to the Internet by 2020. This number will continue to grow at a rapid pace for the next several decades. With major technology companies and startups seriously embracing IoT strategies, now is the perfect time to attend @ThingsExpo in Silicon Valley. Learn what is going on, contribute to the discussions, and ensure that your enterprise is as "IoT-Ready" as it can be!
P2P RTC will impact the landscape of communications, shifting from traditional telephony style communications models to OTT (Over-The-Top) cloud assisted & PaaS (Platform as a Service) communication services. The P2P shift will impact many areas of our lives, from mobile communication, human interactive web services, RTC and telephony infrastructure, user federation, security and privacy implications, business costs, and scalability. In his session at Internet of @ThingsExpo, Erik Lagerway, Co-founder of Hookflash, will walk through the shifting landscape of traditional telephone and voice services to the modern P2P RTC era of OTT cloud assisted services.
While great strides have been made relative to the video aspects of remote collaboration, audio technology has basically stagnated. Typically all audio is mixed to a single monaural stream and emanates from a single point, such as a speakerphone or a speaker associated with a video monitor. This leads to confusion and lack of understanding among participants especially regarding who is actually speaking. Spatial teleconferencing introduces the concept of acoustic spatial separation between conference participants in three dimensional space. This has been shown to significantly improve comprehension and conference efficiency.
The Internet of Things is tied together with a thin strand that is known as time. Coincidentally, at the core of nearly all data analytics is a timestamp. When working with time series data there are a few core principles that everyone should consider, especially across datasets where time is the common boundary. In his session at Internet of @ThingsExpo, Jim Scott, Director of Enterprise Strategy & Architecture at MapR Technologies, will discuss single-value, geo-spatial, and log time series data. By focusing on enterprise applications and the data center, he will use OpenTSDB as an example to explain some of these concepts including when to use different storage models.
SYS-CON Events announced today that Gridstore™, the leader in software-defined storage (SDS) purpose-built for Windows Servers and Hyper-V, will exhibit at SYS-CON's 15th International Cloud Expo®, which will take place on November 4–6, 2014, at the Santa Clara Convention Center in Santa Clara, CA. Gridstore™ is the leader in software-defined storage purpose built for virtualization that is designed to accelerate applications in virtualized environments. Using its patented Server-Side Virtual Controller™ Technology (SVCT) to eliminate the I/O blender effect and accelerate applications Gridstore delivers vmOptimized™ Storage that self-optimizes to each application or VM across both virtual and physical environments. Leveraging a grid architecture, Gridstore delivers the first end-to-end storage QoS to ensure the most important App or VM performance is never compromised. The storage grid, that uses Gridstore’s performance optimized nodes or capacity optimized nodes, starts with as few a...
The Transparent Cloud-computing Consortium (abbreviation: T-Cloud Consortium) will conduct research activities into changes in the computing model as a result of collaboration between "device" and "cloud" and the creation of new value and markets through organic data processing High speed and high quality networks, and dramatic improvements in computer processing capabilities, have greatly changed the nature of applications and made the storing and processing of data on the network commonplace. These technological reforms have not only changed computers and smartphones, but are also changing the data processing model for all information devices. In particular, in the area known as M2M (Machine-To-Machine), there are great expectations that information with a new type of value can be produced using a variety of devices and sensors saving/sharing data via the network and through large-scale cloud-type data processing. This consortium believes that attaching a huge number of devic...
Innodisk is a service-driven provider of industrial embedded flash and DRAM storage products and technologies, with a focus on the enterprise, industrial, aerospace, and defense industries. Innodisk is dedicated to serving their customers and business partners. Quality is vitally important when it comes to industrial embedded flash and DRAM storage products. That’s why Innodisk manufactures all of their products in their own purpose-built memory production facility. In fact, they designed and built their production center to maximize manufacturing efficiency and guarantee the highest quality of our products.
All major researchers estimate there will be tens of billions devices - computers, smartphones, tablets, and sensors - connected to the Internet by 2020. This number will continue to grow at a rapid pace for the next several decades. Over the summer Gartner released its much anticipated annual Hype Cycle report and the big news is that Internet of Things has now replaced Big Data as the most hyped technology. Indeed, we're hearing more and more about this fascinating new technological paradigm. Every other IT news item seems to be about IoT and its implications on the future of digital business.
Can call centers hang up the phones for good? Intuitive Solutions did. WebRTC enabled this contact center provider to eliminate antiquated telephony and desktop phone infrastructure with a pure web-based solution, allowing them to expand beyond brick-and-mortar confines to a home-based agent model. Download Slide Deck: ▸ Here
BSQUARE is a global leader of embedded software solutions. We enable smart connected systems at the device level and beyond that millions use every day and provide actionable data solutions for the growing Internet of Things (IoT) market. We empower our world-class customers with our products, services and solutions to achieve innovation and success. For more information, visit www.bsquare.com.
With the iCloud scandal seemingly in its past, Apple announced new iPhones, updates to iPad and MacBook as well as news on OSX Yosemite. Although consumers will have to wait to get their hands on some of that new stuff, what they can get is the latest release of iOS 8 that Apple made available for most in-market iPhones and iPads. Originally announced at WWDC (Apple’s annual developers conference) in June, iOS 8 seems to spearhead Apple’s newfound focus upon greater integration of their products into everyday tasks, cross-platform mobility and self-monitoring. Before you update your device, here is a look at some of the new features and things you may want to consider from a mobile security perspective.