Splunk Search

How to combine results of a Distinct Count with a ctable query in a table?

dabunn
Engager

I have sendmail logs which have an action field which can be DELIVER, DROP or QUARANTINE.

What I am trying to do is combine the restult of a "ctable Subject Action" query with a "chart dc(Action) by Subject"

ctable Subject Action























Subject     DELIVERDROPQUARANTINE
Buy Naff Stuff     01255
Enlarge everything     1012
Malicious email     13412
Spam Msg     00123
     
     

chart dc(Action) as "Different Action" Subject























Subject     Different Actions
Buy Naff Stuff     2
Enlarge everything     2
Malicious email     3
Spam Msg     1
     
     

What I need is a table that contains both sets of details so that I can add a select search of where dc(Action)=3

Producing Something like
ctable Subject Action








SubjectDELIVERDROPQUARANTINEDifferent Actions
Malicious email134123

The whole query is a little (quite a lot) more complicated in reality, but I cannot figure out how to get both query types into one result.

I've tried eval to create a new field, eventstats amongst others - but my head is now about to explode - so time to ask for help.

Tags (4)
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try this

Your base search | table Subject Action | stats count by Subject Action | appendpipe [|stats count by Subject] | eval Action=coalesce(Action,"Different Actions") | xyseries Subject Action count

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try this

Your base search | table Subject Action | stats count by Subject Action | appendpipe [|stats count by Subject] | eval Action=coalesce(Action,"Different Actions") | xyseries Subject Action count

dabunn
Engager

That is perfect, I just add a search "Different Actions"=3 and it does the job.

Now - I just need to work out how it is working, i've never used appendpipe or xyseries, a bit of research required me thinks.

Again - Thanks

somesoni2
SplunkTrust
SplunkTrust

Can you post some sample data that you get before executing command "| ctable Subject Action" OR "|chart dc(Action) by Subject" ?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...