Archive for February, 2009

Splunk to Jira for PCI

Wednesday, February 25th, 2009

PCI DSS 10.6 requires that logs are reviewed on a daily basis. If the proper alerting mechanisms are in place you can bypass the need to look through thousands of lines of logs, but you still need a trail of what actions are taken after an alert is received.

PCI DSS 10.6

10.6
Are logs for all system components reviewed at least daily? Log reviews must include those servers that perform security functions like intrusion detection system (IDS) and authentication, authorization, and accounting protocol (AAA) servers (for example, RADIUS). Note: Log harvesting, parsing, and alerting tools may be used to achieve compliance with Requirement 10.6.

I am using XMLRPC and a perl script to automatically create issues in Jira when a Splunk alert is triggered. Although you can use Splunk to perform log review by tagging events I thought it would be more beneficial to use our currently installed work flow system so that we don’t have to allow everyone to log into Splunk and we could use some of the other features inside of Jira as well. So what do you need to do to make this work?

1. Install necessary CPAN modules (assuming you already have perl) on your splunk server.


#cpan -e
cpan> install XMLRPC::Lite
...
cpan> install Data::Dumper
...

1.b Enable XMLRPC in Jira under administration -> Plugins -> RPC Plugin.

splunkjirapci1

2. Copy the following script in $SPLUNK_HOME/bin/scripts/openJiraTicket.pl


#!/usr/bin/perl
#
# * Your search can trigger a shell script.
# * Specify the name of the shell script to run.
# * Place the script in $SPLUNK_HOME/bin/scripts.
# * Command line arguments passed to the script are:
# * $0 = script name.
# * $1 = number of events returned.
# * $2 = search terms.
# * $3 = fully qualified query string.
# * $4 = name of saved splunk.
# * $5 = trigger reason (i.e. "The number of events was greater than 1").
# * $6 = link to saved search.
# * $7 = a list of tags belonging to this saved search.
# * $8 = file where the results for this search are stored (contains raw results).
use strict;
use warnings;
#
use XMLRPC::Lite;
use Data::Dumper;
#
my $summary = $ARGV[3];
my $description = "An alert was triggered in splunk with the following information:" . "\n\nSearch String: " . $ARGV[1] . "\nNumber of Results:" . $ARGV[0] . "\nAlert Name:" . $ARGV[3] . "\nReason Alarm Triggered:" . $ARGV[4] . "\n\nLink to Search:" . $ARGV[5];
#
my $jira = XMLRPC::Lite->proxy('http://jira.domain.com:8080/rpc/xmlrpc');
my $auth = $jira->call("jira1.login", "username", "password")->result();
my $call = $jira->call("jira1.createIssue", $auth, {
'project' => 'AI',
'type' => 30,
'summary' => $summary,
'description' => $description,
'assignee' => 'usertoassignto',
});
my $fault = $call->fault();
if (defined $fault) {
die $call->faultstring();
} else {
print "issue created:\n";
print Dumper($call->result());
}
$jira->call("jira1.logout", $auth);

3. Add a new input to your savedsearches.conf file in $SPLUNK_HOME/etc/system/local/savedsearches.conf. Note the action_script is set to openJiraTicket.pl which means whenever the relation field is met the script openJiraTicket.pl will be executed.


[PCIDSS-10_3_4-LinuxFailedLogins]
action_email = linux.admins@domain.com
action_rss = 0
action_script = openJiraTicket.pl
counttype = number of events
enableSched = 1
quantity = 0
relation = greater than
role = productionLinux
schedule = 0 * * * *
search = index=production pam_unix failure startminutesago=60
sendresults = 1
userid = myuser

4. Now create a few failed login attempts via ssh to a server that is logging to Splunk and you should see an issue get opened in Jira by Splunk.

splunkjirapci2

Some other enhancements that can be made to this setup are:

1. Creating an event inside of Splunk itself when the issue is opened in Jira. Then log when the Jira ticket is closed to Splunk as well and measure the variance in time of the opening and closing of issues as a KPI of the operations staff.

2. Extend the functionality of the perl script with an array (or maybe use sqlite) to match the alert name in Splunk to a component in Jira. This would allow you to compartmentalize the alerts into groups in Jira (i.e windows in one component, linux in another, network in another, etc).

3. Use encrypted password in the perl script and use XMLRPC over ssl. I will be doing this shortly. A quick howto

Encrypt your password file

openssl rand 32 -out key.temp
openssl des3 -e -pass file:key.temp -in passwd -out passwd.enc
openssl des3 -d pass file:key.dec -in passwd.enc -out passwd.dec
openssl des3 -d -pass file:key.dec -in passwd.enc -out passwd.dec
openssl des3 -d -pass file:key.temp -in passwd.enc -out passwd.dec

Unencrypt your password from within the script using the key. I learned chomp is VERY important ;)

if ($enpasswd) {
$passwd = `openssl des3 -d -pass file:/etc/pki/tls/private/dbi.key -in $enpasswd |awk NF`;
chomp($passwd);
}

Tags: , , , , , ,
Posted in Linux, Scripting, Technology | No Comments »

Lasagna

Saturday, February 21st, 2009

A not so long and not so short lasagna recipe that I’ve made several times. The ingredient list is below. You can use fresh chopped herbs for a more natural flavor or dried if you want to save time and money.


1 lb ground beef
6 oz ground lean pork
3/4 cup chopped onion
1 clove garlic, minced
16 oz diced tomatoes
16 oz tomato sauce
2 tsp parsley flakes
2 tbsp sugar
1 tsp salt
1 tsp basil leaves
12 oz creamed cottage cheese
1/4 cup parmesan cheese
1 tsp parsley flakes
3/4 tsp salt
1/2 tsp oregano leaves
8 oz lasagna noodles, cooked and drained
3/4 lb shredded mozzarella cheese
1/2 cup Parmesan cheese

