topic Re: How i can show my result in below format in Splunk Search

How i can show my result in below format

nilbak1 — Wed, 30 Sep 2020 03:07:56 GMT

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 ?

Re: How i can show my result in below format

to4kawa — Mon, 25 Nov 2019 11:53:58 GMT

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

Re: How i can show my result in below format

nilbak1 — Mon, 25 Nov 2019 12:02:06 GMT

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

Re: How i can show my result in below format

Sukisen1981 — Wed, 30 Sep 2020 03:04:16 GMT

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

Re: How i can show my result in below format

kamlesh_vaghela — Mon, 25 Nov 2019 12:38:32 GMT

@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

Re: How i can show my result in below format

arjunpkishore5 — Mon, 25 Nov 2019 12:49:56 GMT

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

Re: How i can show my result in below format

to4kawa — Mon, 25 Nov 2019 13:03:56 GMT

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?

Re: How i can show my result in below format

nilbak1 — Mon, 25 Nov 2019 13:18:44 GMT

yes , its different

Re: How i can show my result in below format

nilbak1 — Mon, 25 Nov 2019 13:19:18 GMT

I have already tried both, they were not working

Re: How i can show my result in below format

nilbak1 — Mon, 25 Nov 2019 13:19:43 GMT

Not working for me

Re: How i can show my result in below format

nilbak1 — Mon, 25 Nov 2019 13:20:12 GMT

it's not working, already tried

Re: How i can show my result in below format

nilbak1 — Mon, 25 Nov 2019 13:20:52 GMT

@niketnilay Can you help here ?

Re: How i can show my result in below format

Sukisen1981 — Wed, 30 Sep 2020 03:04:21 GMT

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

Re: How i can show my result in below format

to4kawa — Mon, 25 Nov 2019 13:44:51 GMT

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

Re: How i can show my result in below format

woodcock — Mon, 25 Nov 2019 15:22:32 GMT

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

Re: How i can show my result in below format

niketn — Tue, 26 Nov 2019 05:54:18 GMT

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

Re: How i can show my result in below format

nilbak1 — Wed, 30 Sep 2020 03:08:35 GMT

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

Re: How i can show my result in below format

nilbak1 — Tue, 26 Nov 2019 13:06:38 GMT

Thanks everyone for all your time and support here

Re: How i can show my result in below format

to4kawa — Tue, 26 Nov 2019 13:15:05 GMT

I see, no problem, Happy Splunking