topic Re: How to edit my search to output results to separate rows based on multiple values? in Splunk Search

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

rajadatta — Wed, 20 May 2015 22:58:53 GMT

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.

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

stephanefotso — Wed, 20 May 2015 23:24:27 GMT

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)

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

rajadatta — Thu, 21 May 2015 00:03:48 GMT

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?

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

stephanefotso — Thu, 21 May 2015 00:31:40 GMT

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

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

rajadatta — Thu, 21 May 2015 18:19:06 GMT

This is great. Thank you.

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

rajadatta — Thu, 21 May 2015 21:48:30 GMT

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.

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

stephanefotso — Sat, 23 May 2015 00:00:16 GMT

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

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

rajadatta — Tue, 26 May 2015 19:03:31 GMT

Thanks this was a huge help.