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!

Technical Workshop Series: Splunk Data Management and SPL2 | Register here!

Hey, Splunk Community! Ready to take your data management skills to the next level? Join us for a 3-part ...

Spotting Financial Fraud in the Haystack: A Guide to Behavioral Analytics with Splunk

In today's digital financial ecosystem, security teams face an unprecedented challenge. The sheer volume of ...

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability

Solve Problems Faster with New, Smarter AI and Integrations in Splunk Observability As businesses scale ...