Splunk Search

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

cheokiie
Engager

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

0 Karma
1 Solution

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

poete
Builder

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
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...