Loop to select a Range of values and display.

[Vigneshprasanna]

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

[DalJeanis]

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 
    | head 10 
    | 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.

[niketn]

@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?

[Vigneshprasanna]

@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

[niketn]

@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
"

[Vigneshprasanna]

@niketn a small clarification if suppose i have AUDIT_TIME "24/4/17 12.00" with the value A=-10 and value B=bag and AUDIT_TIME 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

[Vigneshprasanna]

@niketn

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
AUDIT_TIME,LOGGING_PRIORITY,LOG_LEVEL,THREAD_NUMBER ,CONNECTION_FACTOR,AUDIT_DATA
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
[| makeresults index="jboss" THREAD_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
[| makeresults index="jboss" THREAD_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

[Vigneshprasanna]

@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 ..

can you please help me in designing the query

thanks for the support in advance 🙂

Regards,
Vigneshprasanna R