Salesforce data analysis leveraging time-based Splunk lookups

 As a Sales Engineering manager I recently wanted to perform some analysis of customer meeting data from Salesforce - specifically when one of the Architects on the team brought in an overlay. My default tool of analysis is Splunk (he says shocking no one) and while I could write a very long case statement or use a lookup I was left with a challenge. Time. Given a long enough time period, there were Architect to territory alignments that straight up changed OR Architects were temporarily assigned to territories to support gaps in coverage. Regular lookups don't work in this case. What to do?

CQLing Game Data - The Blog Version

I've been learning CrowdStrike's LogScale platform recently. To help myself learn the CrowdStrike Query Language (CQL) I figured I'd do some analysis of game data I had collected awhile ago. Help me learn and then create this post to perhaps help others who might be learning CQL as well. 

That end, this post is a more written version of the live & somewhat interactive LogScale dashboard located: here. 

I'm providing the data itself at the bottom of this post if you want to monkey around with it.

It isn't uncommon for users of tools like LogScale to not know where to start from a query perspective. Something unique with LogScale is being able to easily share dashboards outside of the tool itself. This is a pretty cool capability! The other place where I see individuals somewhat struggle is how to create a dashboard. Not mechanically as much as how to lay out data. 

Splunk to LogScale Cheatsheet

Learning a new language is always fun(?). Many folks start with existing paradigms and look for comparisons. I'm no different while trying to learn LogScale's Query Language or LQL. 

I've looked for various cheetsheets and haven't found much. I'll paste a very rough one I've created below and hope to update it over time. Feel free to pass over anything I should add or tweak! There also has to be a better way to post this other than a pasted image from Excel. I'm a Luddite /shrug

Finding Log Volume Ingestion Anomalies in Splunk

 

This is for my man Destry who I met recently in person. He was giving me a bit of good-natured fun at not posting more frequently. So Destry, this is for you!

I’m doing a Splunk tips & tricks workshop this week with some folks who, among other things, had asked for a query to identify log volume anomalies. Ahh volume anomalies. So many variations of this. Several apps can be found on Splunkbase which have been developed by the user community. One might ask why Splunk hasn’t incorporated more of this sort of thing in the Monitoring Console /shrug.

My normal recommendation to folks is run a few queries to capture log volume (internal index license log) and event counts (tstats) in a ‘summary index’ for long term retention and quicker analysis. Some of that is likely found in the introspection index but I’ve not done a deep dive there TBH. The workshop I’m doing is with folks in a multi-tenant environment where each would like to do their own quick analysis.

So let’s define a few goals

• When a host is sending abnormally more or less of a data type compared to other hosts
• When a host is sending abnormally more or less of a data type compared to itself
• One query to do both comparisons to keep compute down and not have intermediate steps (like populating or reading from a lookup) for simplicity

New Managers: Hiring Process

The bug to write has bit again. 

There aren’t a ton of quick resources nor do many orgs place a lot of emphasis on training for new managers. I’ve done a good bit of candidate prospecting and hiring over the last year so I’ll write through a bit through that lens across a few posts. I’m no expert but do have some thoughts on the subject.

You’re at a point where you must hire someone. What does that process look like and where do you even start?

I’d begin by contemplating the following

• Who you hire represents a $500k - $1M investment assuming they will be with you for several years. Make sure you give the process appropriate time & energy.
• “We hire people based on what they know and fire them for who they are.” Unknown original source but really like it. Don’t be so focused on skills that you miss warning signs of cultural fit and work ethic.  

After giving those a good think, here are some additional framework-y things to do some mental gymnastics on before you start. You will inevitably have to adjust as you go but limit how much of the plane you are building after it is in the air.

Framework Compliance: Does activity live on a spectrum or an iron triangle?

Whenever I see an article title like “<insert framework>: One framework to rule them all” as recently discussed here I’m simultaneously reminded of the following xkcd cartoon, which touches on the N+1 issues of framework consolidation, and my grandmother saying "you can’t change the direction of a parked car." Relative to achieving framework compliance I’m not sure if those two thoughts live on the same spectrum or make up 2 corners of a compliance related iron triangle. Not sure what the 3rd iron triangle corner would be though.