Cook and stir ground beef, ground pork, onion and garlic in large saucepan or dutch oven, until meat is brown and onion is tender. I usually add the onion and garlic first as it takes a little longer to to become translucent than it does to brown the meat.

Once the meat is cooked drain off all the fat you can, add the tomatoes and break them up with a fork. Next, stir in the tomato sauce, 2 tbsp of Parsley Flakes, 2 tbsp of sugar, 1 tsp of salt, and 1 tsp basil. Heat to boiling, stirring occasionally. Reduce heat; simmer uncovered 1 hour or until mixture is the consistency of spaghetti sauce.

In another bowl mix the cottage cheese, Parmesan cheese, parsley flakes, 3/4 tsp salt and the 1/2 tsp oregano.

Reserve 1/2 cup meat sauce for thin top layer in the next step. In un-greased baking pan, 13×9x2 inches, layer 1/4 each of the noodles, remaining meat sauce, the mozzarella cheese and the cottage cheese mixture; repeat 3 times. Spread reserved meat sauce over top;sprinkle with 1/2 cup Parmesan cheese.

Here is what mine looked like when I was at this point

lasagna1

Preheat oven to 350 and bake uncovered for 45 minutes.

Tags: , , ,
Posted in Food | No Comments »

Armadillo Run

Saturday, February 21st, 2009

I will be registering for the Armadillo Run in Oldsmar on March 21, 2009. The run is sponsored by the Rotary Club and proceeds will benefit local charities in the city. I hope to have bought an RS100 Heart Monitor by then. My Nike+ Ipod hasn’t been working and I’ve discovered it’s fairly inaccurate. Check out the excerpt below from The Bull Runner

NOV. 10, SATURDAY. At the risk of looking like Inspector Gadget, I wore both the Garmin and Nike+ on my next long run last Saturday. Our goal this time was 18 km. Again, we ran the same initial 12 km route then ran halfway through that same route making our way back to the starting point.

At the onset, both gadgets recorded the same distance. At least, that’s how it was at 2 km. But, as we increased our distance, the discrepancy increased as well.

For the pace, there was a significant 30 second difference too; the Nike+ giving me a faster pace than the Garmin.

At the same 12 km mark (based on the Nike+ in the past), the Garmin, much to my delight, registered the exact distance as the past week’s run: 9.5 km. I pretty much knew which gadget was more accurate at that point. And, sadly, our once favorite 12 km route needed an immediate name downgrade to “10 km route.”

It looks like I’m not running as far or as fast as I thought! I’ve started timing myself the good old fashioned way and measure out my routes using GPS to about a half kilometer now to be safe.

Posted in Running | No Comments »

Tarragon Chicken Recipe

Friday, February 20th, 2009

I followed a similar recipe to this for Chicken Tarragon the other day and it turned out well. I burned the sauce a little in the last stage of reduction so remember to keep the heat low. I also used a sweeter white wine than I wanted to, but it was still very good.

Chicken breast recipe is cooked with tarragon, chicken broth, a little white wine, and heavy cream.
Ingredients:

6 boneless chicken breast halves
salt and pepper
1/4 cup all-purpose flour
1/4 cup butter
1 tablespoon finely chopped onion
1/4 cup dry white wine
1/2 teaspoon dried tarragon
1/4 cup chicken broth
1/4 cup heavy cream

Preparation:
Sprinkle chicken breast halves with salt and pepper; dredge with flour. Set aside remaining flour.
In a large skillet, heat 3 tablespoons of butter over medium heat. Brown chicken on both sides, Remove chicken; keep warm. Add onion to skillet and sauté for 1 minute.
Add wine to skillet; increase heat to high and cook until liquid is almost evaporated, stirring to loosen browned bits on bottom of skillet.

Reduce heat to medium-low; add reserved flour, stirring to a thick paste. Add tarragon and chicken broth. Return chicken to skillet; cover and cook until tender, about 20 to 25 minutes. Remove chicken breasts to a hot platter. Add remaining butter and heavy cream to the skillet. Heat through; pour creamy tarragon sauce over chicken breasts.
Tarragon chicken recipe serves 6

Tags: , ,
Posted in Food | 1 Comment »

Tax Systems and the American Middle Class

Friday, February 20th, 2009

Tax Systems and the American Middle Class

On December 16, 1773 at approximately 7:00PM, 90,000 lbs of tea were thrown overboard from the Dartmouth, Ealeanor, and Beaver in Griffin Wharf. The Boston Tea Party, as it became known, helped spark the American Revolution. The Tea Party’s purpose of showcasing America’s disdain with the British governments unfair taxes did not go unnoticed and Americans have debated taxes ever since. Proponents of tax reform argue that the current system is unfair and is also unnecessarily complex. In order to understand what tax system will provide the most benefit to the majority of Americans in terms of fairness, simplicity, and incentive to continue increasing productivity several questions must be answered. First, what percentage of America is middle class and how is middle class defined in terms of income? Second, what tax systems exist and how does each tax and redistribute income differently? Finally, can the effects of a change to the tax system on tax payer behavior be accurately predicted? For example, if a Value Added Tax (VAT) is introduced can we predict how much consumer spending will decline? Once these elements are examined and a tax system is determined to be the most advantageous to the majority of Americans the implementation costs of this system should be studied for feasibility.

Defining the Middle Class
First, it is important to define the American middle class and determine if it contains the majority of the American population. The American middle class is defined by Gilbert as a broad range of people ranging from families of four earning over $27,500 (the poverty line) to the same family which earns more than $100,000 per year (Gilbert, 1998). This range is further subdivided by many sociologists into what is known as the lower, middle, and upper middle classes. For the sake of this research the middle class will remain general. According to the United States Census Bureau (USCB) slightly over two thirds (67.7 percent) of household’s income fell between $25,000 and $149,000 in 2006 (U.S. Census Bureau). Figure 1 provides a visual representation of the USCB data. The majority of American households are classified as middle class.

