All Apps and Add-ons

How to create result table in result table (Advance XML)

sieutruc
Contributor

Hello,

I have data which has maximum 8 types, associating with each type, there are maximum 4 status.

When i use:

...| stats count by type

 type1   count
 type4   count
 type8   count

...| stats count by status

 status1   count
 status2   count
 status3   count
 status4   count

But my boss wants me to display like

 type1   count    status1   count   Percent
                  status2   count   Percent
                  status3   count   Percent
                  status4   count   Percent
 type4   count    status1   count   Percent
                  status2   count   Percent
                  status3   count   Percent
                  status4   count   Percent
 type8   count ...

Which count of all status equals count of specific type, and percent field is defined as countPerStatus/countOfTypeX (X=1...8)

How can i archieve that in Splunk ?
And what is the value of $click.value$ in this case ?

1 Solution

sideview
SplunkTrust
SplunkTrust

Well, the way that's the best and the simplest overall, is:

| stats count by type status | eventstats sum(count) as typeCount by type | eval percent=100*count/typeCount 

that will yield a table like:

 type1   status1   count   typeCount   Percent
 type1   status2   count   typeCount   Percent
 type1   status3   count   typeCount   Percent
 type1   status4   count   typeCount   Percent
 type4   status1   count   typeCount   Percent
 type4   status2   count   typeCount   Percent
 type4   status3   count   typeCount   Percent
 type4   status4   count   typeCount   Percent
 ... etc

The drilldowns from here are very easy, at least with Sideview. Don't use the legacy $click.value$ keys - instead you can use $click.fields.type$, $click.fields.status$, $click.fields.typeCount$, $click.fields.Percent$, etc...

If you really must have it the way your boss requested, ie with the "type" values not being repeated, and the typeCount value not being repeated, then you just tack a little more on the end:

| stats count by type status | eventstats sum(count) as typeCount by type | eval percent=100*count/typeCount | stats list(status) as status list(count) as count list(Percent) as Percent by type typeCount

but this is really NOT a good idea, not least because it makes all your drilldowns basically impossible to do correctly without custom code... With this more finicky layout the only value you'll be able to get under the drilldown is $click.fields.type$ and $click.fields.typeCount$. All the other ones will be array-valued and you'd need some pretty advanced stuff to try and tease them apart.

NOTE: The advanced embedding features of the Sideview Table module do actually allow you to render any subset of living module config inside the individual cells of a Table module, and as such this includes other entire Table modules. However the drilldown stuff will still make this a bit of a dead end. If something is pushing you to start rendering tables inside tables, pause and reflect that that something is cracked in the head. 😃

Have you considered putting a couple Pulldown modules upstream from all this -- so the user could choose to see just one type, and just one status? Or you could use the Pulldowns multiple-selection stuff and allow the user to select one or more types, one or more statuses...

View solution in original post

sideview
SplunkTrust
SplunkTrust

Well, the way that's the best and the simplest overall, is:

| stats count by type status | eventstats sum(count) as typeCount by type | eval percent=100*count/typeCount 

that will yield a table like:

 type1   status1   count   typeCount   Percent
 type1   status2   count   typeCount   Percent
 type1   status3   count   typeCount   Percent
 type1   status4   count   typeCount   Percent
 type4   status1   count   typeCount   Percent
 type4   status2   count   typeCount   Percent
 type4   status3   count   typeCount   Percent
 type4   status4   count   typeCount   Percent
 ... etc

The drilldowns from here are very easy, at least with Sideview. Don't use the legacy $click.value$ keys - instead you can use $click.fields.type$, $click.fields.status$, $click.fields.typeCount$, $click.fields.Percent$, etc...

If you really must have it the way your boss requested, ie with the "type" values not being repeated, and the typeCount value not being repeated, then you just tack a little more on the end:

| stats count by type status | eventstats sum(count) as typeCount by type | eval percent=100*count/typeCount | stats list(status) as status list(count) as count list(Percent) as Percent by type typeCount

but this is really NOT a good idea, not least because it makes all your drilldowns basically impossible to do correctly without custom code... With this more finicky layout the only value you'll be able to get under the drilldown is $click.fields.type$ and $click.fields.typeCount$. All the other ones will be array-valued and you'd need some pretty advanced stuff to try and tease them apart.

NOTE: The advanced embedding features of the Sideview Table module do actually allow you to render any subset of living module config inside the individual cells of a Table module, and as such this includes other entire Table modules. However the drilldown stuff will still make this a bit of a dead end. If something is pushing you to start rendering tables inside tables, pause and reflect that that something is cracked in the head. 😃

Have you considered putting a couple Pulldown modules upstream from all this -- so the user could choose to see just one type, and just one status? Or you could use the Pulldowns multiple-selection stuff and allow the user to select one or more types, one or more statuses...

sieutruc
Contributor

In fact, i use your tool Sideview Utils and can do what you said.
I just want to get my desired table above; and when doing drilldown, i use only $click.fields.type$ .
And what do you mean "array-valued and you'd need some pretty advanced stuff to try and tease them apart." How can i find those stuffs ?

Is it possible using variable as $click.fields.type$ in javascript ?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...