How to combine multiple fields filtering to create a new eval variable?

[cheokiie]

Hi , i have the following fields (host id time) and 6 records

host    | id
******    *****************
A   |   3
A   |   5
B   |   1
B   |   6
C   |   5
C   |   3

If the following conditions is met , the records will be product A and B respectively as below :

eval product A = (if host = A and id = 3) AND (if host = B and id = 6) AND (if host = C and id = 5) >> total 3 records 
eval product B = (if host = A and id = 5) AND (if host = B and id = 1) AND (if host = C and id = 3)

My final outcome should be like this in stats :

product |   count
******    *****************
A       |   3 (3 events matches product A)
B       |   3

I've tried>>" eval product = case(host=="A" AND id=='3', "A" , host=="B" AND id=='6', "A" ) etc | stats count(host) by product" but not working , it just keep loading . Am i doing it wrongly ? Please correct me if i'm wrong

Thanks

[poete]

I created the following lookup file, according to your input:

Host,Id
A,3
A,5
B,1
B,6
C,5
C,3

with this, the following search does the right computation

| inputlookup test.csv
| eval productA=if(Host="A" and Id=3,1,if( Host="B" and Id=6,1,if(Host="C" and Id = 5,1,0)))
| eval productB=if(Host="A" and Id=5,1,if( Host="B" and Id=1,1,if(Host="C" and Id = 3,1,0)))
| stats sum(product*)

What it basically does is, for each Host,Id pair to evaluate if the pair belongs to productA or productB, and then simply sums for all products

[niketn · Accepted solution]

@cheokiie, try adding the following eval and stats pipes to your existing search:

<YourCurrentSearch>
| eval Product=case(
    ((host="A" AND id=3) OR (host="B" AND id=6) OR (host="C" AND id=5)),"A",
    ((host="A" AND id=5) OR (host="B" AND id=1) OR (host="C" AND id=3)),"B",
    true(),"Others"
    )
| stats count by Product

Following is a run anywhere example based on sample data provided:

| makeresults 
| eval data="A|3;A|5;B|1;B|6;C|5;C|3" 
| makemv data delim=";" 
| mvexpand data 
| makemv data delim="|" 
| eval host=mvindex(data,0), id=mvindex(data,1) 
| fields - data, _time 
| eval Product=case(
    ((host="A" AND id=3) OR (host="B" AND id=6) OR (host="C" AND id=5)),"A",
    ((host="A" AND id=5) OR (host="B" AND id=1) OR (host="C" AND id=3)),"B",
    true(),"Others"
    )
| stats count by Product

Please try out and confirm!