How would I create a Table using stats within stats?

[Mena]

Hello-

I am attempting to make a table and hopefully be able to integrate it into a dashboard.

Goal is to interrogate on two fields and pull stats accordingly.

FieldA has multiple values- table is to show all values of FieldA. Utilize stats count for how many daily transactions have been processed by each unique value of FieldA. 

Then the portion I am having difficulties with- with the daily count for each unique value of FieldA, I want to interrogate that count by FieldB to see how many of that count is a hit for any value of FieldB.

This is the code I am using:

table FieldA FieldB | fields "FieldB", "FieldA " | fields "FieldB", "FieldA " | stats count by FieldA , FieldB| sort -"count"

The second count of FieldB hits out of the count of FieldA instances is always showing up as zero, despite having values other than zero in FieldB. FieldB values should all be numeric. 

[PickleRick]

Firstly, your search is... strange. You do "table | fields | fields" with the same set of fields. That makes no sense.

Just using "| table" is enough.

I don't quite understand what you want to achieve though. Your "| stats count by FieldA, FieldB" will give you counts by each combination of FieldA and FieldB values. So now you only need to sum by value of one field or another if you want to get aggregated results.

Oh, and you don't need to do "| table" if you want to do "| stats" just after.

So just do "| stats count by FieldA FieldB" and summarize if needed

[gcusello · Accepted solution]

Hi @Mena,

let me understand:

do you want to have in a single table the count of each value of fieldA and the count of each value of fieldB, is it correct?,

in your search you have the count of both count of FieldA and FieldB and probably the values of these fields don't match.

My first hint is to create two different tables one for FieldA and one for FieldB.

But anyway, if you want one table you could try something like this:

<your_search>
| stats count AS CountA BY FieldA
| append [ search
   <your_search>
   | stats count AS CountB BY FieldB
   ]
| eval Field=coalesce(fieldA,FieldB)
| table Field CountA CountB

This solution has only the limit of 50,000 results for the subsearch, but I suppose that it isn't a problem.

Ciao.

Giuseppe

[Mena]

Thanks! I had some issues with the coalesce- was only CountA values. Tried doing this:

index=* sourcetype=_JSON logstreamName="*" OR logstreamName="*"   |stats count as total by FieldA | appendcols [ search index=* sourcetype=_JSON logstreamName="*" OR logstreamName="*" | stats count(eval(FieldB> 0)) AS total by FieldA]

So I only want to interrogate the stats count of FieldA against values of FieldB that are greater than 0.