Mathom Solutions Blog-IT

Splunk for Log Searching...

swjohnson.nospam@nospam.mathomsolutions.com (swjohnson) — Mon, 06 Jul 2009 04:39:00 -1300

It's been awhile but wanted to post something that I found very useful.

I encountered a situation where I need to parse through tens of thousands of log files for SQL Server and IIS and needed to do an analysis of what was contained within for a possible security incident and to create a standard auditing process for some compliance requirements for a new contract. I thought about pulling the logs to a secure location, creating a SQL Server database and importing the files and using full text search but then I came across Splunk. They have a nice little free version that allows you to index up to 500MB per day.

With Splunk, I was able to pull down the logs to a directory, point Splunk at it and within minutes, it was all indexed and it automatically did the rest as new logs were added. From there I was able to do research for XSS, SQL Injection, Login/Logout and failed/succesful information.

Very nice and fast! Check Splunk out as it has tons of possiblities besides log management!

Software justification/ROI...

swjohnson.nospam@nospam.mathomsolutions.com (swjohnson) — Sun, 05 Apr 2009 07:49:00 -1300

If you remeber from the previous post Speakng the Language of Managment, we needed a write-up to give to Senior Management to get our software approved. Here is a sample that I did for a small purchase for SQL Trace Analyzer that we wanted to better help manage, report and understand the profiler traces.

------------------------------

This is the software that we have been talking about and would like to purchase.

http://www.sqlsolutions.com/tadownload/SQLOnlinePayment/Step4.aspx?sSelected=15,1;36&sProducts=1&sLicenses=15,1

This software allows me to take the trace information that I gather from SQL and from the Performance Monitor on the SQL Server and map them over each other to give us more insight into the performance and troubleshooting of our SQL Servers.

Problem:

From time to time, the performance of the SQL Servers need to be investigated and, as such, I encounter three major problems:

Correctly, rapidly and accurately report/analyze SQL Profiler statistics for clients that are experiencing performance issues that are impacting our contracted SLA terms. I not only manage Product Y's systems but also the SQL Server systems for Product Z, Product X and Product B's ASP versions so I have to do this for various teams and clients with differing SLA's and often under a situation where the client is not happy. (Impacts multiple clients and SLA's)
Correctly and accurately report/analyze SQL Profiler statistics for Performance Reports that will be given out to clients who ask about this in their RFP and RFI process (this would be used in conjunction with a report from the Web load tool). (Impacts future business)
Correctly and accurately report/analyze SQL Profiler statistics for the ongoing tuning of new development applications (Impacts Accuracy and Responsiveness).

Current Process:

Currently, I have been using a free tool called ClearTrace that just aggregates the data. As well, I have also loaded the data into my own structures but this is quite time intensive and probably not the best use of my time to develop a new tool. Finally, I have even tried the Microsoft software called Database Tuning Advisor (DTA) which comes with SQL Server 2005.

Problems with Current Process:

The ClearTrace tool does not allow graphing or historical data so I can't see a before the change and after the change.
The ClearTrace tool has limitations in the number of profiler entries it can process. It can process about 3 million records and it crashes.
The ClearTrace tool is unsupported software.
The manual process is time consuming and not user friendly.
The time it takes to develop a new tool internally is time taken aware from customer focused tasks and is time not spent on the customer service hierarchy.
The DTA does not provide any statistics, rather it only provides recommendations. So I am not able to see how many time a process is called or a larger global perspective of the system, you just have to trust Microsoft. As well, the DTA does not work well for processes that use temp tables and when stored procedures call other stored procedures (nesting)—both of which are common with the Product Y's application.

Goals SQL Trace Analyzer Tool:

Provide internal teams with a timely list of prioritized tasks for optimization when a client is experiencing performance issues.
Provide internal teams with an easy to use step in the development process to be proactive and eliminate potential bottlenecks in future versions.
Create a performance capabilities document to justify sales claims and to provide to many large clients that our systems can handle large quantities of concurrent users without performance issues for their projects that have short timeframes.
Collect performance data to help better understand and plan capacity for growth.

Test Plan:

I downloaded a trial version of the software and ran the same profiler files through the software application. I was able to run a smaller sample of Project X's profiler output in about 30 minutes as compared to the ClearTrace run which took about 2 hours to process and manually graph.

ROI/Savings:

