How i can show my result in below format

[nilbak1]

I have table in below format.
Order_Creation_Time Order_Count Brand
00 6 brand1
00 4 brand2
00 2 brand3
00 69 brand4
01 8 brand1
01 15 brand2
01 40 brand3
01 70 brand4

These Order_Creation_Time are the hours from (starting from 00 till 23)

I have to convert this in below format

Brand 00 01 02 03 04 ....10.....20......23
brand1 6 8
brand2 4 15
brand3 2 40
brand4 69 70

Can anyone help me with this ?

[nilbak1]

Thanks everyone for all your time and support here

[woodcock]

Like this:

|makeresults
| eval _raw="Order_Creation_Time,Order_Count,Brand
00,6,brand1
00,4,brand2
00,2,brand3
00,69,brand4
01,8,brand1
01,15,brand2
01,40,brand3
01,70,brand4"
|multikv
| fields - _time _raw linecount

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| xyseries Brand Order_Creation_Time Order_Count

[Sukisen1981]

hi @nilbak1
What is not working? I guess we are all confused about your expected output?
|table Order_Creation_Time,Order_Count,Brand |chart values(Order_Count) by Brand,Order_Creation_Time
The output you have given in your question comes from a simple chart, but am i missing something?
please see my screenshot of output

[nilbak1]

@niketnilay Can you help here ?

[niketn]

@nilbak1 I see a lot of community experts have already tried to assist you with your issue. So you may have to describe a little bit more as to what or how the solution/s suggested by them did not work for you? When you are preparing the header column of hours, do you need to show the header row for each hour whether brand data exist for it or not.

[arjunpkishore5]

Based on your sample data, this should work for you

|chart values(Order_Count) over Brand by Order_Creation_Time 

OR this

| xyseries Order_Creation_Time, Brand,  Order_Count

Here are the docs for reference
chart: https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/Chart
xyseries: https://docs.splunk.com/Documentation/Splunk/8.0.0/SearchReference/Xyseries

hope this helps

[nilbak1]

I have already tried both, they were not working

[kamlesh_vaghela]

@nilbak1

Can you please try this?

YOUR_SEARCH | table Order_Creation_Time Order_Count Brand
| xyseries Brand Order_Creation_Time Order_Count

Sample Search:

| makeresults | eval _raw="Order_Creation_Time  Order_Count Brand
00  24  brand1
00  62  brand2
00  83  brand3
00  2   brand4
01  93  brand1
01  35  brand2
01  84  brand3
01  46  brand4
02  64  brand1
02  61  brand2
02  44  brand3
02  43  brand4
03  61  brand1
03  68  brand2
03  41  brand3
03  51  brand4
04  76  brand1
04  79  brand2
04  30  brand3
04  44  brand4
05  75  brand1
05  51  brand2
05  80  brand3
05  64  brand4
06  17  brand1
06  5   brand2
06  74  brand3
06  78  brand4
07  36  brand1
07  68  brand2
07  96  brand3
07  13  brand4
08  87  brand1
08  38  brand2
08  74  brand3
08  59  brand4
09  89  brand1
09  4   brand2
09  68  brand3
09  87  brand4
10  8   brand1
10  90  brand2
10  90  brand3
10  94  brand4
11  98  brand1
11  80  brand2
11  31  brand3
11  95  brand4
12  9   brand1
12  16  brand2
12  79  brand3
12  11  brand4
13  61  brand1
13  10  brand2
13  11  brand3
13  98  brand4
14  23  brand1
14  93  brand2
14  85  brand3
14  100 brand4
15  44  brand1
15  66  brand2
15  15  brand3
15  70  brand4
16  25  brand1
16  91  brand2
16  39  brand3
16  64  brand4
17  61  brand1
17  79  brand2
17  64  brand3
17  15  brand4
18  71  brand1
18  43  brand2
18  35  brand3
18  48  brand4
19  42  brand1
19  62  brand2
19  33  brand3
19  45  brand4
20  98  brand1
20  92  brand2
20  14  brand3
20  9   brand4
21  99  brand1
21  80  brand2
21  16  brand3
21  56  brand4
22  99  brand1
22  12  brand2
22  27  brand3
22  87  brand4
23  69  brand1
23  96  brand2
23  7   brand3
23  3   brand4
" | multikv | table Order_Creation_Time Order_Count Brand
| xyseries Brand Order_Creation_Time Order_Count

Thanks

[nilbak1]

Not working for me

[Sukisen1981]

try this
|table Order_Creation_Time,Order_Count,Brand |chart values(Order_Count) by Brand,Order_Creation_Time

[nilbak1]

it's not working, already tried

[to4kawa · Accepted solution]

| makeresults count=2
| streamstats count
| eval _time = if (count==2,relative_time(_time,"-1d@d"), relative_time(_time,"@d")) 
| makecontinuous span=15m
| fillnull
| where count!=1
| eval Order_Creation_Time=strftime(_time,"%H") ,Order_Count = random() % 100 + 1
| streamstats count by Order_Creation_Time
| eval Brand = "brand".count
| table Order_Creation_Time Order_Count Brand
`comment("this is sample data")`
| chart useother=f limit=0 sum(Order_Count) as Order_Count  over Brand by Order_Creation_Time

Hi, how about it?

[nilbak1]

Hi @to4kawa
The result is coming in the correct format which i want, but the values of order_count is not coming correct as you summing them here

[to4kawa]

My data is created at random once each time, so if you look at the table and check the summary, the values will be different.

Is it different if you add chart to your query?

[nilbak1]

yes , its different

[to4kawa]

| makeresults 
| eval _raw="Order_Creation_Time,Order_Count,Brand
00,6,brand1
00,4,brand2
00,2,brand3
00,69,brand4
01,8,brand1
01,15,brand2
01,40,brand3
01,70,brand4"
| multikv forceheader=1
| chart sum(Order_Count) as Order_Count over Brand by Order_Creation_Time

Is there anything other than numbers in Order_count?
Since it was not in the example presented, I summed up normally, but if so, I need to add a query.

[nilbak1]

Hi @to4kava... its working now, mightbe I have messed up my query earlier.

my search | multikv| table ORDER_CREATION_TIME ORDER_COUNT BRAND | chart useother=f limit=0 values(ORDER_COUNT) as ORDER_COUNT over BRAND by ORDER_CREATION_TIME

[to4kawa]

I see, no problem, Happy Splunking