Splunk Search

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

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

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

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

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

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