figure1.jpg

Figure 1. United States Census Bureau data on 2006 income.

Graduated Income Tax

Several tax systems have been proposed as replacements of the current graduated income tax system. In order to compare the proposed systems it is important to understand how the current tax system in the United States operates. According to Merriam-Webster the simplest definition of income tax is, “A tax on the net income of an individual or business”. The first income tax was enacted in 1894 and contained an exemption for any corporation or association organized exclusively for religious, educational, or charitable purposes (Fremont 56). Since the three percent flat tax on income in 1894 many rules have changed. In fact, since 1942 there have been over 30 major amendments to the tax code many of which introduced multiple changes in a single amendment (Hollenbeck Ninety Years of Individual Income and Tax Statistics). The most recent form 1040 from the Internal Revenue Service (IRS) contains over 75 line items. Items such as moving expenses, student loan interest deductions, and domestic production activities only apply to very specific segments of the population. Furthermore, the 1040 form references over 30 additional forms. Over 130 million income tax returns are filed under this system each year (Hollenbeck Ninety Years of Individual Income and Tax Statistics).

The current individual federal income tax system in the United States ranges from 10 to 35 percent based on a graduated income scale (2007 Federal Tax Rate Schedules). The federal tax rate for families considered middle class ranges from 15 to 28 percent. Using the graduated income tax bracket a family of four earning $25,000 would be taxed $3,750 and a similar family earning $130,000 would be taxed $36,120. The second family earns slightly over five times as much income, but pays nearly ten times as much tax as the first family. This large difference in income to tax ratio occurs within what is considered by most sociologists to be the middle class.

An understanding of how the current graduated income tax effects the middle class has been established. There is a large disparity between the upper and lower middle class in regards to the amount of tax burden carried. Two of the most popular replacements to the current system are the flat tax and the fair tax.

Flat Tax System

A flat tax, sometimes called a flat rate tax or proportional tax, is a tax system in which, “the tax rate remains constant regardless of the amount of the tax base.” (Merriam-Webster Online). Recent flat tax plans by Steve Forbes, Paul Bremer, and Arnold Schwarzenegger have caused conversation about a flat tax among Americans. Additionally, many countries in Eastern Europe have introduced flat tax systems and some experts claim it has boosted their economies (Bartlett). In an article called A Brief Guide to the Flat Tax, Mitchell provides two major advantages the average American would receive from a flat tax system:

the most persuasive feature of a flat tax for many Americans is its fairness. The complicated documents, instruction manuals, and numerous forms that taxpayers struggle to decipher every April would be replaced by a brief set of instructions and two simple postcards. This radical reform appeals to citizens who not only resent the time and expense consumed by filing their own tax forms, but also suspect that the existing maze of credits, deductions, and exemptions gives a special advantage to those who wield political power and can afford expert tax advisers. (3)

The majority of flat tax proposals introduce a rate close to 16 percent for all earned income. Utilizing the same scenario from the graduated income tax example this would mean that a family earning $25,000 would pay $4,000 while the second family earning $130,000 would pay $20,800 in tax. While the $400 increase in tax for the family earning $25,000 is not ideal the reduction of the tax burden by more than $15,000 for the family earning $130,000 is astounding.

A point of contention with the flat tax system is that it will not collect enough tax revenue especially from businesses and upper income earners. This is because although the capital gains tax, interest-income, and dividends tax would be eliminated, the personal income and payroll taxes would still exist (Moore). Most in the middle class would not benefit from the elimination of the capital gains tax, interest-income, and dividends as much as they would the elimination of personal income and payroll taxes.

Furthermore, a flat tax system would not address border-adjusted taxes, the tax built into products exported from our country through corporate taxes. According to Leo Linbeck Jr., a member of American For Fair Taxation:

Border-adjusted taxes are, quite simply, the most potent weapons foreign producers have against U.S. producers and workers. Border-adjusted taxes are consumption taxes removed on export by the producing nation and assessed upon imports as ad valorem taxes.

The border-adjusted tax policy is one of the major flaws of the current graduated income tax system. This can be better understood through a short illustration. If an American company named Megacorp which specializes in making $100 dollar widgets is taxed under the corporate tax of 17 percent it must then pass the cost of the tax onto the consumer through the sale of their product. So, a $100 dollar widget would cost $117. When this widget is exported overseas to Latvia the widget price remains the same. However, Foreigncorp, a Latvian company does not pay a 17 percent corporate tax and within Latvia can sell the same widget for $100. Megacorp has lost 17% of it’s competitiveness due to the border tax. Some critics believe there is no point in altering the tax system in the United States if we do not fix the fundamental problems such as border-adjusted taxes.

Fair Tax System

The fair tax system is based on a national sales tax of 23 percent on new goods and services. There are several concessions for impoverished families and a tax rebate to ensure that necessary goods would not be taxed. According to Neal Boortz, a leading advocate of the plan, the fair tax, “would constitute the biggest transfer of power from politicians to the people since the beginning of this country.” (Boortz 193). Proponents argue this would benefit all Americans in several key ways. First, the fair tax would truly make the tax system participatory rather than mandatory. People who do not spend money on new goods and services would not pay the tax. Second, with the elimination of the corporate tax new corporation growth in the country would be unprecedented. This would lead to more jobs and a stronger economy for America.

Some critics argue that the Fair Tax System would not work because of the tax rebate policy. For example, a family earning $22,400 a year could pay $4,253 in national sales tax if buying only necessities. The same family might actually receive a rebate of $5,152 (Hirsch). This scenario illustrates a situation of income redistribution occurring within the system and is precisely what the fair tax system is supposed to eliminate. Scenarios such as the tax rebate policy are only half of the argument from critics.

