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

[rajadatta]

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.

[stephanefotso · Accepted solution]

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)

[rajadatta]

Thanks this was a huge help.

[rajadatta]

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.

[stephanefotso]

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

[rajadatta]

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?

[stephanefotso]

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 ...

[rajadatta]

This is great. Thank you.