Splunk trick to display a one to many relationship in a table with granular and aggregate values

Another oil change and another blog post. Good thing there isn’t always a relationship between those two things or my cars wouldn’t be running. Figured I would spend a minute talking about one of my new favorite Splunk tricks. I think I ran across this in the Implementing Splunk book but then could never find again. Was pleased that it showed up in the Advanced Searching and Reporting class I took at .conf2013 (great class BTW). The trick relates to formatting data and covers a variety of use cases mostly related to displaying a one to many relationship at various levels of granularity. 

Wrapping your arms around Trends: Part 3 (with a side dish of custom parameters)

What a week. Found a couple issues with our 5.0 install with varying degrees of critically. One is being fast tracked so will see what we see. While I'm confident they will be addressed, I'm also the type of guy who would just assume they are fixed yesterday. However, I also waited tables in what seems like a past life. I try to be very conscious about blaming the waiter for a burned steak. There is also a bit of thoughtful reflection as I sit here in Chick-fil-A listening to the music and chill'n while the family assault vehicle is getting an oil change across the street. It isn't the music that is causing the reflection (though it certainly can and hopefully will have that effect) as much as I wonder at the possible connection, if any, between this post and these (1 & 2) that talk about ArcSight tentatively looking for a buyer. Only time will tell what the long term effect acquisition will mean.

At any rate, as a short continuation of this "series" here is a quick report taking the concept of the first article with the Trend created from the second. Since you are pulling from this new Trend you can see all the data in one report - when it started, ended, how long it took, insert count, and ultimately if it was successful or not. Unless you enjoy mentally converting milliseconds into minutes I would recommend creating 2 variables that will convert the time to minutes. The first is to divide the milliseconds by 60,000. The next uses the round variable to convert the first variable to the closest whole minute. Is it exact? No, but unless you have a Trend that is bumping up against the hard coded time limitations for Trend query runs does it really matter?

Reports and what you put in them

I don't know about you but I sometimes have mixed emotions when it comes to reports. Most tend to fall into one of two buckets. Either they are a one page "executive" type summary with colored eye candy charts or they are a ridiculous number of pages showing the gory details of every port, path, or transaction between end points that is so far into the weeds you can lose sight of the bigger picture. In either case the report would probably cause you to open your SIEM in order to do further investigation because in either case a piece of the puzzle for the initial series of follow up questions are probably missing. My top whatever is this, this, and that. Great - is that distributed or focused to just one or a hand full of machines?

So what can you do to make the report itself more stand alone or actionable?

Monitor vs Alert

Several weeks ago now I took some money out of the ATM and the bottom of the transaction receipt showed a balance that seemed a bit low to me. I brought it up to my wife at lunch and watched her mentally parse a multitude of variables to include the day of the month, what bills had been paid, and the times and amounts of money that move out of checking and into sub accounts. About 5 seconds later she said the amount sounded about right. This is the difference between monitoring and alerting.

Don't get me wrong. My wife isn't the type that pulls up the account information every day to check the ebb and flow of cash but by doing the bills and interacting with the accounts she had a familiarity for how and when the money does flow. The trick in my mind with a SIEM is to figure out the balance for both types of actions. Not only do you have to balance how and where analysts get data but also things like how much should be simply monitored vs alerted on, if you create reports is there too much or too little data in them to be actionable, if the report is on a particular data source can you give it some historical context or cross reference it against other data sources.

To many of those ends my new favorite report template in ArcSight is one that comes out of the box. 4 charts on one page and then a table. Most of the daily reports we have been creating were of the one table variety with a level of detail in the information - source, destinations, times, counts, etc. While we humans are good at visually picking out patterns and nuance things out of spreadsheets putting what amounts to a rolled up summary of top information with the multiple charts at the top of the report is great. Now within the report not only have you framed aspects of the data but since more detailed information is in the table section readers can pull out an IP/user name/whatever from the top page and then search for it within the report.

Converting Windows failure codes to something readable

I posted this in the content section on the ArcSight user forums (and if you are ArcSight client you are reading them right...?) but I haven't been able to take anything with antihisimine for the last week and won't be able to until after my alergy test on Monday so feeling mis-er-a-ble.

Hopefully your SIEM is capturing the reason/status/substatus codes when it comes to failed Windows login events. In ArcSight that data is generally found in deviceCustomString4. We recently had to create a series of reports that were being passed to some folks outside the more technical group who would normally just look at the codes.

To make the reports more readable I did a copy/paste job from the GREAT resource over at Ultimate Windows Security - the event encyclopedia. Then created an active list with 2 string fields making sure to index the one with the code. You do that so in your report query you can use the variable allowing you to grab a value from an active list basically doing a join. Since this calls for a string comparison with letters I chose to make the data in the AL lower case and then use another variable to take the data from customString4 and make it lower case as well. Then in your query field selection if you look under the variable section you should see BOTH fields from the Active List. Select the normal fields you want and include the unindexed field from the active list and just clean it up using the alias field in the report.

Worked like a champ though given my current state it might read like a train wreck.