Moreover, opponents claim that the Fair Tax System is part of a conspiracy to put more of a tax burden on the poor through taxes on everything purchased. This claim is quickly refuted by Fair Tax proponents. They argue that by encouraging investment and savings without taxation the working class will begin to build wealth. Small business owners would also benefit from the ability to grow their businesses without worry about the tax implications. Perhaps the greatest example is given by Neal Boortz who reminds critics, “As things are now, wealthy Americans with no current income have no income tax bill to pay” (Boortz 197).

Implementing a New Tax System

The effects of changing the current tax system can only be theorized. No fundamental changes to the tax system have occurred since its inception. Much of the anxiety about altering the tax system can be attributed to the fact that over 68 percent of American income comes from salaries and wages. The following examples illustrate the challenges of implementing an alternative tax system. Figure 2 illustrates the sources of total income for individuals in 2005.

figure2.jpg
Figure 2. Internal Revenue Service Statistics on Income

If the Fair Tax System is implemented some critics argue that a black market of goods may develop. Amity Shlaes, a writer for Bloomberg News, argues that a 30 percent tax on goods and services would have a negative effect on the consumer market:

Implement the FairTax, though, and the U.S. will find its tax-scape taking on a certain sleaziness. Vendors will materialize on street corners selling that DVD player without tax.

Even citizens who never thought of breaking the law will snatch up those DVDS. Thirty percent is simply too great a take to ignore. Especially vulnerable will be younger people, who already view property rights as an option, not a given. Think Napster — if you don’t pay for downloads, you certainly won’t feel the need to pay a sales tax six times the one your state charges.

If Shlaes theory is correct this would add an additional cost to the implementation of a Fair Tax system. The cost of policing the sale of goods and services to ensure they are being taxed appropriately would ultimately be passed back to the citizens in the form of a higher tax rate.

Another major concern of the implementation of the Fair Tax System is the double taxation of individuals during the transition period. For example, if a frugal individual worked for ten years without any major expenditures and earned $500,000 total in a ten year period. The $500,000 was taxed under the graduated income tax system and $100,000 in taxes was paid. After the implementation of the Fair Tax System if the individual wanted to purchase a $40,000 vehicle with the money he saved and was already taxed the person would pay a VAT of nearly 30 percent, or roughly $12,000. This is obviously a major concern for individuals with large amounts of savings. Although some savings will be passed onto consumers through the Fair Tax System in the way of lower prices it would not even come close to compensating those individuals that fall into the double taxation scenario.

Recommendations

Upon inspection of several alternative tax systems the Fair Tax System proposal appears to be the most viable candidate as a replacement. This is due to the fact it would create a culture of saving and investment while simultaneously simplifying the complexity of the tax code and promoting economic growth. Although this alternative tax system has been proposed the current graduated tax system cannot be replaced quickly. Research into several areas should be completed prior to any actions to change the graduated income tax. First, factoring geographic location into the effect of each alternative system would have on the middle class needs to be completed. For example, a family of four in rural South Dakota may live comfortably earning $50,000 annually while the same family in New York City would need to earn twice that to maintain a similar standard of living. Second, unintended consequences should be researched more fully for each alternative plan. For example, a study of how a national sales tax under the fair tax plan would impact consumer spending should be performed.

Conclusion

Several alternative tax systems show they may benefit the middle class either primarily through paying less taxes or secondarily through a better national economy. These systems also simplify the amount of paperwork and confusion leading to more faith in the tax system as a transparent and fair entity. The systems, especially the Fair Tax System, should be studied regressively in more detail to determine which system is best for the American middle class.

Works Cited

Gilbert, Dennis (1998). The American Class Structure. New York: Wadsworth Publishing. 0-534-50520-1.

“United States – Income in the past 12 months.” U.S. Census Bureau. U.S. Census Bureau. 7 Mar 2008

<http://factfinder.census.gov/servlet/STTable?_bm=y&-geo_id=01000US& qr_name=ACS_2006_EST_G00_S1901&-ds_name=ACS_2006_EST_G00_>.

Hollenbeck, Scott. “Ninety Years of Individual Income and Tax Statistics, 1916-2005.” Internal Revenue Service. March 2008. Internal Revenue Service. 16 Mar 2008 <http://www.irs.gov/pub/irs-soi/16-05intax.pdf>.

“2007 Federal Tax Rate Schedules.” Internal Revenue Service. 25 November 2007. Internal Revenue Service. 16 Mar 2008 <http://www.irs.gov/formspubs/article/0,,id=164272,00.html>.

“Proportional Tax.” Merriam-Webster Online. Merriam-Webster. 16 Mar 2008 <http://www.merriam-webster.com/dictionary/proportional+tax>.

Bartlett, Bruce. “Flat-Tax Comeback.” National Review Online 10 November 2003 15 March 2008 <http://www.nationalreview.com/nrof_bartlett/bartlett200311100918.asp>.

Mitchell, Daniel. “A Brief Guide to the Flat Tax.” Backgrounder 18667 July, 2005 15 March 2008 <http://www.heritage.org/Research/Taxes/upload/80562_1.pdf>.

Boortz, Neal. The FairTax Book: Saying Goodbye to the Income Tax and the IRS. HarperCollins, 2006.

Hirsch, Bill. “The Fair Tax: Magic or Illusion.” Ocala.com. 01 December 2005. Ocala Star-Banner. 22 Mar 2008 <http://www.ocala.com/apps/pbcs.dll/article?AID=/20051127/OPINION/51126005/1183/news08>.

Moore, Stephen. “Flat and simple, stupid.” High Beam Encylopedia. 2 February 1996. National Review. 22 Mar 2008 <http://www.encyclopedia.com/doc/1G1-18020709.html>.

