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
Revered Legend

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
Revered Legend

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
Revered Legend

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!

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Want a chance to win $500 to the Splunk shop? Take our IT Incident Management Survey!

  Top Trends & Best Practices in Incident ManagementSplunk is partnering up with Constellation Research to ...