How do you make a comparison of fields with the same values but from different search results?

[j_r]

My basic search returns the following output: Type, ID, Destination
Destination depends on Type. Means:, if Type=X, then Destination = A and if Type=Y, thenDestination`= B.
ID is always the same for 2 search results.

Searc result example:
Type, ID, Destination
X, 1, A
Y, 1, B
X, 2, A
Y, 2, B

I would now like to realize the following:
If I enter B into a text field , then the corresponding entry A should be displayed to me.
Like : search for A where Type = X and same ID

[renjith_nair]

@j_r,

As per the comments, this should work with a single search.

<form>
  <label>Destination</label>
  <fieldset submitButton="false">
    <input type="dropdown" token="start">
      <label>Start</label>
      <fieldForLabel>Destination</fieldForLabel>
      <fieldForValue>Destination</fieldForValue>
      <search>
        <query>|makeresults|eval Type="X,Y,X,Y",ID="1,1,2,2",Destination="A,B,C,D" |makemv Destination delim="," | makemv Type delim="," |makemv ID delim="," | eval x=mvzip(mvzip(Type,ID),Destination)|fields x| mvexpand x|eval x=split(x,",") |eval Type=mvindex(x,0),ID=mvindex(x,1),Destination=mvindex(x,2)|fields Type,ID,Destination|fields - _time
|rename C as "Search above is data generation"
|eventstats values(Destination) as TheOtherDest by ID | mvexpand TheOtherDest |where Destination!=TheOtherDest
|dedup Destination</query>
        <earliest>-1s</earliest>
        <latest>now</latest>
      </search>
    </input>
    <input type="dropdown" token="dest">
      <label>Destination</label>
      <search>
        <query>|makeresults|eval Type="X,Y,X,Y",ID="1,1,2,2",Destination="A,B,C,D" |makemv Destination delim="," | makemv Type delim="," |makemv ID delim="," | eval x=mvzip(mvzip(Type,ID),Destination)|fields x| mvexpand x|eval x=split(x,",") |eval Type=mvindex(x,0),ID=mvindex(x,1),Destination=mvindex(x,2)|fields Type,ID,Destination|fields - _time
|rename C as "Search above is data generation"
|eventstats values(Destination) as TheOtherDest by ID | mvexpand TheOtherDest |where Destination!=TheOtherDest
|search Destination=$start$
|dedup TheOtherDest</query>
        <earliest>-1s</earliest>
        <latest>now</latest>
      </search>
      <fieldForLabel>TheOtherDest</fieldForLabel>
      <fieldForValue>TheOtherDest</fieldForValue>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Data</title>
      <table>
        <search>
          <query>|makeresults|eval Type="X,Y,X,Y",ID="1,1,2,2",Destination="A,B,A,B" |makemv Destination delim="," | makemv Type delim="," |makemv ID delim="," | eval x=mvzip(mvzip(Type,ID),Destination)|fields x| mvexpand x|eval x=split(x,",") |eval Type=mvindex(x,0),ID=mvindex(x,1),Destination=mvindex(x,2)|fields Type,ID,Destination|fields - _time
|rename C as "Search above is data generation"
|eventstats values(Destination) as TheOtherDest by ID | mvexpand TheOtherDest |where Destination!=TheOtherDest</query>
          <earliest>-1s</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">none</option>
      </table>
    </panel>
  </row>
</form>

[whrg · Accepted solution]

Hi! How about using a subsearch?

The subsearch returns all Job_IDs whose Destination is A. The main search (excluding Destination A events) will filter on those Job_IDs:

basesearch
| search Destination!="A" [search basesearch | search Destination="A" | table Job_ID]
| table Destination,Job_ID,Job_Type

Use a text input in a dashboard to replace "A" with "$destination$".

[j_r]

Thank you very much. Works perfectly 🙂

[renjith_nair]

@j_r,
- Are there only two records for each ID and two types X&Y ?
- If there are more than two records, what value should it return for Destination ?

If there are only two records for each ID, try this

"your search "|fields Type,ID,Destination
|eventstats values(Destination) as TheOtherDest by ID
|mvexpand TheOtherDest|where Destination!=TheOtherDest

Here TheOtherDest has your required value

[j_r]

Here is an example of the search:

1. PICKUP7058 JOB7058 "A"
2. DROPOFF7059 JOB7058 "B"
3. PICKUP7061 JOB7059 "A"
4. DROPOFF7060 JOB7059 "C"

Extracted fields are:
"DROPOFF7059, PICKUP7058, ..." as Job_Type
"JOB7058, JOB7059,..." as Job_ID
"A", "B", "C", "D" ... as Destination

As you can see, here 1 and 2 belong to the same process (same Job_ID). These are transport processes from X to Y. For example, A can deliver to B and C. My goal is to use the input destination (for example A) to output the corresponding other possible destination (here: B and C). I hope you understand what I mean 🙂

[renjith_nair]

@j_r,
Is it possible for A to have multiple destination ? For eg. is it possible to have this combination (same job id and two destinations?

PICKUP7058  JOB7058 "A"
DROPOFF7059 JOB7058 "B"
DROPOFF7059 JOB7058 "C"

Nevertheless, can you try this search and lets know if it works? And if it doesn't what change you need?

"your search "|fields Job_Type,Job_ID,Destination
 |eventstats values(Destination) as TheOtherDest by Job_ID
 |mvexpand TheOtherDest|where Destination!=TheOtherDest

[j_r]

Thanks for this fast reply.
No, no multiple destinations in 1 process possible. 1 JobID contains allways 2 different Job types and 2 destination. Unfortunately the process log output is split by Job_Type.

For better understanding i made some screenshots:

Also i have to consider the $token$ for the dropdown-menu in my search.

Your suggested search brought up empty results 😕