Some of the most frequent arguments in IT are between developers and system administrators. Developers always blame shoddy network setup and malfunctioning equipment for program malfunctions. System administrators tend to
blame bad code for network errors and slow communication.
In this scenario, a programmer has developed an application for tracking the sales at multiple stores and reporting back to a central database. In an effort to save bandwidth during normal business hours, this is not a real-time application. Reporting data is accumulated throughout the day and is transmitted at night as a comma-separated value (CSV) file to be inserted into the central database.
This newly developed application is not functioning correctly. The files sent from the stores are being received by the server, but the data being inserted into the database is not correct. Sections are missing, data is in the wrong place, and some portions of the data are missing. Much to the dismay of the system administrator, the programmer blames the network for the issue. He is convinced that the files are becoming corrupted while in transit from the stores to the central data repository. Our goal is to prove him wrong.
Tapping into the Wire
In order to collect the data we need, we can capture packets at one of the stores or at the central office. Because the issue is affecting all of the stores, it would seem that if the issue is network-related, it would occur at the central
office—that is the only common thread among all stores.
The network switches support port mirroring, so we’ll mirror the port the server is plugged into and sniff its traffic. The traffic capture will be isolated to a single instance of a store uploading its CSV file to the collection server. This result is the capture file tickedoffdeveloper.pcap.
Analysis
We know nothing about the application the programmer has developed, other than the basic flow of information on the network. The capture file appears to start with some FTP traffic, so we’ll investigate that to see if it is indeed the mechanism that is transporting this file. This is a good place to examine the communication flow graph for a nice, clean summary of the communication that is occurring. To do so, switch to conversations tab and select FTP conversation, then double-click left button of mouse to show detail window of the conversation. Figure 8-37 shows the resulting graph.
Figure 8-37: The flow graph gives a quick view of the FTP communication.
Based on this flow graph, we see that a basic FTP connection is set up between 172.16.16.128 and 172.16.16.121 . Since 172.16.16.128 is initiating the connection , we can assume that it is the client, and that 172.16.16.121 is the server that compiles and processes the data. The flow graph confirms that this traffic is exclusively using the FTP protocol.
We know that some transfer of data should be happening , so we can use our knowledge of FTP to find the data transfer conversation. as shown in Figure 8-38
Figure 8-38: data transfer conversation.
Then select the conversation and show it’s detail windows, please see figure below:
Figure 8-40: The TCP detail shows what appears to be the data being transferred.
We’ve verified that data is being transferred, but we have yet to prove the programmer wrong. In order to do that, we need to show that the contents of the file are sound after traversing the network by extracting the transferred file from the captured packets.
The data appears because it is being transferred in plaintext over FTP,but we can’t be sure that the file is intact based on the stream alone, In order to reassemble the data so that we can extract it in its original format, Click left button of mouse and select Copy, next paste it to notepad and save it into a disk file.
The result of this save operation should be a CSV file that is an exact bytelevel copy of the file originally transferred from the store system. The file can be verified by comparing the MD5 hash of the original file with that of the
extracted file. The MD5 hashes should be the same, as shown in Figure 8-42.
Once the files are compared, we can prove that the network is not to blame for the database corruption occurring within the application. The file transferred from the store to the collection server is intact when it reaches the server, so any corruption must be occurring when the file is processed by the application.
Figure 8-42: The MD5 hashes of the original file and the extracted file are equivalent.
Lessons Learned
One great thing about packet-level analysis is that you don’t need to deal with the clutter of applications. Poorly coded applications greatly outnumber the good ones, but at the packet level, none of that matters. In this case, the
programmer was concerned about all of the mysterious components his application was dependent upon, but at the end of the day, his complicated data transfer that took hundreds of lines of code is still no more than FTP, TCP, and IP. Using what we know about these basic protocols, we were able to ensure the communication process flowed correctly and even extract files to prove the soundness of the network. It’s crucial to remember that no matter how complex the issue at hand, it’s still just packets.