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!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...