Dashboards & Visualizations

How would I create a Table using stats within stats?

Mena
Explorer

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. 

Labels (2)
0 Karma
1 Solution

gcusello
Esteemed Legend

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

View solution in original post

PickleRick
Ultra Champion

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
Esteemed Legend

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
Explorer

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.

 

0 Karma
Get Updates on the Splunk Community!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...