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 ?
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...
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...
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 ?