Splunking Game Data - Darkfire Heroes part 2

In part of one of this series I went down the path of Splunking game data from a 2 day, single challenge event. In this installment I'm going to dive into a bit of data from an event that encompassed several different challenges. 

In looking back at the dashboard I created, how I was working with some of the data, and the sheer volume of data across all of the challenges - the dashboard itself might not have much value from a game analytic perspective lol. I also realized how much I focused on one challenge - waves. In looking back I think that was largely due to the numbers being smaller. The approaches to displaying the data will work across all of the events so at some level it would be a lot of copy and paste as well. At any rate hopefully folks will take something away from the underlying queries and some of the challenges with trying to display the data/volume of data. TBH this is the more primary focus of this overall effort. 

Data was captured across 4 challenges, 3 player level brackets, and listed scores for the top 100 positions (other than the clan boss fight).

1. Waves - 5 separate groups of mobs attack your heroes
2. Pacifism - multi-level, player vs environment (PvP) track where you couldn't bring damage dealers
3. No Healers - multi-level, PvE track where you couldn't bring a healer
4. Boss - big bad dude that everyone in the clan attacked

Splunking Game Data - Darkfire Heroes

(Part 2 Link)

So there is somewhat of an eclectic mix of drivers going into this post. As I talk with individual Splunk users, Splunk administrative teams, and larger teams that have access to Splunk (security, operations, etc) I sometimes come across a subset of folks who have a tough time trying to figure out how to take advantage of the data they have access to. In some cases it is because they lack imagination, vision, or strategy relative to what they are trying to accomplish. Sometimes it is being so heads down in the daily grind they have trouble 'looking up' so to speak and collecting their thoughts. More often than not though they are wearing multiple hats and could use a hand getting over the Splunk language learning curve. This post is designed to help a bit - some Splunk Processing Language (SPL) exposure mixed with a bit of dialogue on why I made the dashboard/dashboard panels the way I did.

To start with I need data. Turns out I've started playing a mobile game called Darkfire Heroes. It isn't quite in a beta state but isn't available globally quite yet. The other day, Dec 8 & 9 to be exact, they had a 2 day Player vs Player (PvP) contest. Individual match wins were tracked as well as aggregate win total if you belonged to a group within the game; a clan. As a total side note it is interesting to see people's gaming pedigree based on descriptions they use. Are some of the bad guys/monsters you fight creeps, mobs, trash, etc and are you a part of a clan, guild, or alliance? Outside of this mini event there is a clan rewards chest that also gives varying level of rewards based on aggregate PvP wins over a week. PvP matches won during this 2 day event will count towards that reward chest. 

User Aware Splunk Dashboards

One of the more interesting aspects of Splunk is giving users direct access to raw data. This is great on so many levels from a troubleshooting or investigative perspective. However there are times when you'd rather or need to give people, let's call it, a guided experience to what data they see. This is particularly true when within the same overarching or umbrella organization you have separate lines of business or groups of people such that you don't want to give people direct access to the data; you want/need to limit access at more of the UI level. You might have some data source like a vulnerability scanner where all of the data is coming into one index and you want to give people access to only the scan data that applies to them. One way to accomplish this is by adding search time restrictions to particular roles. While effective this approach can get complex very quickly. The following Splunk .conf talk gets into some great detail (link).  Another approach is to slice and dice what index the data goes into as it is indexed based on the user groups you have setup. This can be effective as well but then data from that singular tool is all over the place and what happens if you are using something like CIDR blocks to map data to index and those CIDR blocks change? In this article I'm going to get into a third approach that is making the dashboard user aware and displays information based on who the user is without giving them native access to the data.

I should say there are likely several ways to accomplish this that might be more efficient or work better for particular use cases. This worked for me though and can be a good starting point. If you know of other ways to limit data access at the UI level I'd love to hear about them; feel free to put them in the comments!

Drilling into the OTHER category in Splunk

So what has broken my 3 year blog posting hiatus you might ask? Some nerd-like delight in working through a Splunk dashboard capability I didn't realize was there!