Software will cost $495 + annual maintenance of $49.

For the past several months I have done this formally three times for Client A (because of contract requirements) and once for Client S, and numerous times for Product B's ASP clients (listed the various clients but removed for confidentiality) for client performance relate issues and will be doing it formally in the next few months for approximately 10 additional clients. Finally, we will be adding a performance analysis step as part of the monthly Product Y's monthly software release.

The formal tests for Client A took 9 hours for the first two test and 6 hours for the last test and and based on the test case, should take about 2 hours with the new software. I would also estimate a time of about 5 hours for each of the upcoming formal tests. So just focusing on Client A, we have 24 total hours of time versus and estimate of 6 with the new software. Based on my hourly rate at $35 that would compute to a savings of around $630.00.

The one off client performance traces take anywhere from 3 to 6 hours depending on the severity and urgency and up to 2 hours on the high side with the new software. While the variability of time estimate for this scenario is difficult, it would follow that a similar reduction in hours as experienced with the formal tests.

The upcoming process change, I estimate it will take about 3-6 hours per run under the current process and this will be done monthly and about 1.5 hours each month with the new software. So again, we should see a similar reduction in the number of hours as experienced with the formal tests. Since we plan on doing monthly releases, the old process would take a total of 36 to 72 hours or cost about $1260 to $2520 and under the new process cost about $258 in time and generate a savings of $1000 to $2268.

While the total savings may not appear great in the grand scheme of things it has an ROI ranging from 329% to 585% in the first year. It is a savings and the more expensive resource can be spent on other more important tasks.

Thanks and let me know if you need any additional information/help in getting this processed.

--------------------

So there you have it. I start off with a short request, explain the business problem, explain the current process and the problems with it, set the goals that this purchase will accomplish, explain the test plan and finally show the savings from this.

Good luck and hope this helps you get that software you need!

Next up, I will show you a template that I have been using to do an indepth analysis for upgrading to SQL Server 2008.

Speaking the language of Management...

swjohnson.nospam@nospam.mathomsolutions.com (swjohnson) — Tue, 17 Mar 2009 00:39:00 -1300

One thing I have noticed recently with the economic crisis, is that the the money has stopped flowing. Well not exactly, the requests have just been more scruntinzed than before and more justification has to be given before it is approved. What I have seen then is more of us technical types have problems getting our projects funded because we don't speak MBA very well. Do you know what ROI, NPV, and Break Even Point mean? Management will throw these terms about like we throw atomicity, referential integrity and 3NF and assume everyeone knows what we are talking about.

So, that's the point of the next few articles. In this post, I will explain what they mean. In subsequent posts, I will give a small example of what I did to justify a small software purchase and then show what I am doing to justify the migration to SQL 2008 from SQL 2005.

Return on Investment (ROI) is a traditional financial measure that is used to evaluate the efficiency of an investement. In some cases, it can be used to compare the efficiency of a number of investments. Simply it is calculated by taking the benefit gained dividing by the cost of the investment and expressing that as a percentage.

ROI = (Gain from Investment - Cost of Investment) / Cost of Investment

This popular metric says that if the ROI is negative, the project should not be funded and if alternatives have a higher percentage, they should be chosen over other simialr projects with lower ROI's. Another words, they will choose the one with the highest return. As you can see, this is a fairly simple calculation but the difficulty is in the details. How do you calculate the "Gain from Investmet" or "Cost of Investment"? And that is where the manipulation can come into play. One person may add in additional cost or think of other gains that you don't include and that can explain why one person calculates on ROI and another calculates something different for the same project.

Net Present Value (NPV) is an indicator of how much value an investment or project adds to the company. It compares the value of a dollar (or what ever your currency you are working in) today to the value of that same dollar in the future, taking inflation and returns into account. If the NPV of a project is positive, it should be accepted. On the other hand, if it is negative, the project will probably be rejected because the associated cash flow will also be negative and most accounting types do not like that as Cash Flow is the lifeblood of a corporation (especially in tough economic times). To see the actual formula chek out the Wikipedia explanation.

Break Even Point (BEP) is the point at which cost or expenses equal revenue; there is no net loss or gain, and one has "broken even". Why is this important? From a management perspective, they want to know how long it will take before the project going to cover its costs and to start making money. From there, they can start to see the profitability horizion. Also, with the speed of technological change, you don't want to get too far out on your break even point. For more information please check out the Wikipedia explanation.

