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 ?
| 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?
Thanks everyone for all your time and support here
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
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
@niketnilay Can you help here ?
@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.
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
I have already tried both, they were not working
@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
Not working for me
try this
|table Order_Creation_Time,Order_Count,Brand |chart values(Order_Count) by Brand,Order_Creation_Time
it's not working, already tried
| 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?
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
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?
yes , its different
| 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.
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
I see, no problem, Happy Splunking