Several days ago some fellow Splunk users asked if there was a way to drill into the "OTHER" category. They had an overview dashboard with a bar chart viz allowing the user to pivot to a more detailed interactive dashboard. The challenge was the overview graphic leveraged Splunk's ability to show the top N results with the rest of the results up as OTHER. The interactive dashboard didn't like receiving OTHER as that wasn't a value in the data. I tried a few different approaches but they honestly didn't work. Through that effort though I stumbled upon the ability to set a condition match in XML.

Wait whuuut? I've known about condition match from adjusting navigation bars in Splunk and it turns out this capability is also available in dashboards themselves. Could I use this mechanism for the use case at hand?

Adjusting Splunk forwarder phonehome / throughput

I was in the process of writing up a few things for a new EDU that is going to be spinning up a larger scale Splunk environment and figured if I was going to the effort it might as well be placed here for others to see. In working with my own environment today I realized I was making some adjustments that I take for granted but that we had to learn and bake in. For this installment these items are focused on the following:

1. Adjusting the forwarder to deployment server phone home interval
2. Allowing forwarders to send more than 256 kbps

Find saved searches in Splunk that are failing

I hope to circle back to this eventually. Until then --- enjoy:

index=_internal log_level=ERROR SavedSplunker | stats count as Count by host message | rex field=message "savedsearch_id=\"(?<Author>[^;]+);(?<App>[^;]+);(?<Search>[^\"]+)\"(?:, message=)?(?<Message>.+)" | table host App Search Author Message Count | eventstats sum(Count) as total by host | eventstats sum(Count) as foo by host App | sort -total -foo -Count | fields - total foo

Splunk admin tasks after you start getting data in...

I had the rather unique privilege to post a 3 part blog series on Splunk's official site recently. The focus was on some administration tasks Splunk admins should work into their routine. There is a level of assumption when users search in Splunk - these hosts are really these hosts and events that are observed within a time range really happened then. The series talks through a couple methodologies to validate those assumptions

• Part 1 - Validating host field values: link
• Part 2 - Validating agent host's system time: link
• Part 3 - Getting a feel for data ingestion latency: link

Moving toward Splunk's CIM

For those that don't know, for some time Splunk has been moving toward a Common Information Model (CIM). They are using this both a data normalization effort - what should you name fields from particular data sources - as well as a layer of abstraction placed over your data to indicate what the data IS. In the end this is a worthwhile effort though the devil is in the details for those of us with 1) older - by whatever definition - Splunk instances with local extractions and 2) larger - again by whatever definition - Splunk instances with 3) a large - sensing a trend? - number of sourcetypes. Frankly I'm big time scared of the performance implications of using what was a second or third class citizen in Splunk (tags) as my primary source of querying across 1k sourcetypes and 15B logs per day. Martin Mueller had a great talk at .conf15 looking into performancy sorts of things which brings a lot to that aspect of the discussion (search for his name here for a link to the slides. His name is actually spelled Martin Müeller and a direct link to the pdf is >here<).

Performance concerns aside the question is how do you go about a discovery effort to figure out which of your sourcetypes should map to which CIM based data model? In theory and based on the number of sourcetypes you have you could do this by manually reviewing a list. That might work for some percentage of sourcetypes but perhaps not all. At any rate some of this is addressable by the new Splunk commands: pivot and datamodel. The challenge with those is they are essentially searching across your data with the fields contained within the model in a one off basis (one DM at a time) and if the fields don't match then there simply is no results. What I was wanting was a way to take all of the fields from my data and throw that up against all of the fields in all of the models with a side of fuzzy string comparison. I *think* I have found a way.

Taming Verbose Windows Logs - Update

