Splunk : Discussion on "Subsearches"

okay in this video we will talk about sub searches in Splunk okay so sub searches are really important topic in Splunk through which we can we can achieve lot of stuffs we try to understand what are them and how to construct a sub search what are its limitations and and all the stuffs okay now to start with what first we will do is we will try to understand what is what is this sub search now what is the soft search so a sub search is basically a another search within a primary or outer search that means you will you will write a Splunk search right now inside that search you will be writing another search within a square bracket within within a square bracket and then that that search will execute and and then we'll see like how it works over there okay so it's basically a search within within a primary or outer search now a sub search look for a single piece of information that is then added to there as a criteria or argument to the primary search so suppose we sometimes we write a search like index equals to some index name then some field name let's say field one equals to fill some values right then field two equals to some other values right so those are the information or the criteria or the argument we are adding to that do that search right now those arguments you can dynamically generate as well using using its officers that's the primary reason behind using yourself search because we will not know those arguments are those filter conditions beforehand it will be always changing right so that's how you will be generating those arguments dynamically okay that that's the reason we'll be mainly using sub search now that as I told like we use sub search because the single piece of information that we are looking for is is dynamic okay we'll see examples as well for this one now a typical sub search looks something like this one if you see this is our initial search right so this is the main search or outer search if you see the the not highlighted portion is our right now inside that search inside the square bracket we are actually executing another search which is basically finding out the top client IP okay this is the topmost client IP which is purchasing from from let's say buttercup the game's website right as the Splunk tutorial later we'll be using today as well okay so basically we are trying to find it out a client which has purchased most number of products from from the website over there okay so if you see this this client every day or every every hour it will be changing right because a single person cannot be that most top buyer from your website right so that time in this case is right we'll be using sub search to determine which is the top client now and based on that we will be we will be passing that to the main search to choose further searching that one right now similar example I can give like suppose you want to know which host is most busiest or for a particular hour right so that will be also changing every hour right so that those scenarios we we should be using sub search in this cases okay now let's move on now how to construct a sub search so the first thing is sub searches must be enclosed in the square brackets as I told in the primary search so we must have a command after the pipe and the before the before the sub search okay now when a search contains the serve search the soft search always run first to provide the data to outer search that means sub search is basically returning something to the outer search now from the outer search to inside the surface or it is not possible it's the other way around like from the soft search we are we are basically passing some value to the main searches over there okay now the first command of the sub search has to be a generating command like search even count input lookup or T stats there are a lot of generating commands are available right so I created videos for general different kinds of command in in Splunk right different types of commands in Splunk so generating command has to be the first command in s of we have to remember that one while constructing the soft search now regarding time ranges and the sub searches like time ranges in the outer search time range is specified in the outer search like we can always mention the time raise like earliest equals to earliest time and logistical status time right if you provide that time ranges in outer search that will not be applicable to the to the sub searches okay now similar way if you specify the time range in the serve search it will not be applicable to the two that outer outer searches over that okay so it is only applicable to the to the sub searches now if you run a query which is having a self search and you do not specify in the specify the times by using that earliest and latest okay and but you are selecting the time range from the time picker then it will automatically apply to the outer search as well as sub searches both okay so we we have to understand this one as well now we talked about how to construct a sub search now let us try to see when we should be using using sub searches right so one of the things I already told when you want to parameterize the outer search right now to return something dynamically that that piece you are returning to that outer search is dynamic in nature right so in those cases we need we need the sub searches right so using the output to using the output of another search so basically parameterizing one search using the output of another search okay now run a separate search and add the output to the first search using the append command now if you see there are a lot of commands which are which support sub search is like append happen calls or or let's add join or let's say for each those commands are basically if you if you see my previous videos on those commands they have a self search as well in inside them right so they actually takes a subset input over there okay so in those cases also we can we can use sub searches okay so sub searches are used with commands like join depend upon calls for each etc now this is important one not all the commands supports sub search as in as an input love suppose let's say we have a command something like this one source type equals to something and then multi kv now we cannot use a soft search with this particular one because this is the transform it transformation command right so if you use try try to use a sub search after that it will not take because the multi kv command is does not expect yourself search engine has come argument over there okay it's mostly used when you want to search something okay accomplished within the search now there are certain commands as I told which such as append join that can accept a subset in those cases we can definitely use sub searches over there okay now in terms this is this is the technical implementation and in terms of functionality as I told like when you want to generate a piece of information dynamically we will be using sub searches over there but while doing that we have to remember this technical limitations as well over there okay now now there is a possibility to use multiple sub searches as well okay for in a particular in a particular search query now let us take and take an example over here okay so let's say I have a search something like this one index equals to foo some error and I kept it like this one because we will have sub searches over here so that's why some stats count now inside this search let's say I am using another search like this one search index equals to bar success some data it will be returning the data to this to this outer search okay now let's see inside this search again we have another search like searching this equals to some one same thing else stats count okay so in terms of as I told search sub searches are run first in a search query right now when you have multiple sub searches and that two like nested one this is the nested one right so so in these cases generally the most inner searches will be running first so so this this particular search will run first which will basically compute something and return something over here okay based on that return value this search will be running now okay now these two are sub searches already done now the main searches will be running over here okay based on that value written by this by this sub searches okay so this is the order of the running of the searches so this is this happens when our sub searches are nested now let's see what happened when our sub searches are sequentially ordered like this one this example over here I have a query something like this one then I have a search after the pipe I have a self search after the pipe I have another sub search over here okay in this case it always tried to run from left to right the sub search is now we have two sub searches over here right so the first sub searches will be running this guy the second searches will be running this guy then our main search will be running over here okay this is Vida this will be the order of the running of the searches okay now let us take couple of example over here so for that we will go to our Splunk enterprise okay so for today we will be using the Splunk tutorial data okay so I will be providing that link for Splunk you drill it as well I just index the data in my in my main index so let us do for all time because I think couple of days back only I ingested this data okay this is very simplistic data we have ww secure data access combined and vendor sells data right so I only only index the tutorials dot zip file now for today's demo we'll be using this access combined data okay now because we want to understand the client which is which has purchased the most number of products over here okay so for that I will choose X and equals to purchase over here okay now as it is access combined data right so the status we have to check it as 200 because this is the success over here okay so you have total 5 triple 2 for now based on when we will be doing that this this number will be changing because that tutorial later there Splunk always changed that one so this this may be different or in your case okay now we know we have all our events over here now let us first try to see without the soft search how we can do this one okay now if I just try to understand which is the topmost client IP what I will do I just write top limit equals to one client IP right so this will give me the top client IP over here and how many how many times he has purchased over here okay so if you see he has purchased 134 times now now if I just wanted to know what are the product IDs he has purchased or the product he has purchased what I will do I will just try to let's say I will just try to create another search over here okay with this one so with the same criteria where it has to be the purchase and the status should has to be 200 over here we have a field called product ID over here right but I I just wanted to know my top client purchased products right so that means I have to pass the client IP over here right so client IP equals to this guy correct because this is my top client so I know what are the what are the things what are the events related to that guy now I can use our stats command right let's say this is the count and then how many if I just wanted to say this how many distinct product ID okay how what are the different product IDs that guy purchased I will be writing this this kind of query right very simplistic way so this is the total count is the distinct products and these are the different product IDs he has purchased over here now if you see this approach we have to write two to get whole picture we have to write two queries over here the first query will be giving me the top client which will be always dynamic right and after finding that client IP we are passing that client IP over here in the second search then getting the data desired rate over here right now think about this one when you try to generate a report like this one it will not be helpful in this case there is a need that we will be combining this two queries together somehow now sub searches are rescuing us from from this this type of cases over here okay so what we will do we will just take the client IP let's say we'll just say table client IP over here okay from this search so we'll have only a client IP field over here I'll just copy this search over here okay so instead of this one okay client IP equals to some some IP over here I will be using a sub search over here the sub search will be returning the same stuff over there we will reason through it how it will how will return as well okay so let us first run this one and see whether we are getting the similar kind of result or not so I'll just try to run this in a separate window so that we will we can compare okay okay I think I copied till that point okay so let us try to run it now if you see it is saying unknown search command because when inside the sub search the first command has to be a generating command so that's why I am writing a search over here okay it has to be for all time now if you see it generated the similar output over here so this sub search is basically returning basically returning if I just do the control Z over here this guy over here write client IP equals to something over here so that is the main fundamental of the sub search over here if you see we achieve lot of stuff not only we are dynamically generating something we actually code relating to different searches over here as well right if you are from cql background you may know in Raquel or any or any other sequel tool we can write it quite like select star from table one where some ID in select star from other table ID equals to something right so we can basically fetch the ID values from the other table and pass it as an input to the Quoddy as well right so this is the same stuff we are doing it over here as well using a soft search okay now let us try to understand how how it works how how this guy this this sub search is able to return something something like this one okay so for that we need to understand one thing there is a command called format which I think I still not discussed in my channel I will be discussing very soon that one okay if I if I list that say if I just run this command over here format okay if you see it created a new field called search okay and with with this kind of input over here which is very similar to whatever we are seeing it over here right so sub searches are always called this format okay after them this is this is the by default you do not need to mention that format over here it will always call that one okay then that's why this format will create something like this one and that will be that will be passed over here as as this one okay now there is a there is a lot of format options over here related to sub searches which we'll be seeing now okay so for that let us first try to see what if we have more than one value more than one field over here okay so for that I will just remove this top top command let's say we will work with we will work with two columns let's say client IP and session IDs this this a session ID nothing like no functionally related stuff I'm just taking two columns over here just to show you how how format works okay and we have multiple rows over here previously we we are passing just single client IP right now we have multiple rows over here now if I just call this format command over here if you see it created a single single field over here single value field over here right and which is nothing but for each and every row it is creating something like this one J session ID equals to something and client IP equals something or this is the second row right this is an ID equals to something and client IP course or something or for all these rows it has created this particular structure so it will return it will return something like this one this whole string will be returning to the outer search okay so so that that's the reason there is a technical reason how how sub searches are passing values to the to the outer searches okay now in format there are there are lot of options available okay now there are two special options are available called if you have a field name okay with fill name of let's say search or query it behaves something different way okay so for that what I will do is 175 44 330 this has this has to I think it has multiple session IDs we will we'll just filter out this this with this particular client okay so let's let's do that one search client IP equals to this one okay so let us first try to see our data okay it has it has multiple data now let's let's say I will just try to again filter with another a for a particular j session ID over here okay so we from the from this particular search from this particular search we we have we have this this kind of data over here okay so now let's let's read up it so that we will just get a single row over here okay now if I just try to use the normal format okay so it will it will be something like this one as we as you have already known right so each and every row it will be for all the different different fields it will be and it will be using and operator for different different rows it will be using all of our operator over here right now if I rename one of this field let's say J session ID to search okay this is a very a shell case let's say rename J session ID G session ID as search okay so now if you see this the difference if I just duplicated it will be making more sense because we will be comparing this one okay here here we will just keep it as is the normal normal format okay and over here we are using this one with search so when I rename to this particular field as a search if you see it will remove the fill name from here as well so this type of things are helpful when you want to do some validation or you want to pass some static value to your to your search this type of things are helpful over here right now this you can do with query as well like you can rename as either search or query to achieve the same stuff if I just if I just rename it to query it will be it will be providing me the similar output over here okay so now similarly if I just let let us see what happens if I just rename this client IP as okay so in this case if you see it also removes the client IP filled him over here so you'll be passing something like this one to the outer search for for the searching purpose okay now let me let me give you an example over here how how it will be helpful now let's say like till this point okay without renaming it this one we will we will pass it to the we will use it as a first sub search over there okay so instead of this this client IP will be using will be using a sub search over here okay so this one we will just remove these stats we will just try to see how how it is working first so we have to be search over here okay so it's it's returning me six rows for for that particular if I just see client IP and for that particular J session ID over here okay now let's say I I create another another field over here after deed up let's say I created another field called eval okay find equals to let's say I just wanted to find 470 over here okay so I'll just sum write something like this one all the events which are having 470 over here okay so this soft search will have another field called find over here now I just wanted to find only for the 470 because 4/7 is not a field value over here right so that means I have to rename that that fine field as search or rename or query we may find as query over here okay so in this case if you see it over here it only finding out the events which are having 470 over here okay so in this type of scenario you can you can use this particular feature renaming the two either either fine either query or search we wait like in in future videos when we will talk about the return command right so we will see and an implementation of this particular feature only in it and command as well okay now if you see it over here I when I use this particular search as a sub search I did not I did not use a format command over here because for sub searches the format command will be automatically invoked okay so now let us go back to our PPT again just try to find out what are the other stuff related to the soft searches so we have we have seen sub searches example as well we have seen the usage of format command with sub searches as well now let us talk about the performance considerations of the searches because sub status has performance impact if the number of events written by the sub search is very high okay so a sub search can be a performance strain if the search returns a large number of results that's the reason Splunk always limits the maximum results returned from the sub searches to ten thousand results again it will be based on that which command is invoking the sub searches because if you see sub searches can be used by the append command and join command as well right so those commands have their inbuilt input which can overwrite this particular this particular limit as well okay and sub searches has to be completed within within a 60 seconds of run time otherwise it will automatically finalized okay and Splunk will be silently removing those rows which which are which are not present in the result but I really should be presenting over there okay so have to remember this one now it may happen that for our case we have to exceed this limit right now we have couple of choices over here like either we can rewrite the query to limit that one to keep in this limit or we can use the format command as you have seen like when I invoke the format command for multiple rows it created a single row over there right so you can use those type of feature as well okay to limit your number of rows okay even still if if these two things are also not possible from your end so in Splunk limits conf also there are couple of configurations like max out max time and TTL right the max out is that that 10000 limit max time in that 60 second limit and TTL is the time to cash a given search result in seconds okay so you can play around with this one but this also changing this one also may have some performance implications and there are certain comments over here you should be careful reading this one and changing this one okay but ideally we should be playing around with the search level as only okay instead of keys of changing this this particular settings over here but if it is not not at all achievable by using the search maybe we can we can then think about playing around these things okay let's move on now as I already told sub searches can be used to correlate the events as well and for their I have given an example right week from from a sequel world we can write it query something like this one select star from some table where fill the value in selects fill value from other tables right now if I just try to make it as a as a sub search over here okay so then we can use something like this one source type because something search source type equals to another source type some field value over here you do not need to use this format command over here I just kept it just to remind you over here but this is this is this portion is definitely an optional part over here okay and and and if you if you just need to return only the values in without the field name you can always rename that field with either search or query over there right then it will be it will be automatically sending that field values without the field em over here okay so hopefully this video is helpful so whatever we we try to understand over here is different aspect of sub searches its limitations and how we can use it technically as well as functionally over there okay hopefully this video is helpful see you in next video

Channel: Splunk & Machine Learning

Views: 8,390

Keywords: splunk, how to, subsearches

Id: TiwKp-T56xQ

Length: 27min 57sec (1677 seconds)

Published: Tue Jan 14 2020