How To Lookup A Number In Between A Range Using Map and Makecontinuous Command
Hi Guys!!!
Today we have come with an interesting trick with two commands i.e. map and makecontinuous to lookup a number between a range.
For showing you this requirement we will use one csv file which we have uploaded as a lookup file named “info.csv” in splunk and the sample data which we have indexed in “test_index” index.
Please, see the below image to see the content of the lookup file named info.csv.
Explanation:
In this lookup file, we have three fields, “start_number”, “end_number” and “location”.
Please, see the below image to see the indexed data.
Explanation:
Here, we are using index “test_index” and sourcetype “test”, where we have our sample data.
We have used the “table” command to show the values of the “number” field in tabular form.
Example: 1
Now, using the “map” command, we will get the values of the “number” field which will fall between the ranges (in between the values “start_number” and “end_number” field) of the lookup file “info.csv” and also we will get the values of additional field i.e. “location” from the lookup file.
So, let’s see the below query to get the output,
index=test_index sourcetype="test" | table number | map search="| inputlookup info.csv | where "end_number" >= $number$ AND "start_number" <= $number$ | eval "number"="$number$" | table location,number,start_number,end_number" | eval Range=start_number."-".end_number | fields - start_number,end_number | stats count, list(number) as number, list(Range) as range by location
Explanation:
First, we have used index “test_index” and sourcetype “test”, where we have our sample data.
We have used the “table” command to show the values of the “number” field in tabular form.
Now we have used,
| map search=”| inputlookup info.csv | where “end_number” >= $number$ AND “start_number” <= $number$ | eval “number”=”$number$” | table location,number,start_number,end_number”
The “map” command works as a looping operator that runs a search repeatedly for each of the input events or results.
map search=”<string>” [This is the syntax of map command]
In the place of string, we have to write the query which we want to run as an ad hoc search to run for each input of the resultset.
Here we have used, “| inputlookup info.csv | where “end_number” >= $number$ AND “start_number” <= $number$ | eval “number”=”$number$” | table location,number,start_number,end_number”
| inputlookup infor.csv -> To get the content of the lookup file “info.csv”
| where “end_number” >= $number$ AND “start_number” <= $number$ | eval “number”=”$number$” -> This will only give the values of number field which is greater than the values of “end_number” field and less than the value of “start_number” field. Now to get these values in a field named “number” we have used, | eval “number”=”$number$”. And, then we have just “table” command to get the “location”, “number”, “start_number”, “end_number” fileds’ values in tabular form.
| eval Range=start_number.”-“.end_number -> This will create a new field named Range where we can see the values of the “start_number” field and “end_number” field concatenated with “-”
| fields – start_number,end_number -> This will exclude the “start_number” field and “end_number” [as, we don’t need to show these two fields in the result].
| stats count,list(number) as number,list(Range) as range by location -> This will derive the count and the list of values of the number field and Range field grouped by the field location.
As, you can see, we are getting only the values in the “number” field which fall between the ranges in the “Range” field created from the lookup file (info.csv).
Example: 2
Here, we will show you how to achieve the same thing using “makecontinuous” command. So, let’s start,
index="test_index" sourcetype="test" | table number | eval aa="aa" | append [| inputlookup info.csv | table start_number,end_number,location | eval Range=start_number."-".end_number | makecontinuous start=0 end=50000 start_number | filldown end_number,location,Range | where start_number<=end_number | rename start_number as number ] | stats values(*) as * by number | where isnotnull(aa) | fields - end_number,aa | where isnotnull(location)
Explanation:
First, we have used index “test_index” and sourcetype “test”, where we have our sample data.
We have used the “table” command to show the values of the “number” field in tabular form.
Then, we have created a field named “aa” which will contain the value(string) “aa” using the “eval” command.
Then we have used,
“| append
[| inputlookup info.csv
| table start_number,end_number,location
| eval Range=start_number.”-“.end_number
| makecontinuous start=0 end=50000 start_number
| filldown end_number,location,Range
| where start_number<=end_number
| rename start_number as number]”
“append” command we used to append the two queries together. So, let’s see how we inside this query we have used, “makecontinuous” command to get the requirement.
| inputlookup info.csv -> This we used to get the content of the lookup file named “info.csv”.
| table start_number,end_number,location -> This will show the values of “start_number”, “end_number” and “location” field in tabular format.
| eval Range=start_number.”-“.end_number -> This will create a new field named “Range” where we can see the values of the “start_number” field and “end_number” field concatenated with “-”
| makecontinuous start=0 end=50000 start_number -> “makecontinuous” command will fill in the gaps we have in the start_number field by continuing the numbers by incrementing.
[start, end ], set the range or minimum and maximum extents for the values of the field we will use with the command. The data which falls outside of the [start, end] range will be discarded.
| filldown end_number,location,Range -> This will fill the null values in “end_number”, “location”, “Range” fields with the last not null value for corresponding fields.
| where start_number<=end_number -> This will show the results only where the values of the “start_number” field are less than or equal to the field values of the “end_number” field.
| rename start_number as number -> This will rename the “start_number” field as “number”.
| stats values(*) as * by number -> Then by the “stats” command we have sorted two-three fields by the field “number”. So that, the rows which have null values for “end_number”, “location”, “Range”, “aa” will be discarded.
| where isnotnull(aa) -> This is to discard all the rows where the value of the “aa” field is null.
| fields – end_number,aa -> This is to discard the “end_number” and “aa” fields.
| where isnotnull(location) -> This is to discard all the rows where the value of the “location” field is null.
As you can see, we are getting only the values in the number field which falls between the ranges in the “Range” field created from the lookup file (info.csv) and we are getting the corresponding “location” field values.
Example: 2.1
Now, if you want to get the results or values of “number” field which does not fall between any range of the “Range” field, you can use the below query,
index="test_index" sourcetype="test" | table number | eval aa="aa" | append [| inputlookup info.csv | table start_number,end_number,location | eval Range=start_number."-".end_number | makecontinuous start=0 end=50000 start_number | filldown end_number,location,Range | where start_number<=end_number | rename start_number as number ] | stats values(*) as * by number | where isnotnull(aa) | fields - end_number,aa | where isnull(location)
Explanation:
The explanation of this query is the same as Example: 2, only one difference is there i.e. the last command “| where isnull(location)”, which means we only want to see the rows which contain “null” values for the “location” field.
If you see the above image, you will understand we are only getting the values of the “number” field which don’t fall between any range of the “Range” field.
Happy Splunking !!
