Hello. Welcome to Result Modification. I'm Jessica with Splunk Education. In this module, you'll learn how to modify result sets, manage missing data, and normalize data using commands and functions. We'll be looking at some examples using data sources from the fictional company Buttercup Games. Let's start with the appendpipe command. The appendpipe command will allow us to take existing results and push them into the sub-pipeline, meaning the results of the search specified in the appendpipe command are appended to the end or bottom of the outer result set. If we take a look at the syntax of the appendpipe command, we can see that it is preceded by a search, and then followed in square brackets by the subpipeline. The results of this search wrapped in brackets are what are appended to the results of the outer search preceding the appendpipe command. Let's take a look at an example here. Let's look at our internal network and make sure we are looking for connections to the Internet that are not business usage, and count this based on usage and username. We'll go ahead and run a search across our network index, sourcetype of cisco_wsa_squid. If we run this search and take a look at the fields sidebar, what we will notice here is that we have a field named usage with five unique values, one of those values being business. We would like to exclude those connections to the Internet that are not business related. Therefore, we are going to add usage not equal to business into our search and filter. From there, we want to get a count of events split by these four usage types and user names. We'll pipe to the stats command and get a count of events by usage and username and display that information in a table. Now, I want to know in total for each usage case how many visits there were. We can see here we have for each usage and user name combination, but I would like to see the overall picture of usage counts. What I could do is remove user name from these stats command. This is taking out the counting of permutations based on usage and user. But now I can't see which users are using which usage types more than others. I just see the overall count per usage type. How do I get that in between? How do I count this based on usage and user name and perform subtotals? Let's add username back into the stats command, and let's say that we attempt to use the stats command and get a sum of count, split that by usage. But again, my user names disappear. So what I'd like to do is I would like to take this table that we're currently looking at and append it to this original table. That will be done using the appendpipe command. I will put the appendpipe command right after the stats command. Within the appendpipe command in square brackets, specify a subsearch, which is going to begin with a stats command, and we'll sum up the values stored within the account column and split that by usage. Once I run this search, we will now see a column added to the table names, sum(count), and notice we have a bunch of empty cells here. If we move across each page and make it to the very last page, we can see the totals appear at the very bottom. This is displaying that the values are appended to the very bottom and end of this table. Now, I'd like these subtotals to appear underneath the count column. Therefore, I'm going to rename this sum count column to "count". I will do that in the stats command following the sum function. I will add the as clause followed by count. What that will do is append those subtotals to the very bottom of that table underneath that count column. Now, ideally, I would like these subtotals to appear in the right place where I have the subtotal for Borderline usage types, Personal usage types, Unknown and Violation at the end of each section. So in order for these to be rearranged within the table, I need to re-sort all of the names or rows based on usage. So what we will do here is pipe over to the sort command and sort based on usage type. Displaying the subtotals for each usage type at the end of that section, here we have the subtotal for Borderline. We will see the same thing for Personal usage types, along with Unknown and Violation. Now I see these empty cells that appear for each subtotal row, and I don't have a good label for this. This empty row that doesn't have a user name, I would like to name based on its subtotal. As we know, we are appending a table to our original table, which has a count and usage. I need to create a new field called username. Since we already have that here, that will be set to the label to fill in these empty cells so that the labels will show up underneath this column and be set for these subtotals. So what we will do in the appendpipe command is pipe on over to the eval command, set username equal to "Subtotal of usage for", and then concatenate the value of the usage field in that row. If I run the search, we will now have a label for each subtotal. I can see subtotal of usage for Borderline. I will see the same thing for Personal, Unknown and Violation. So we went ahead and added a stats and eval command to the appendpipe command. A search can contain more than one appendpipe command. So before we send off the report, we decide to add a grand total to the end of the report. Now, in order to add a grand total, this is going to require following this sort command with an additional appendpipe command that is going to search for the values of username that begin with "Subtotal of usage". And from there, we are going to sum up these subtotal count values. So what we will do is pipe to the stats command, sum count as "count" and give that a name. Before we even provide an additional eval command, let's run the search. Let's jump on over to the very last page and we will see an overall grand total at the very bottom, which is the total of all of these subtotals. I'm going to provide a label where this empty cell appears, adding an eval command to this second appendpipe command, where username is equal to the overall grand total. And if we take a look at the very bottom of our table, we can see that label appearing.