Shlaes, Amity. “ScareTax, Not FairTax, Is Name for Huckabee Plan.” Bloomberg.com:News. 12 December 2007. Bloomberg. 26 Apr 2008 <http://www.bloomberg.com/apps/news?pid=washingtonstory&sid=aIOIqqEofwFU>.

The Boston Tea Party Historical Society. Date when site was accessed <http://www.boston-tea-party.org>.

Works Consulted

McCaffery, Edward. Fair Not Flat: How to Make the Tax System Better and Simpler. University of Chicago: 2002.

Kemp, “Preferences for funding particular government services from different taxes.” Journal of Economic Psychology 29(2008): 54-72.

Davies, Hoy. “Flat rate taxes and inequality measurement.” Journal of Public Economics 84(2002): 33-46.

Altig, Auerbach. “Simulating Fundamental Tax Reform in the United States.” The American Economic Review 91(2001): 574-595.

Snowdon, “How flat is flat.” International Tax Review 18(2007): 10-13.

Nicodeme, Gaetan. “Flat Tax: Does One Rate Fit All?.” Intereconomics 42.3 (May 2007): 138-142. Business Source Premier. EBSCO.

“How tax complexity can be good for you. ” International Tax Review (2005): 1. ABI/INFORM Global. ProQuest. 17 Feb. 2008 <http://www.proquest.com/>

