Splunk’s Search Processing Language (SPL) provides many commands to correlate data. When it comes to combining the results of two different datasets which can’t both be queried in the initial search, users may be most familiar with the append, appendcols, and join commands. While powerful, these come with the costs and limitations of using a subsearch.
But what if you want to efficiently combine the results of two searches which use the same base search, without the expense of searching that data twice? This can be easily accomplished through the lesser-known but highly valuable appendpipe command. This article provides an overview of the Splunk appendpipe command, its syntax, usage, and examples to help you integrate it effectively into your Splunk queries.
Understanding the appendpipe Command
Benefits of Using appendpipe Command
- No additional search job: Every search job running in Splunk is allocated a core; there is a limit on how many can run at one time. Since appendpipe doesn’t expend an additional core, it will not withhold that resource from other ad-hoc or scheduled searches, resulting in lower search concurrency and potentially fewer skipped searches.
- No subsearch limits: Because appendpipe uses a subpipeline instead of a subsearch, it is not limited to returning the usual 10,000 results for general subsearches, or 50,000 results for the append, appendcols, and join commands.
- No re-searching the same data: Just like using a base search and a post-process search in a dashboard, appendpipe allows you to transform the results of an initial search any number of times, and still have the original result set at hand—except it’s all in one search!
Proper Command Syntax
<primary search>
| appendpipe [<subpipeline working off of primary search’s results>]
Command Usage
Use Case #1: System Performance Report
Create a report of all systems’ average CPU and memory usage percentages, ending in total averages across all systems.
SPL query:
index=system sourcetype=cpu_usage
| eval mem_percent=mem_used/mem*100
| stats avg(cpu_load_percent) as avg_cpu_pct, avg(mem_percent) as avg_mem_pct by dest
| appendpipe
[ stats avg(avg_cpu_pct) as avg_cpu_pct, avg(avg_mem_pct) as avg_mem_pct
| eval dest="ALL SYSTEMS" ]
| eval avg_cpu_pct=round(avg_cpu_pct, 2), avg_mem_pct=round(avg_mem_pct, 2)
avg_cpu_pct | avg_mem_pct | dest |
|---|---|---|
32.04 | 12.28 | alpha |
99.10 | 83.54 | beta |
62.95 | 30.77 | gamma |
64.70 | 42.20 | ALL SYSTEMS |
While the addcoltotals command can be used for adding a sum total row to search results, it cannot perform any other aggregate functions. Through the appendpipe command, we can add a row for the overall average values for all systems.
Use Case #2: Monitor Network Activity
Generate a summary detailing the volume of various network activities handled by a specific device.
SPL query:
index=web sourcetype=access_logs dest=alpha
| stats sum(bytes) as total_bytes by action
| addcoltotals labelfield="action" label="all"
| appendpipe
[ stats count
| where count=0
| rename count as total_bytes
| eval action="all"]
| eval total_gb=round(total_bytes/1024/1024/1024, 2)
| fields - total_bytes
total_gb | action |
|---|---|
0 | ALL |
Since our initial search produced no results, addcoltotals has no effect; there are no results for the command to apply to. We can use appendpipe here to ensure an aggregate row is returned even when there are otherwise no results, without overwriting results when they do exist.
If “alpha” were sending network data, our results would look more like this:
total_gb | action |
|---|---|
12.49 | allowed |
2.05
| blocked |
0.33 | teardown |
14.87 | ALL |
Use Case #3: Current Splunk Cloud SVC Usage by User
Identify today’s most expensive users in your Splunk Cloud environment, as well as how many SVCs are yet unused.
SPL query:
index=summary source="splunk-svc-consumer" svc_consumer="search" earliest=@d
| stats sum(svc_usage) as svc_usage by search_user
| appendpipe
[ stats count, sum(svc_usage) as total_svc_usage
| eval total_svc_usage=coalesce(total_svc_usage, 0)
| appendcols
[ search index=summary source="splunk-svc" earliest=@d
| stats max(stack_license_svc) as svc_limit ]
| eval search_user="Unused", svc_usage=if(svc_limit>0, svc_limit-total_svc_usage, 0)
| fields search_user svc_usage ]
| sort - svc_usage
This advanced query utilizes both appendpipe and appendcols to fetch the SVC limit and calculate the number of unused SVCs.
- First, we get the total usage per user. This is the data the appendpipe subpipeline will be working with.
- Within the subpipeline, we sum all usage with the stats command. The `count` aggregate is included to ensure we do not end up with no results within the subpipeline. When this happens, we can then simply coalesce total_svc_usage with `0` to populate the field.
- The appendcols command is utilized to fetch the environment’s total SVC allowance from a separate dataset, and add it to the same row as a new field.
- Next, we label the row as user “Unused”, and calculate the remaining SVC allowance, if a limit exists.
- The subpipeline returns its single result with the search_user and svc_usage fields, and appends it to the initial result set.
- Finally, we sort the results from highest to lowest usage.
Conclusion
The appendpipe command is a powerful tool that enhances the capabilities of Splunk queries. It allows for seamless integration of additional data manipulation and aggregation steps within a single search job.
With appendpipe, you can:
- Easily add aggregate rows to search results, such as overall averages or totals.
- Handle scenarios where initial searches produce no results, ensuring consistent output.
- Avoid searching the same data multiple times when it needs to be transformed in multiple different ways.
By leveraging the capabilities of the appendpipe command, we can unlock new possibilities and take our analysis to the next level!
To access more Splunk searches, check out Atlas Search Library, which is part of the Atlas Platform. Specifically, Atlas Search Library offers a curated list of optimized searches. These searches empower Splunk users without requiring SPL knowledge. Furthermore, you can create, customize, and maintain your own search library. By doing so, you ensure your users get the most from using Splunk.
