Splunk Search
Highlighted

## Loop to select a Range of values and display.

Explorer

Hi Team,

I am trying to design a query here, i have a list of vales as below

the requirement is that i wanna all the values that are in between same combination of A & B

for example if "value A"="-11" & "Value B"="request" then the query should check for the same values if it finds A=-11 and B= request the loop should exit and display the O/p as below

-11 request - (should display the 1st combination )
-10 bag
-13 ball

so can any one help me designing a query for the above case

Regards,
Vigneshprasanna R

Tags (5)
Highlighted

## Re: Loop to select a Range of values and display.

Legend

@Vigneshprasanna please add more context to your question. What if there is a third match `Value A = -11` and `Value B=request`

In your example there is another set `ValueA=-10` and `ValueB=bag`. Should that give `-10 bag, -13 ball, -11 request`?

| eval message="Happy Splunking!!!"

Highlighted

## Re: Loop to select a Range of values and display.

Explorer

@Nikentnilay

your understanding is correct 🙂 that's what i really want.

if the value A=-10 and value B=bag it should return
-10 bag,
-13 ball,
-11 request

if there is a third match of value A=-11 and Value B="request" it should return as below
-11 request
-10 bag

so at the end the data will be like sets

1st set for the match -11 & "request " will be
-11 request
-10 bag
-13 ball
2nd set for the match -11 & "request " will be
-11 request
-10 bag

so on it will go ..............

when we click on the set one it should display all the values of the set 1 as above same way with set 2 etc ..

thanks for the support in advance 🙂

Regards,
Vigneshprasanna R

Highlighted

## Re: Loop to select a Range of values and display.

Explorer

@niketnilay,
Ya your understanding is perfectly correct, it has to display one set when i choose the couple of value A and B
if it is A= -10 and B = bag it will display
-10 bag, -13 ball, -11 request

Thanks in advance, for the support

Regards,
Vigneshprasanna R

Highlighted

## Re: Loop to select a Range of values and display.

Legend

@Vigneshprasanna, I am not sure of your loop requirement. But try the following run any where example. Seems like you want to select value for ValueA and ValueB based on which your search should return correlated data.

The `| gentimes` till `| mvexpand ValuesB]`, generates dummy data as per your question. The outer query using map command is in place to get the earliest time for selected ValueA and ValueB ( as correlation needs to start with this values). You can use a dummy search to set the token using Search Event Handler which would be better (less complicated in terms of SPL and better performance as compared to map command.).

The inner search uses streamstats to group selected ValueA and ValueB. In the below example I have used `| search ValueA=-11 ValueB="request"` and `reset_before=\"ValueA=-11 AND ValueB=request\"`. I expect these values to come from Dropdown selection and you can test by changing the values to `-10` and `bag` respectively.

``````| gentimes start=-6
| eval _time=starttime
| fields _time
| reverse
| appendcols
[| makeresults
| fields - _time
| eval ValueA="-11,-10,-13,-11,-10,-11"
| makemv ValueA delim=","
| mvexpand ValueA]
| appendcols
[| makeresults
| fields - _time
| eval ValueB="request,bag,ball,request,bag,request"
| makemv ValueB delim=","
| mvexpand ValueB]
| search ValueA=-11 ValueB="request"
| dedup ValueA ValueB
| eval Time=_time
| map search="
| gentimes start=-6
| eval _time=starttime
| fields _time
| reverse
| appendcols
[| makeresults
| fields - _time
| eval ValueA=\"-11,-10,-13,-11,-10,-11\"
| makemv ValueA delim=\",\"
| mvexpand ValueA]
| appendcols
[| makeresults
| fields - _time
| eval ValueB=\"request,bag,ball,request,bag,request\"
| makemv ValueB delim=\",\"
| mvexpand ValueB]
| eval selectedTime=\$Time\$
| eval originalTime=_time
| where originalTime>=selectedTime
| streamstats count by ValueA ValueB reset_before=\"ValueA=-11 AND ValueB=request\"
| stats dc(ValueA) as countValues list(ValueA) as ValueA list(ValueB) as ValueB by count
| search countValues>1
"
``````

| eval message="Happy Splunking!!!"

Highlighted

## Re: Loop to select a Range of values and display.

Explorer

@niketnilay a small clarification if suppose i have AUDITTIME "24/4/17 12.00" with the value A=-10 and value B=bag and AUDITTIME with next value of A=-10 and value B=bag is 24/4/17 12.30

is it possible to get the values between the AUDIT_TIME 24/4/17 12.00 and 24/4/17 12.30 sequentially ??
hopefully this should also return the same value we are looking for ..

like
24/4/17 12.10 -10 bag,
24/4/17 12.15 -13 ball,
24/4/17 12.20 -11 request

Thanks for the great support 🙂

Regards,
Vigneshprasanna R

Highlighted

