Splunk Search

creating timechart grouped data tables

stephenreece78
Engager

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?

Labels (5)
0 Karma
1 Solution

yuanliu
Builder

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:

_timecodeproduct
2021-05-20 08:42:43200prod1
2021-05-20 09:42:43200prod2
2021-05-20 10:42:43200prod1
2021-05-20 11:42:43203prod2
2021-05-20 12:42:43200prod2
2021-05-20 13:42:43203prod1
2021-05-20 14:42:43202prod2
...  

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

_time200prod1200prod2201prod1201prod2202prod1202prod2203prod1203prod2204prod1204prod2
2021-05-190000000000
2021-05-202230221120
2021-05-211302212311

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

_time200: prod1200: prod2201: prod1201: prod2202: prod1202: prod2203: prod1203: prod2204: prod1204: prod2
2021-05-19          
2021-05-201100133222
2021-05-211422111210

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

prod-code.png

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

View solution in original post

yuanliu
Builder

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:

_timecodeproduct
2021-05-20 08:42:43200prod1
2021-05-20 09:42:43200prod2
2021-05-20 10:42:43200prod1
2021-05-20 11:42:43203prod2
2021-05-20 12:42:43200prod2
2021-05-20 13:42:43203prod1
2021-05-20 14:42:43202prod2
...  

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

_time200prod1200prod2201prod1201prod2202prod1202prod2203prod1203prod2204prod1204prod2
2021-05-190000000000
2021-05-202230221120
2021-05-211302212311

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

_time200: prod1200: prod2201: prod1201: prod2202: prod1202: prod2203: prod1203: prod2204: prod1204: prod2
2021-05-19          
2021-05-201100133222
2021-05-211422111210

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

prod-code.png

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

View solution in original post

stephenreece78
Engager

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

Tags (1)
0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.