By Martin Hettervik, Senior Consultant and Team Leader at Accelerate at Iver, Splunk MVP The stats command is commonly used and well known. The tstats command is also well known, but for many only associated with searching data models. What if I told you that tstats can be used to gain the performance benefits of searching in tsidx files, without even using accelerated data models? In this post, we’ll explore how to super-optimize your Splunk searches using tstats, TERM, PREFIX, and a deeper understanding of how Splunk handles data under the hood. How do stats work, and why it’s not always optimal The stats command works by retrieving raw data from the indexers (stored in compressed journal files), performing relevant field extractions at search time, discarding the leftover raw data, and formatting a table. The flow is illustrated in the diagram below.   As an example, you could search WinEventLog and create a table of most common computer names. In the picture below, the raw data that you would have so dig through is on the left, while the table you end up with is on the right. As you can see, most of the data in the raw event is not needed. Searching through all that unnecessary text creates longer search times and is not always optimal. What alternatives do we have for optimization? There are some commonly used tricks in Splunk for creating faster searches. Let’s have a quick look at some of them, and pros and cons for each. Accelerated data model Good if your search can utilize data that maps to standard CIM data models Can be rebuilt, if changes in data or fields Complex to create a new data model for a small single use case, e.g. a single dashboard Add continuous resource usage 24/7 Summary index Easy to understand and set up Not flexible for later changes in data or fields Add continuous resource usage 24/7 Accelerated report Easy to set up Not reusable for other searches Add continuous resource usage 24/7   One thing in common with all options above, is that they add continuous resource usage on Splunk. They run regular background jobs to create “summarized” data. This is not a problem if several searches can use the same summarized data, or if the searches are expected to run often, so that it’s worth it performance wise. However, for a single dashboard or report that is only expected to be used occasionally, the cost in resource usage could become higher than the value added by the search optimization. To avoid this continuous cost, can we use tstats directly on the data? Yes. The tstats command only searches in indexed metadata (tsidx files), not raw data. Even so, you don’t necessarily need indexed fields or an accelerated data model to use tstats, it depends on the minor and major breakers in the raw data. Learn how to use TERM and PREFIX together with tstats for amazing results. Let’s look into it! Enter tsidx files and breakers The tsidx files contains indexed fields and segmented keywords (term) from the raw data. The keywords points to the events in the raw data where the fields or keywords are present. This mechanic is used by Splunk under the hood by for search optimization. The diagram below illustrates how the pointers work. What segmented keywords ends up in the tsidx file is dependent on major and minor breakers in the raw data. Major breakers separate keywords. Examples on major breakers are space, tab, brackets and quotation marks. Minor breakers create different segment combinations from keywords. Examples on minor breakers are period, slash, colon and the equal sign. You can look up all the breakers in the Splunk documentation. As an example, se the event below, that shows how a key value pair in the raw data is stored as keywords in the tsidx file (notice the minor breakers). So that means we can use tstats directly raw data? In some cases, yes! Here is where the magic of TERM comes into play. Use TERM to encapsule keywords in tsidx files containing minor breakers. So, instead of writing a normal stats search like this: index=dc_logs source=WinEventLog:Security ComputerName=win-hp-25431 | stats count Try writing a tstats search like this: | tstats count where index=dc_logs source=WinEventLog:Security TERM(ComputerName=win-hp-25431) Note how we do not need to use TERM on the fields index and source, since they are already indexes fields. For the computer name keyword, we need to encapsule the key value pair in TERM because it contains minor breakers. How about if you wanted to create a timechart? The timechart command has the property that it adds empty time slots to the results (useful in visualizations), which is omitted by using simply stats. Can we re-create this effect with tstats? Yes. Instead of writing a normal timechart search like this: index=dc_logs source=WinEventLog:Security ComputerName=win-hp-25431 | timechart span=1d count Try writing a tstats search like this: | tstats count where index=dc_logs source=WinEventLog:Security TERM(ComputerName=win-hp-25431) by _time span=1d | timechart span=1d count Sweet! How about if we want to count by a field? Is this also possible with tstats? Indeed, we can use something called PREFIX. Instead of writing a stats search like this: index=dc_logs source=WinEventLog:Security | stats count by ComputerName Try writing a tstats search like this: | tstats count where index=dc_logs source=WinEventLog:Security by PREFIX(computername=) (Note that PREFIX always uses lowercase in the encapsulation.) Super nice, but when will using tstats not work? This trick by using tstats combined with TERM and PREFIX will not work if there are major breakers in the key value pairs in the raw events. Look at the example below. Note that the logon account is separated between key and value by a space (major breaker). We will therefore not find the key value pair “logon account: pburdytt2i” in the tsidx file. We will however find the username “pburdytt2i”, but cannot know the key context in which the use name exists. Unfortunately, this tstats trick only works if the data is formatted in the “correct” way, but when it does work, the efficiency boost is huge! Final notes Optimizing your Splunk searches with tstats, TERM, and PREFIX can dramatically improve performance and reduce resource usage in Splunk. While not every search can be converted, understanding how Splunk segments and stores data gives you some powerful knowledge to make faster and more optimized queries. If you haven’t done so already, have a look at your dashboards, reports and alerts, and see which searches you can super optimize. Happy splunking! ... View more