In looking at the Windows firewall logs coming out of the Security event viewer (mainly 5156) I realized the space in "program files" was throwing off the regex. You got to love the format of Windows logs. Maybe one day we will ingest the XML version - not likely :(. If anyone has ideas on a better regex I am ALL EARS! I tried using newlines, carriage returns, spaces, etc in front of "Network" (which is 2 lines down from the application name) but wasn't getting the desired results.

This is an update to >this< post.

Transforms
New
(?ms)EventCode=(5156|5152).*?Keywords=(Audit Failure|Audit Success).*?Message=The Windows Filtering Platform (?:has )?([^\.]+).*?Process ID:\s+(\S+).*?Application Name:\s+(System|.+\.exe).*?Direction:\s+(\S+).*?Source Address:\s+(\S+).*?Source Port:\s+(\S+).*?Destination Address:\s+(\S+).*?Destination Port:\s+(\S+).*?Protocol:\s+(\S+).*?Filter Run-Time ID:\s+(\S+).*?Layer Name:\s+(\S+).*?Layer Run-Time ID:\s+(\S+)

Old
(?ms)EventCode=(5156|5152).*?Keywords=(Audit Failure|Audit Success).*?Message=The Windows Filtering Platform (?:has )?([^\.]+).*?Process ID:\s+(\S+).*?Application Name:\s+(\S+).*?Direction:\s+(\S+).*?Source Address:\s+(\S+).*?Source Port:\s+(\S+).*?Destination Address:\s+(\S+).*?Destination Port:\s+(\S+).*?Protocol:\s+(\S+).*?Filter Run-Time

Props Field Extraction
New
^Trimmed Event EventCode=(?<EventCode>5152|5156) (?<Keywords>Audit Success|Audit Failure) (?<Process_ID>\S+) (?<Application_Name>.+) (?<Direction>Outbound|Inbound) (?<Source_Address>\S+) (?<Source_Port>\S+) (?<Destination_Address>\S+) (?<Destination_Port>\S+) (?<Protocol>\S+) (?<Filter_Run_Time_ID>\S+) (?<Layer_Name>\S+) (?<Layer_Run_Time_ID>\S+) (?<TaskCategory>blocked a packet|permitted a connection)

Old
^Trimmed Event EventCode=(?<EventCode>5152|5156) (?<Keywords>Audit Success|Audit Failure) (?<Process_ID>\S+) (?<Application_Name>.+) (?<Direction>\S+) (?<Source_Address>\S+) (?<Source_Port>\S+) (?<Destination_Address>\S+) (?<Destination_Port>\S+) (?<Protocol>\S+) (?<Filter_Run_Time_ID>\S+) (?<Layer_Name>\S+) (?<Layer_Run_Time_ID>\S+) (?<TaskCategory>blocked a packet|permitted a connection)

Does better information sharing require a security clearance?

From time to time the topic of information sharing comes up in relation to getting security clearances in order to have more open and timely dialog with various government agencies. Having lived in that space for a time I would agree having a clearance would help in having overarching conversations if only because the culture is one that defaults to needing a clearance to have meaningful dialog. The problem comes when I put on more of an incident responder/cyber defender hat. The TLDR summary is the information most useful to cyber defenders isn't who has compromised their environments as much as it is the IOCs and methodologies used to gain entry. This is because we aren't defending a strategic point in 3D space. We are having to defend our organizations potentially from every computer plugged into an ethernet jack or wifi around the planet. Note this post is about why I think having multiple people in your security group cleared is less important that an adjustment in the classification paradigm. This isn't in response to being notified that my company has been breached.

Not to rehash 'cyber warfare' conversations post Aurora but conflict, by whatever definition, in the 5th domain (cyber) is unlike kinetic based conflict occurring especially in domains 1 through 3 (land, air, sea) and less so in space (the 4th). To back up a bit and make gross generalizations the end state of much of the classified intelligence space is ultimately linked to and focused on attribution (aka who's responsible for X so I can go punch them in the throat). Retribution though doesn't happen in the 5th domain - at least at the commercial level. The impasse generally found then is at the government information sharing level where the who is portion marked with the highest classification level because in that world that is the most important piece of information. That trickles down to portion marking the techniques being used and lastly things like specific things like IPs. While the initial response to a breach from management is often "who did this?" followed quickly by "why were we a target?" and the "why's" can and should help shape our defensive strategies/priorities, as a cyber defender at some level I could care less about the answers. Why you ask? Because that information isn't actionable. I'm more interested in the how as it relates to knowing what I should look for and what needs to be fixed. 'How' in this case ISN'T just which IP addresses were used 3 months ago and we are only hearing about this now. It is the full scope of IOCs.

I fully appreciate if the federal agencies openly shared IOCs and TTPs the malicious actors would simply switch how they are doing what they are doing. That said though I don't believe the solution to more and better sharing at the rank and file actionable data level is to get a clearance so we can better operate in the federal space once we are 'read in'. For cyber based compromise notifications adjust the portion marking on the classified documents appropriately allowing companies to better defend and respond /shrug.

Electronically Aided Collisions and InfoSec

Like cruise control for your car, GPS assisted autopilots and the like for boats can help operators with mundane tasks like holding a course over a long stretch of water. Unlike driving though boating comes with additional challenges - keeping track of water depth, the impact of changing weather conditions, the fact that there generally aren't defined 'roads' or travel lanes, etc. The rise in adoption of electronic navigational devices has also seen a rise in what is being termed "Electronically Aided Collisions." These can range from a GPS device malfunction or signal interference which causes the boat to veer off course and run aground to a momentary (or longer term) judgement lapse where your attention is off where the boat is going and the general surroundings to the detriment of your boat - or worse - others.

While my sail boating father and I were talking about this and him sharing stories my mind starting drawing parallels to the InfoSec world. I've tried to boil these down really to two thoughts

Gaining visibiliy to ad-hoc data exports from Splunk

Along the same lines of understanding how your users are using Splunk and dovetailing into are users abusing their access to data in Splunk is taking a periodic look into what data they might be exporting. By that I mean exporting to a csv or maybe generating a pdf of a dashboard. Ideally you would like to know, for example, if this Mark character has exported something, what format was it in, what was the search, and how many records or results were included in the download.
There are a couple challenges

1. Search results (result count, events searched, etc) are in the internal search completion logs while the search parameters are in the internal search initiation logs.
2. Those logs are separate from the web logs that indicate someone has performed one of the export actions.
3. The various Splunk commands you might use to merge all of this data has some limitations that you will need to keep in mind. For example to use a subsearch to get something like search_id and pass it to a parent search is limited by default to a 60s runtime and/or 10k results. A join or append is limited to a 60s runtime and/or 50k records, again by default. If you have even a moderately sized deployment over the course of several days you have thousands of searches being run when you factor in your users, scheduled content, and internal Splunk processes. I suppose one way to mitigate this is to review the detection query output every day but that seems a little too frequent to me.

Splunk Apps: Forwarder Health

It is long past time I actually wrote a few posts on the Splunk apps I've created. Woke up far too early for a Saturday morning and in an effort to avoid anything around the house I will rationalize this as productivity at a general level and feel I've accomplished much! Who knows - it might be of value to my ... ones ... of readers! =)

Actually it was VERY cool to have a guy come up after my presentation at the 2014 Splunk user conference and mention having read my blog while working with ArcSight and now while working with Splunk (thanks Joe!).

Forwarder Health

So our environment has currently some 2,200+ forwarders which is certainly not the largest environment out there but is likely much larger than the average. While there are apps like Splunk on Splunk and Fire Brigade to help identify issues with your indexers and search heads there wasn't something that helps identify issues with forwarders. Admittedly this is a hefty task as there are innumerable issues a forwarder can have. I wondered though if there was a way to generically detect if an agent was having issues. The sage like advice from the Verizon breach reports bubbled up in my mind - start by looking at the size of the haystacks. What if you were to compare the number of internal logs a forwarder was generating and compare it to the average? A couple hours later the bones of the app were in place.

Greatest Splunk search of all time

Of all of the Splunk searches I've made over the last couple years the one I keep coming back to time after time is this

index=_internal sourcetype=splunkd deployedapplication (removing OR installing OR uninstalling) NOT "removing app at location" | rex "DeployedApplication - (?<Action>\S+)\sapp(\=|\S+\s)(?<App>\S+)" | eval Action = case(Action="Removing" , "Removing" , Action="Uninstalling" , "Removing" , Action="Installing" , "Installing" , 1=1,"Fix me") | rex "(Removing|Installing) app=(?<Version>\S+)" | eval Version = if(isnull(Version),"5x","-= 6x =-") | dedup _time host Action App Version | table _time host Action App Version | sort -_time

This search shows apps being installed or removed at agent level and is invaluable from an admin perspective. I have it saved to run in 5 minute real-time which is a good window for us as our agents only check into our deployment server every 3 minutes.