Finally as you start your analysis keep these tips in mind:

Talk in terms of company benefits. Don't go into detail about how a purchase will save you time and effort. Instead, focus on how it will save the company money. Talk to a few end-users to develop some figures as to how much money you can save.
Use the right financials and verifiable numbers. Too many requests talk about "payback," when management wants to know about NPV. They want to know this, because NPV helps them maintain cash flow--the lifeblood of the company. Requests that take guesses at the financial value of the proposed purchase often come out with compelling math as to the value of the purchase. Unfortunately, the numbers behind the math don't add up and that jeopardizes the whole proposal--and your credibility.
Identify, and push, hot buttons. What is most important to your managers and others who will approve or reject your request? While I don’t like acronyms or buzz words, it may help get your project the sight it needs.
Capitalize on trends. Know what is coming up in your company's operations, offerings, and other areas that may affect your request. To find out, just ask the key people in those areas. For example, you may find out if Project X is coming out with a new version. If so, then think of how the people involved in these things might need and what the benefits of the proposed purchase mean to them. Basically look around the entire company and see where this can benefit more than just you.
Write tight and in the active voice. In the body of your proposal eliminate the wordiness, repetition, adjectives, and fluff. The more succinct your writing, the more power it has and the more likely folks will read and understand it (they are just as busy as you). Using the active voice increases understanding and retention of the topic and it shows confidence. For more information see http://www.mindconnection.com/library/writing/activevoice.htm
Eliminate judgment statements. Present the information objectively and you imply the reader is intelligent enough to understand it. Making claims that something is "essential," "imperative," or "a no brainer," and you imply the reader is stupid. You want them on your side...don't alienate them.
Use the recommended format. If your company doesn't have a standard format, ask someone in accounting who handles these for a couple of funding requests that succeeded, plus suggestions on format. While you're at it, ask for any other advice that might be helpful--what you gain from his/her experience just might surprise you. Be thankful and show your team spirit and you will create a good ally.
Use an executive summary. This is a one-page cover sheet that provides a "thumbnail" of your proposal. It should tell the purpose, have a paragraph about the benefits, and give the key financial parameters (IRR and NPV). (NOTE: This is dependent on the size of the request. If you are asking for a $1000 piece of software, it may only need to be a paragraph or so.)

Next up is a simple example that I used to get a software request approved for SQL Trace Analyzer software.

SQL Server Anti Forensics...

swjohnson.nospam@nospam.mathomsolutions.com (swjohnson) — Thu, 19 Feb 2009 13:29:00 -1300

As I am sure you are all aware, database hacking is happening--afterall it's where the crown jewel--you data--are located. I came across this article today and it was very helpful in terms of how a hacker could get through a system with as little footprint as possible and how to think about protecting your systems.

Cesar Cerrudo is a Security Consultant and Lead Research for Application Security Inc and he presented recently at Black Hat 2009. His paper was on SQL Server Anti-Forensics: Techniques and Countermeasures. The paper talks about anti-forensics (which is about how a hacker covers/removes traces of their activity) and the counter measures you can take to help reduce or mitigate the chances of them reducing their tracks when they have invaded your system.

This is a must read and make sure to check out his other articles!

Free EBooks to help your learning...

swjohnson.nospam@nospam.mathomsolutions.com (swjohnson) — Sat, 07 Feb 2009 08:17:00 -1300

I ran across a couple of good links for free e-books about Microsoft SQL Server and figured I should share them.

Introducing SQL Server 2008 by Peter Debetta, Greg Low, and Mark Whitehorn. You can learn about the new features SQL Server 2008 including Security and Administration, Performance, Type System, Programability, Storage, High Availability, and Business Intelligence.

Brad's Sure Guide to SQL Server 2008 by Brad McGehee. This is about SQL Server 2008 and also includes two of his other books: How to become an exceptional DBA and DBA Best Practices.

Mastering SQL Server Profiler by Brad McGehee. As its name implies, learning all about SQL Server Profiler (2005) and how it can help you make your systems faster.

Dissecting SQL Server Execution Plans by Grant Fritchey. Hey, this is an awesome book that talks about how you can read the excution plans and make your system go faster....hey who wouldn't want that?

Hopefully you will find these helpful.

Also, if you know of any others, please let me know.