Erik Fjærli. “Tax Reform and the Demand for Debt. ” International Tax and Public Finance : Special Issue: Public Finance and Financial Markets (Guest 11.4 (2004): 435-467. ABI/INFORM Global. ProQuest. 17 Feb. 2008 <http://www.proquest.com/>

Mintz, Jack M. “The thorny problem of implementing new consumption taxes. ” National Tax Journal 49.3 (1996): 461-474. ABI/INFORM Global. ProQuest. 17 Feb. 2008 <http://www.proquest.com/>

Jeffrey Owens. “Fundamental Tax Reform: An International Perspective. ” National Tax Journal 59.1 (2006): 131-164. ABI/INFORM Global. ProQuest. 17 Feb. 2008 <http://www.proquest.com/>

“SOI Tax Stats.” Internal Revenue Service. January 2006. Internal Revenue Service. 7 Mar 2008

<http://www.irs.gov/taxstats/indtaxstats/article/0,,id=129270.html>

Fremont, Marion. Governing Nonprofit Organizations: Federal and State Law and Regulations. Cambridge: Harvard University Press, 2004.

“Form 1040.” Internal Revenue Service. 2007. Internal Revenue Service. 16 Mar 2008 <http://www.irs.gov/pub/irs-pdf/f1040.pdf?portlet=3>.

Regnier, Pat. “Just how fair is the ‘Fairtax’?.” Money Magazine 7 September 2005 15 March 2008 <http://money.cnn.com/2005/09/06/pf/taxes/consumptiontax_0510/>.

Tags: , , , ,
Posted in Economics | 2 Comments »

Configuring Oracle ASM on Enterprise Linux 5

Friday, February 20th, 2009

Configuring Oracle ASMLib on Multipath Disks

There are two issues with using multipath disks in ASM. ASM cannot handle seeing the same disk twice. If it does, it will cause an error. A single disk can appear three times in a multipath configuration:

1. The first path to the disk
2. The second path to the disk
3. The multipath access point

Here’s an example: say a system has one local disk, /dev/sda, and one disk attached via external storage. The machine has two connections, or paths, to that external storage. The Linux SCSI driver will see both paths. They will appear as /dev/sdb and /dev/sdc. The system may access sdb or sdc, the access ends up in the same place.

If multipathing is enabled, there will be a multipath disk, for example /dev/multipatha, that can access both paths. That is, any I/O to multipatha can use either path. If a system were using the sdb path, and that cable is unplugged, the system will get an error. But the multipath disk will know to switch to the sdc path.

Most system software will be unaware of the multipath configuration. It can use any of the paths, sdb, sdc, or multipatha, and it won’t know the difference. ASMLib is the same way. The default configuration does not care which path it uses.

ASMLib will choose only one of the paths, because ASM cannot handle seeing the same disk twice. This solves the first issue. ASM only sees one path, and it is happy. Here is the second issue: which path does ASM see? In its default configuration, ASMLib will choose the first path it finds. This is the first path as reported by Linux. Depending on your storage driver, it could be the multipath, or it could be one of the single paths.

The system administrator wants ASMLib to always use the multipath disk. What’s the point of having it if Oracle is not using it? There is no way, however, for ASMLib to know what a multipath looks like. It must be told via its configuration.

Disk Scan Ordering

Disks are marked for ASMLib using a process described in ASMLib Installation. ASMLib learns what disk are marked during a process called disk scanning. ASMLib runs this scan every time it starts up. The system administrator can also force a scan via the /etc/init.d/oracleasm scandisks command.

ASMLib examines each disk in the system. It checks if the disk has been marked for ASMLib. Any disk that has been marked will be made available to ASMLib. Normally, ASMLib checks each disk in the exact order the OS lists them. This is fine for most systems.

In the last section, we described a case where the OS order isn’t good enough. The system administrator wants ASMLib to see multipath disks before it sees single path disks. This allows ASMLib to choose the multipath disk and make it available to Oracle.

ASMLib allows two modifications to the disk scan order. First, it allows exclusion of certain disks. In other words, ASMLib will ignore those disks completely. Second, the system administrator can specify disks that are to be scanned first. Disks in this list are scanned before the rest of the disks in the system.

A multipath configuration can use either facility. The system administrator could choose to exclude all the single path disks. ASMLib will then ignore them, only scanning the multipath disks. Conversely, the administrator could specify that the multipath disks are scanned first. ASMLib will see them first, and prefer them over the single paths.

Configuring the Scan Order

The Oracle ASMLib configuration file is located at /etc/sysconfig/oracleasm. It contains all the startup configuration that the system administrator specified via the /etc/init.d/oracleasm configure command. That command cannot configure scan ordering.

The configuration file contains many configuration variables. The ORACLEASM_SCANORDER variable specifies disks to be scanned first. The ORACLEASM_SCANEXCLUDE variable specifies the disks that are to be ignored.

The variables take a whitespace-seperated list of prefix strings to match. In other words, if a disk starts with one of the prefix strings, it matches. For example, the prefix string sd will match all SCSI devices. The disk sda starts with sd. Note that these are not globs. They do not use wildcards. They are simple prefixes. Also note that the /dev/ path is not part of the prefix.

NOTE: When scanning, only the device names known by the kernel are scanned. With device-mapper, the kernel sees the devices as /dev/dm-XX. The /dev/mapper/XXX names are created by udev for human readability. Any configuration of ORACLEASM_SCANORDER or ORACLEASM_SCANEXCLUDE must use the dm prefix.

A couple examples:

Multipath Disks First

The system administrator configures ASMLib to scan the multipath disks first. In the ASMLib configuration file she edits the ORACLEASM_SCANORDER variable to look like so:

ORACLEASM_SCANORDER="multipath sd"

During a scan, ASMLib first tries all disks that start with “multipath”. The multipath device /dev/multipatha certainly matches. It is scanned first. Next, ASMLib tries all disks that start with “sd”. This is all the SCSI disks. The local disk /dev/sda will be scanned, but it is not an ASM disk. The single path disks /dev/sdb and /dev/sdc are also scanned. They are ASM disks, but ASMLib will see that it already has a path to that disk. It will ignore them. Finally, ASMLib will scan any other disks that did not match either prefix.

Excluding Single Path Disks

The system administrator configures ASMLib to ignore the single path disks. In the ASMLib configuration, he edits the ORACLEASM_SCANEXCLUDE variable to look like so:

ORACLEASM_SCANEXCLUDE="sdb sdc"

Here, the system administrator has been more specific. ASMLib should ignore exactly the disks /dev/sdb and /dev/sdc. It should not ignore other SCSI disks. While scanning, ASMLib will ignore those paths, only seeing the /dev/multipath disk. Once again, Oracle will use the multipath disk.

EMC PowerPath and ASMLib
A lot of administrators want to use EMC PowerPath for multipathing and ASMLib for disk access.

However, PowerPath and the 2.4 kernels are not supported by EMC. Systems with the Linux 2.6 kernel, such as RHEL 4 or SLES 9, and the 2.0 ASMLib kernel driver are supported. Please reference the EMC Support Matrix to verify that any/all interoperability requirements are met.

If you have ANY issue related to support for ASMLib and PowerPath on systems using the Linux 2.4 kernel, such as RHEL 3 or SLES 8, please contact EMC.

In order to keep ASMLib from scanning local disks first run fdisk to determine how many local disks you have.


fdisk -l |grep sd |grep -v bytes |awk -F" " '{print $1}' |cut -c 6-8 |sort -n |uniq
sda
sdb
sdc
sdd
sde
sdf
sdg

Place the out from the fdisk command in the /etc/sysconfig/oracleasm.

ORACLEASM_SCANEXCLUDE="sda sdb sdc sdd sde sdf sdg"

Warning!
Once this is complete the oracleasm service must be restarted. Make sure this action is coordinated with a DBA so Oracle is shutdown on the node being modified!


[root@hostname~]# /etc/init.d/oracleasm listdisks
QAORADATA
QAORALOG
[root@hostname~]# /etc/init.d/oracleasm querydisk QAORADATA
Disk "QAORADATA" is a valid ASM disk on device [8, 33] <-- local disks so failover won't work
[root@hostname~]# /etc/init.d/oracleasm querydisk QAORALOG
Disk "QAORALOG" is a valid ASM disk on device [8, 49] <-- local disks so failover won't work
[root@hostname~]# /etc/init.d/oracleasm stop
Unmounting ASMlib driver filesystem: [ OK ]
Unloading module "oracleasm": [ OK ]
[root@hostname~]# /etc/init.d/oracleasm start
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]
[root@hostname~]# /etc/init.d/oracleasm listdisks
QAORADATA
QAORALOG
[root@hostname~]# /etc/init.d/oracleasm querydisk QAORADATA
Disk "QAORADATA" is a valid ASM disk on device [120, 33] <-- power device, failover will work
[root@hostname~]# /etc/init.d/oracleasm querydisk QAORALOG
Disk "QAORALOG" is a valid ASM disk on device [120, 1] <-- power device, failover will work
[root@hostname~]# ls -al /dev/emcpowerc1
brw-r----- 1 root disk 120, 33 Mar 27 08:47 /dev/emcpowerc1
[root@hostname~]# ls -al /dev/emcpowera1
brw-r----- 1 root disk 120, 1 Mar 27 08:47 /dev/emcpowera1

oracleasm configure


/etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: 500
Default group to own the driver interface []: 500
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y

Workaround for ASM in EL5

For some reason using the oracleasm command doesn't work in RHEL5. I found a work around is to use asmtool to manually force label the ASM disk.


asmtool -C -l /dev/oracleasm/ -n "QAORADATA" -s "/dev/emcpowerc1" -a force=yes
asmtool: Device "/dev/emcpowerc1" is not a partition
asmtool: Continuing anyway

Tags: , , , ,
Posted in Linux, Scripting, Technology | 1 Comment »

Audit Database Activity with Splunk or Syslog

Friday, February 20th, 2009

Here are the steps I took to audit database activity and get the data into Splunk. You could technically send this to any log repository capable of listening on a TCP socket. The challenge is that many RDBMS don’t allow you to get database information. Oracle is notorious for this (because they want you to purchase Database Vault). Of course if you don’t have money (like me) for that you can use this solution. You really need three things:

