I am trying to setup a timechart and I am a beginner in Splunk. I'd like to show a timechart with two rows, i.e., two sets of data for a week).
I have below query:
index=myindx "Box Sales Job:" "Total number of boxes sold" earliest=-7d@d latest=@d | rex field=_raw "Total number of boxes sold:(?<BoxCount>.+) for (?<BoxType>.+)" | table BoxType, BoxCount, _time
that produces
+---------+----------+-------------------------+
| BoxType | BoxCount | _time |
+---------+----------+-------------------------+
| Small | 45 | 2018-08-28 16:27:35.649 |
| Small | 17 | 2018-08-28 13:27:35.649 |
| Large | 65 | 2018-08-28 16:27:34.142 |
| Large | 10 | 2018-08-28 13:27:34.142 |
| Small | 66 | 2018-08-24 16:59:55.100 |
| Large | 12 | 2018-08-24 16:59:54.288 |
| Small | 60 | 2018-08-24 09:38:01.101 |
| Large | 12 | 2018-08-24 09:38:00.373 |
| Small | 45 | 2018-08-27 16:44:28.652 |
| Large | 56 | 2018-08-27 16:44:28.168 |
| Small | 12 | 2018-08-25 16:31:32.386 |
| Large | 34 | 2018-08-25 16:31:31.931 |
| Small | 98 | 2018-08-23 16:33:43.708 |
| Large | 12 | 2018-08-23 16:33:43.092 |
+---------+----------+-------------------------+
What I am trying to show is such as below: Please note box sales happened twice on 28th.
+---------+----------+-------------------------+
| Box | 7days_before | 6days_before | 5days_before | 4days_before | 3days_before | 2days_before | 1days_before | latest |
|-------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------|
| Small | 45 | 49 | 98 | 12 | 45 | 60 | 66 | 45 |
| Large | 75 | 11 | 12 | 34 | 56 | 12 | 12 | 65 |
| Small | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 17 |
| Large | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 10 |
I tried below query and a couple other ways.
index=myidx "Box Sales Job:" "Total number of boxes sold" earliest=-7d@d latest=@d | rex field=_raw "Total number of boxes sold:(?<BoxCount>.+) for (?<BoxType>.+)" | table BoxType, BoxCount, _time | timechart span=24h count | timewrap 1d
but it ends up showing the count of rows rather than the desired format. I am not sure what am I missing. Any ideas?
Can you try the below? I haven`t tested it though.
index=myidx "Box Sales Job:" "Total number of boxes sold" earliest=-7d@d latest=@d
| rex field=_raw "Total number of boxes sold:(?.+) for (?.+)"
| table BoxType, BoxCount, _time
| timechart span=1d sum(BoxCount) As BoxCount by BoxType
Can you try the below? I haven`t tested it though.
index=myidx "Box Sales Job:" "Total number of boxes sold" earliest=-7d@d latest=@d
| rex field=_raw "Total number of boxes sold:(?.+) for (?.+)"
| table BoxType, BoxCount, _time
| timechart span=1d sum(BoxCount) As BoxCount by BoxType
It works. However I had to report each days sales separately instead of summing it up (to know if this job runs twice). So I made a change in timechart as
timechart span=24h values(NachaCount) by NachaType
Thank you!