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

[dabunn]

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		DELIVER	DROP	QUARANTINE
Buy Naff Stuff		0	12	55
Enlarge everything		1	0	12
Malicious email		1	34	12
Spam Msg		0	0	123

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

Subject	DELIVER	DROP	QUARANTINE	Different Actions
Malicious email	1	34	12	3

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.

[somesoni2 · Accepted solution]

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]

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]

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