1. Install DBI and Oracle:DBD on the host that will be polling the database.
2. Grant a user who will run the script SELECT on dba_audit_trail table (or whatever table you want to poll and pull into splunk).
3. Setup the perl script with the proper inputs and encrypt the password file for no plain text on the file system.

1. Install DBD::Oracle on 64-bit Linux and Oracle 11g

DBD::Oracle 1.21 to install on a 64-bit Linux OS against the Oracle 11 full client. As root, we downloaded DBD::Oracle from CPAN.


# perl -MCPAN -eshell
cpan> install DBI
cpan> get DBD::Oracle
...
# cd /root/.cpan/build/DBD-Oracle-1.21
# export ORACLE_HOME=actual value of Oracle Home
# export ORACLE_SID=actual value of ORACLE_SID
# export ORACLE_USERID=working ORACLE_USERID
# export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME
# perl Makefile.PL
...
# make
...
# make test
...
# make install

Now you can install the dbipoll perl script.

Generate a random key, encode a file with it, and then decode a file with it. Useful to generate a password, encrypt it and then get rid of the plain text password inside scripts.

openssl rand 32 -out key.temp
openssl des3 -e -pass file:key.temp -in passwd -out passwd.enc
openssl des3 -d pass file:key.dec -in passwd.enc -out passwd.dec
openssl des3 -d -pass file:key.dec -in passwd.enc -out passwd.dec
openssl des3 -d -pass file:key.temp -in passwd.enc -out passwd.dec

Verify you are running encrypted. Add the following temporarily to your sqlnet.ora

TRACE_DIRECTORY_CLIENT=/ora-main/app/oracle/product/11.1.0.6/db/network/trace
#TRACE_FILE_CLIENT=client
TRACE_LEVEL_CLIENT=16

Inspect the trace for encryption

[root@mymachine trace]# cat cli_9668.trc |grep encry
(2863411744) [13-FEB-2009 15:37:50:911] naeshow: These are the encryption algorithms that the client will accept:
(2863411744) [13-FEB-2009 15:37:50:911] naeshow: Choice 0: no algorithm; encryption inactive
service: encryption
(2863411744) [13-FEB-2009 15:37:50:934] na_info: Oracle Advanced Security: encryption service for Linux: Version 11.1.0.6.0 - Production
service: encryption
(2863411744) [13-FEB-2009 15:37:50:935] nacomps: service encryption
(2863411744) [13-FEB-2009 15:37:50:940] nacomus: service encryption
service: encryption
service: encryption
(2863411744) [13-FEB-2009 15:37:50:952] naeecom: The server chose the 'RC4_256' encryption algorithm
(2863411744) [13-FEB-2009 15:37:50:952] na_tns: encryption is active, using RC4_256

Note the last two lines indicating we are encrypted.

Here is the dbipoll.pl script.


