creating timechart grouped data tables

stephenreece78 — Thu, 20 May 2021 00:00:56 GMT

hi all,

newbee question here but i can't seem to find an answer.

I am trying to create a timechart table grouped table that has product as the parent and error codes as the child.

I can't seem to get any sort of multiple column grouping where i can have separate calculations for each column variant.

so the structure would losely be:

Product1 Product2

200 201 300 ......and so on 200 201 300 303 ......and so on

date1 34 23 22 43 23 76 23
date2 2 43 45

.....and so on.

it would be fine to coalesce the prod and error code , but i can figure that out either i.e. 200Prod1 201Prod1 200Prod2 201Prod2

Is this something that is logically doable?

creating timechart grouped data tables

yuanliu — Thu, 20 May 2021 08:28:08 GMT

You may want to elaborate your input so others can have some idea about what you are trying to achieve.

Assuming an input like the following:

_time	code	product
2021-05-20 08:42:43	200	prod1
2021-05-20 09:42:43	200	prod2
2021-05-20 10:42:43	200	prod1
2021-05-20 11:42:43	203	prod2
2021-05-20 12:42:43	200	prod2
2021-05-20 13:42:43	203	prod1
2021-05-20 14:42:43	202	prod2
...

which is simulated with the following

| makeresults count=30 | streamstats count | eval _time = _time + count * 3600, product = if(random() % 2 == 0, "prod2", "prod1"), code = 200 + random() % 5 | fields - count

There are several ways to obtain your desired outcome. The simplest is to use coalesced headers

| eval code_prod = code . product | timechart span=1d count by code_prod

This will give you something like

_time	200prod1	200prod2	201prod1	201prod2	202prod1	202prod2	203prod1	203prod2	204prod1	204prod2
2021-05-19	0	0	0	0	0	0	0	0	0	0
2021-05-20	2	2	3	0	2	2	1	1	2	0
2021-05-21	1	3	0	2	2	1	2	3	1	1

As timechart, the visual will not be very easy to grasp.

When I really, really want split charts, I have used the following technique. (Well, I use it mostly not for split charts.) Bewarned: things get ugly really fast when you have many different return codes.

| eval c200 = if(code == 200, 1, 0), c201 = if(code == 201, 1, 0), c202 = if(code == 202, 1, 0), c203 = if(code == 203, 1, 0), c204 = if(code == 204, 1, 0) | timechart span=1d sum(c200) as 200 sum(c201) as 201 sum(c202) as 202 sum(c203) as 203 sum(c204) as 204 by product

The results table looks something like this

_time	200: prod1	200: prod2	201: prod1	201: prod2	202: prod1	202: prod2	203: prod1	203: prod2	204: prod1	204: prod2
2021-05-19
2021-05-20	1	1	0	0	1	3	3	2	2	2
2021-05-21	1	4	2	2	1	1	1	2	1	0

At first glance, this is not very different from the above. But because the seemingly coalesced header is generated by timechart itself, timechart has a way to split them, called "trellis". In "Visualization", if you check "Use Trellis Layout" under "Trellis", you can get graphic charts like this. ("Split by" in this example is product; you can also split by code.)

As I mentioned, this works when you have only a handful of codes to deal with.

Re: creating timechart grouped data tables

stephenreece78 — Thu, 20 May 2021 08:39:57 GMT

thanks for the quick reply.

i should have mentioned the table may well be exported and used in other applications so the first option you showed for output is ideal as it can be imported into other apps and interrogated easily

topic Re: creating timechart grouped data tables in Splunk Search

creating timechart grouped data tables

creating timechart grouped data tables

Re: creating timechart grouped data tables