We have different lookup inputs into the Splunk ES asset list framework. Some values for assets change over time, for example due to DHCP og DNS renaming. When an asset gets a new IP due to e.g. DHCP, the lookup used as input into the asset framework is updated accordingly, but the merged asset lookup "asset_lookup_by_str" will contain both the new and the old IP. So the new IP is appended on the asset, it's not replacing the old IP. Due to "merge magic" that runs under the hood in the asset framework, over time this creates strange assets with many DNS names and many IPs. My question is, how long are asset list field values stored in the Splunk ES asset list framework? Are there any hidden values that keep track of say an IP, and will Splunk eventually remove the IP from the asset in the merged list? Or will the IP stay there forever, and these "multivalue assets" will thus just grow with more and more DNS names and IPs until the mv field limits are reached? And, if I reduce the asset list mv field limits, how does Splunk prioritize what values will be included or not? Does the values already on the merged list have priority, or does any new values have priority? Tried looking for answers in the documentation but could not find answers on my questions there. Hoping someone will share some insights here. Thanks! ... View more

These dashboards are part of an app i made, to visualize Nessus security scans i Splunk. The idea is somewhat inspired by the existing Tenable App for Splunk from Tenable, but I wanted to to take the visualizations to the next level, and make the data easier to understand and navigate. The first dashboard is an overview dashboard. The picture below does not show the whole dashboard, but you get the point. It shows data from all vulnerability scans, with color coding differentiating the level of vulnerability severity. It's an easy way of seeing which environments and hosts have the most vulnerabilities, and see which type of vulnerabilities are most widespread. Also note that it shows what period there are scan data from (which might not be the same as the time picker) and how many networks have been scanned (out of the total number of networks). This next picture shows one of many drilldown dashboards in the app. It allows for a more detailed view of vulnerabilities per host, and also the possibility to get more information about a specific host if you click on the top table. This table also uses the same color coding as the overview dashboard. The bottom table links directly to the Tenable website, with more information about the specific vulnerability ID clicked on. All dashboards allows for various types of filtering, or example only show vulnerabilities with a minimum severity, e.g. at least medium. The dashboards are also utilizing the Splunk ES asset list to get more information about the hosts, so that it's possible to sort on vulnerabilities per business group or environment, among other things. Also, there is a lookup of "ignored vulnerabilities", for which the users can add vulnerabilities to ignore them in the dashboards, e.g. by editing it in the Splunk App for Lookup File Editing. Summary of functionality used in the dashboards: Color coding of vulnerability severity Drilldowns to other dashboards with more detailed information Drilldowns to external URLs with information on severity IDs Various filtering options on the dashboards Host enrichment from Splunk ES asset list Dynamic whitelisting of vulnerabilities through lookup file Correlation with other sources to show meta-information about vulnerability scans ... View more