## Re: Loop to select a Range of values and display.

Explorer

@niketnilay

For the below set of sample data i have modified your query, i dont know where i went wrong im not getting the output 😞

please correct me where im wrong

2018-05-14 14:25:00,093 INFO STDOUT 14:25:00,093 [com.xxxxxxx.xxx.conn.aoo.invok] INFO - APP Request [ eq.SELECT,,***********//DTA,AA.AA.AA.AAAA,@ID:EQ=DTA ]
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.beans.xxxxxxxBean Transform - Completed server response transform. Took 31 ms.
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet 123-132-0-23-0
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet
2018-05-14 14:25:00,171 INFO STDOUT 14:25:00,171 [com.xxxxxxx.xxx.conn.aoo.invok] INFO - APP Request [ ,," 123145353" ]
2018-05-14 14:25:00,296 WARN org.apache.tomcat.util.http.Parameters Parameters: Invalid chunk ignored.
2018-05-14 14:25:00,311 WARN org.apache.tomcat.util.http.Parameters Parameters: Invalid chunk ignored.
and the above sample data is parses as
Fields
Regular Expression
^(?P[^,]+),(?P\d+)\s+(?P\w+)\s+(?P[^ ]+)\s+(?P[^ ]+)\s(?P.+)

In the above data i have mapped THREADNUMBER :"http-xxxxxx%xx.123.123.123-800-8" As value A and AUDIT_DATA: "APP Request" as value B

so now im trying to print the below events as they are the values between the same values combinationa of A & B

2018-05-14 14:25:00,093 INFO STDOUT 14:25:00,093 [com.xxxxxxx.xxx.conn.aoo.invok] INFO - APP Request [ eq.SELECT,,***********//DTA,AA.AA.AA.AAAA,@ID:EQ=DTA ]
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.beans.xxxxxxxBean Transform - Completed server response transform. Took 31 ms.
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet 123-132-0-23-0
2018-05-14 14:25:00,108 INFO com.xxxxxxx.browser.servlets.BrowserServlet

the query i tried

| gentimes start=01/01/01
| eval time=starttime
| fields _time
| reverse
| appendcols
NUMBER=* | fields - time | mvexpand ValueA ]
| appendcols
[| makeresults index="jboss" AUDIT
DATA="- Request" | fields - time | mvexpand ValueB ]
| search ValueA="" ValueB=""
| dedup ValueA ValueB
| eval Time=
time
| map search="
| gentimes start=01/01/01
| eval time=starttime
| fields _time
| reverse
| appendcols
NUMBER=* | fields - time | mvexpand ValueA ]
| appendcols
[| makeresults index="jboss" AUDIT
DATA="- Request" | fields - _time | mvexpand ValueB ]

``````     | eval selectedTime=\$Time\$
``````

| eval originalTime=time
| where originalTime>=selectedTime
| streamstats count by ValueA ValueB reset
before="ValueA="" AND ValueB="""
| stats dc(ValueA) as countValues list(ValueA) as ValueA list(ValueB) as ValueB by count
| search countValues>1
"

this query is not returning me any data 😞

Regards,
Vigneshprasanna R

Highlighted

## Re: Loop to select a Range of values and display.

SplunkTrust

I believe this is a general solution to get the first ten combinations. You can change the "head" value in the appendpipe to decide how many you want.

``````| streamstats count as recno
| streamstats count as reccount by ValueA ValueB
| appendpipe [| where reccount<3
| stats min(recno) as minrec max(recno) as maxrec by ValueA ValueB
| eval myData="ValueA=".ValueA.",ValueB=".ValueB.",minrec=".minrec.",maxrec=".maxrec
| stats max(maxrec) as maxmax list(myData) as myData
| eval myData=mvcombine(myData,"!!!!")
]
| eventstats max(myData) as myData max(maxmax) as maxmax
| where recno<=maxmax
| makemv myData delim="!!!!"
| eval maxFan=mvcount(myData)
| eval myFan=mvrange(0,maxFan)
| mvexpand myFan
| eval myData=mvindex(myData,myFan)
| rex field=myData "minrec=(?<minrec>/d+),maxrec=(?<maxrec>/d+)"
| where recno>minrec AND recno<maxrec
| rex field=myData "(?<SearchValues>ValueA=\d+,ValueB=\d+),"
| table SearchValues ValueA ValueB
``````

The output should look something like this...

``````SearchValues               ValueA  ValueB
ValueA=-11,ValueB=request  -10     bag
ValueA=-11,ValueB=request  -13     ball
ValueA=-10,ValueB=bag      -13     ball
ValueA=-10,ValueB=bag      -11     request
ValueA=-13,ValueB=ball     -11     request
``````

This version will only get the first example/pair of each ValueA ValueB combination. The appendpipe subsearch could be modified to provide every pair, but the code might be slightly more complicated.