#!/usr/bin/perl
#
#
# Now accepts arguments for countkey, table, and columns.
# countkey will ideally be a timestamp field, such as EXTENDED_TIMESTAMP in AUD$.
#
# Need to deal with the following variations...
# 1 - Oracle local
# 2 - Oracle remote
# 3 - mysql local
# 4 - mysql remote
# 5 - Sybase local
# 6 - Sybase remote
#
# Oracle connect string: $dbh = DBI->connect("dbi:Oracle:host=$server;sid=$db;port=$dbport", $user, $passwd);
# Oracle connect string without env vars: $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd )
# mysql connect string: $dbh = DBI->connect("dbi:mysql:database=$db\;host=$server;port=$dbport","$user","$passwd");
# Sybase connect string: $dbh = DBI->connect("dbi:sybase", $username, $password, $db);
use IO::Socket;
use strict;
use DBI;
use Getopt::Long;
use Sys::Syslog;
#
our ($server,$db,$table,$columns,$user,$enpasswd,$passwd,$dbtype,$tcphost,$tcpport,
$countfile,$countkey,$ofile,$dbport,$baseconnect,$dbh);
#
GetOptions(
"dbtype=s" =>\$dbtype, # Type of DB (Oracle, MySQL, etc.)
"server=s" =>\$server, # IP address of database server.
"dbport=s" =>\$dbport, # Port for connecting to remote DB.
"db=s" =>\$db, # Database to retriever records from.
"user=s" =>\$user, # DB username.
"password=s" =>\$passwd, # Unencrypted password.
"xpassword=s" =>\$enpasswd, # Encrypted password.
"table=s" =>\$table, # Table to retrieve from.
"columns=s" =>\$columns, # Columns to fetch.
"tcphost=s" =>\$tcphost, # Splunk server IP.
"tcpport=s" =>\$tcpport, # Splunk tcp listener port.
"countfile=s" =>\$countfile, # File to store row count status.
"countkey=s" =>\$countkey, # Field to use as count value
"o=s" =>\$ofile, # File for output (default is stdout).
);
#
# Deal with encrypted passwords first...
if (!$passwd) {
if ($enpasswd) {
$passwd = `openssl des3 -d -pass file:/etc/pki/tls/private/dbi.key -in $enpasswd |awk NF`;
chomp($passwd);
}
}
#
if ($dbtype =~ /oracle/i) {
$ENV{'DBI_DRIVER'} = "Oracle";
$dbtype = "Oracle";
$dbh->{InactiveDestroy} = 1;
#$baseconnect = DBI->connect("dbi:$dbtype:$db");
if (!$dbport) {
# Assume a local DB.
$dbh = DBI->connect("dbi:$dbtype:$db", "$user", "$passwd") ||
die( $DBI::errstr . "\n" );
#
} else {
if (!$passwd) {
$dbh = DBI->connect("dbi:Oracle:host=$server;sid=$db;port=$dbport", $user) ||
die( $DBI::errstr . "\n" );
} else {
$dbh = DBI->connect("dbi:Oracle:host=$server;sid=$db;port=$dbport", $user, $passwd) ||
die( $DBI::errstr . "\n" );
}
}
#
}
if ($dbtype =~ /mysql/i) {
$ENV{'DBI_DRIVER'} = "mysql";
$dbtype = "mysql";
#$baseconnect = DBI->connect("dbi:$dbtype:database=$db;host=$server");
if (!$dbport) {
# Assume a local DB.
$dbh = DBI->connect("dbi:$dbtype:host=$server;database=$db","$user","$passwd") ||
die( $DBI::errstr . "\n" );
#$dbh = $baseconnect . "$user" . "$passwd";
} else {
if (!$passwd) {
$dbh = DBI->connect("dbi:$dbtype:database=$db;host=$server;port=$dbport","$user") ||
die( $DBI::errstr . "\n" );
} else {
$dbh = DBI->connect("dbi:$dbtype:database=$db;host=$server;port=$dbport","$user","$passwd") ||
die( $DBI::errstr . "\n" );
}
}
#
}
if (($dbtype =~ /sybase/i) && (!$ENV{'SYBASE'})) {
$ENV{'SYBASE'} = '/usr/local/freetds';
$ENV{'DSQUERY'} = "$server";
$dbtype = "sybase";
#$baseconnect = DBI->connect("dbi:$dbtype", "$db");
if (!$dbport) {
# Assume a local DB.
$dbh = DBI->connect("dbi:$dbtype:database=$db","$user","$passwd") ||
die( $DBI::errstr . "\n" );
} else {
if (!$passwd) {
$dbh = DBI->connect("dbi:$dbtype:database=$db;port=$dbport","$user") ||
die( $DBI::errstr . "\n" );
} else {
$dbh = DBI->connect("dbi:$dbtype:database=$db;port=$dbport","$user","$passwd") ||
die( $DBI::errstr . "\n" );
}
}
}
my $sth;
if ( -f $countfile) {
# Compare vs. file with saved count
open (CF, "<$countfile");
my $filecount = readline CF;
chomp $filecount;
close (CF);
# Adding an ORDER BY clause here may be a requirement
$sth = $dbh->prepare("SELECT $columns FROM $table where $countkey > \'$filecount\' order by $countkey");
} else {
$sth = $dbh->prepare("SELECT $columns FROM $table order by $countkey");
}
#
our $handle;
if (($tcphost) && ($tcpport)) {
# Send directly to splunk server...
# Create tcp socket to send the data to the splunk server
my $remote = IO::Socket::INET->new( Proto => "tcp",
PeerAddr => $tcphost,
PeerPort => $tcpport,
Type => SOCK_STREAM)
or die "cannot connect to tcp daemon on $tcphost";
$remote->autoflush(1);
$handle = $remote;
#$target = "\$remote";
} elsif ($ofile) {
# Write to local outfile...
open (OFILE, ">$ofile");
$handle = *OFILE;
#$target = *OFILE;
} else {
# send to stdout
open (STDOUT, ">&1");
$handle = *STDOUT;
#$target = *STDOUT;
}
#
# Get some Rows...
$sth->execute or die $sth->errstr;
open (CF, ">$countfile");
while(my $hash_ref = $sth->fetchrow_hashref) {
my $output = "";
my $lastrow;
foreach my $key (keys(%$hash_ref)) {
my $str = $hash_ref->{$key};
if ($key ne "EXTENDED_TIMESTAMP") {
if ($str =~ /"/) {
(my $xstr = $str) =~ s/"/\\"/g;
$str = "\"$xstr\"";
} else {
$str = "\"$str\"" if ($str =~ / /);
$str = "\"$str\"" if ($str =~ /,/);
}
$output = $output . $key . "=" . $str . ",";
}
if ($key eq "EXTENDED_TIMESTAMP") {
# if ($key eq "TO_CHAR(EXTENDED_TIMESTAMP,'MM/DD/YYYYHH24:MI:SS')") {
$str =~ s/\"//g;
$lastrow = $str; #if (lc($str) eq lc($countkey));
}
#
}
chop($output);
# Update countfile
# Where did we leave off?...
*CF->autoflush(1);
seek(CF,0,0);
print CF "$lastrow\n";
#
# And send them somewhere.
print $handle $output . "\n";
}
#
$sth->finish();
$dbh->disconnect;
#
close (CF);
close $handle;

Now schedule this to run (I just wrote a wrapper script in ksh exporting the variables to be safe).


#!/usr/bin/ksh
#
export ORACLE_HOME=/ora-main/app/oracle/product/11.1.0.6/db
export ORACLE_SID=rmdev1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME
#
/usr/bin/dbipoll.pl -dbtype=oracle -server=servername -dbport=1521 -db=development -user=splunk -xpassword=/etc/dbipasswd -table=dba_audit_trail -columns="OS_PROCESS,OS_USERNAME,USERNAME,USERHOST,ACTION,ACTION_NAME,TRANSACTIONID,SCN,SQL_BIND,SQL_TEXT,TO_CHAR(EXTENDED_TIMESTAMP,'MM/DD/YYYY HH24:MI:SS'),EXTENDED_TIMESTAMP" -countfile="/app/oracle/product/monitor/countfile" -countkey="EXTENDED_TIMESTAMP" -tcphost=splunkserver -tcpport=9094

Of course you will also need to setup a tcp input port on your splunk server. Once you have all that in place you can run the following query to see what hosts are performing the largest amount of actions on the database and what users are running the majority of actions on the database. I have removed hostnames and usernames legends from the following for security.

index=indexname sourcetype=databasesource | timechart count(USERNAME) by USERNAME

DBAuditSplunkUsers

index=indexname sourcetype=databasesource |timechart count(USERHOST) by USERHOST

DBAuditSplunkHosts

You can also use this to setup alerts if any users runs an update on a database (useful to catch those sneaky developers). :)

Tags: , , , , , ,
Posted in Linux, Scripting, Technology | 1 Comment »