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!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...