Splunk Search

How to edit my search to output results to separate rows based on multiple values?

rajadatta
New Member

Hi - I have been trying to get this search below to result in separate rows depending on the values. I have the information below:

My current search

index="atti" sourcetype="strongmail" source="/data1/strongmail/log/strongmail-failed.log" | chart count(mailingclass) as NumberFailed by mailingclass 

which results in:

mailingclass NumberFailed
smtpvhost1.adsolutions.yp.com 136
smtpvhost1.yp.com 131
smtpvhost3.yp.com 124
smtpvhost2.adsolutions.yp.com 28
smtpvhost3.adsolutions.yp.com 8
smtpvhost2.yp.com 2

I want to get two rows:

first row "Consumer" where
mailingclass="smtpvhost1.yp.com" OR mailingclass="smtpvhost2.yp.com" OR mailingclass="smtpvhost3.yp.com"

second row "Advertiser" where
mailingclass="smtpvhost1.adsolutions.yp.com" OR mailingclass="smtpvhost2.adsolutions.yp.com" OR mailingclass="smtpvhost3.adsolutions.yp.com"

Now I want to only have two total rows. an example would be something like this:

ORGANIZATION Failed
Consumer 172
Advertiser 257

Any help would be great, this is so much easier in SQL, I am having issues all day in SPLUNK. Thanks.

0 Karma
1 Solution

stephanefotso
Motivator

Here you go

index="atti" sourcetype="strongmail" source="/data1/strongmail/log/strongmail-failed.log" | stats count(eval(mailingclass="smtpvhost1.yp.com" OR mailingclass="smtpvhost2.yp.com" OR mailingclass="smtpvhost3.yp.com") as Consumer  count(eval(mailingclass="smtpvhost1.adsolutions.yp.com" OR mailingclass="smtpvhost2.adsolutions.yp.com" OR mailingclass="smtpvhost3.adsolutions.yp.com) as Advertiser)
SGF

View solution in original post

stephanefotso
Motivator

Here you go

index="atti" sourcetype="strongmail" source="/data1/strongmail/log/strongmail-failed.log" | stats count(eval(mailingclass="smtpvhost1.yp.com" OR mailingclass="smtpvhost2.yp.com" OR mailingclass="smtpvhost3.yp.com") as Consumer  count(eval(mailingclass="smtpvhost1.adsolutions.yp.com" OR mailingclass="smtpvhost2.adsolutions.yp.com" OR mailingclass="smtpvhost3.adsolutions.yp.com) as Advertiser)
SGF

rajadatta
New Member

Thanks this was a huge help.

0 Karma

rajadatta
New Member

This might be more complicated currently the results are like below:

Consumer | Advertiser
3232 | 2323

From the initial query is there a way to get to just per row and not column. The charts are not doing the correct comparison since it is not comparing to each other.

More like:

Organization | Failed
Consumer | 3232
Advertiser | 2323

Thanks appreciate the help, my objective is to do be able to do a comparison in the end.

0 Karma

stephanefotso
Motivator

Here you go :

index="atti" sourcetype="strongmail" source="/data1/strongmail/log/strongmail-failed.log" | stats count(eval(mailingclass=="smtpvhost1.yp.com" OR mailingclass=="smtpvhost2.yp.com" OR mailingclass=="smtpvhost3.yp.com")) as Consumer count(eval(mailingclass="smtpvhost1.adsolutions.yp.com" OR mailingclass="smtpvhost2.adsolutions.yp.com" OR mailingclass="smtpvhost3.adsolutions.yp.com")) as Advertiser |eval Organisation=mvappend("Consumer","advertiser")|eval Failed=mvappend(Consumer,Advertiser)|table Organisation Failed
SGF
0 Karma

rajadatta
New Member

thanks for the answer ran into some issues with some parenthesis, but I got it to work, thanks so much:

index="atti" sourcetype="strongmail" source="/data1/strongmail/log/strongmail-failed.log" | stats count(eval(mailingclass=="smtpvhost1.yp.com" OR mailingclass=="smtpvhost2.yp.com" OR mailingclass=="smtpvhost3.yp.com")) as Consumer count(eval(mailingclass="smtpvhost1.adsolutions.yp.com" OR mailingclass="smtpvhost2.adsolutions.yp.com" OR mailingclass="smtpvhost3.adsolutions.yp.com")) as Advertiser

Is it easy to manipulate this into a chart to see comparisons over time?

0 Karma

stephanefotso
Motivator

Yes of course! even with the stats command you can see comparison over time:

Try this:

index="atti" sourcetype="strongmail" source="/data1/strongmail/log/strongmail-failed.log" | stats count(eval(mailingclass=="smtpvhost1.yp.com" OR mailingclass=="smtpvhost2.yp.com" OR mailingclass=="smtpvhost3.yp.com")) as Consumer count(eval(mailingclass="smtpvhost1.adsolutions.yp.com" OR mailingclass="smtpvhost2.adsolutions.yp.com" OR mailingclass="smtpvhost3.adsolutions.yp.com")) as Advertiser by _time

or this

    index="atti" sourcetype="strongmail" source="/data1/strongmail/log/strongmail-failed.log" | timechart count(eval(mailingclass=="smtpvhost1.yp.com" OR mailingclass=="smtpvhost2.yp.com" OR mailingclass=="smtpvhost3.yp.com")) as Consumer count(eval(mailingclass="smtpvhost1.adsolutions.yp.com" OR mailingclass="smtpvhost2.adsolutions.yp.com" OR mailingclass="smtpvhost3.adsolutions.yp.com")) as Advertiser 

or ...

SGF
0 Karma

rajadatta
New Member

This is great. Thank you.

0 Karma
Get Updates on the Splunk Community!

Splunk App for Anomaly Detection End of Life Announcment

Q: What is happening to the Splunk App for Anomaly Detection?A: Splunk is officially announcing the ...

Aligning Observability Costs with Business Value: Practical Strategies

 Join us for an engaging Tech Talk on Aligning Observability Costs with Business Value: Practical ...

Mastering Data Pipelines: Unlocking Value with Splunk

 In today's AI-driven world, organizations must balance the challenges of managing the explosion of data with ...