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!

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