topic Re: how to correct/modify x-axis in Splunk Search

how to correct/modify x-axis

wessam — Fri, 09 Jun 2017 15:17:45 GMT

Dears,

I have two columns , first one is called ticket and second columns is date as below
Ticket date
AS123 6/6/2017 12:12:12
AS345 1/6/29017 11:10:12
AS564 2/6/2017 9:0:10

I would like to draw a graph where the x-axis display number of days from 1 to 31 and Y-axis represent number of tickets per each day

but the problem is in the x-axis as it displayed only days where there is a ticket only , it doesn't mention for example that at 3rd of June there wasn't any tickets as below
num count
1 1
2 1
6 1

What i would like to get
1 1
2 1
3 0
6 0
5 0
6 1

Re: how to correct/modify x-axis

somesoni2 — Fri, 09 Jun 2017 16:36:30 GMT

You can try this

index=xxx source=yyy |table "ticket","date" | rex field=date "^\d+\/(?<num>\d+)" |chart count by num | makecontinuous | fillnull value=0 count

Problem with above is it'll fill the missing numbers between the smallest and largest value of num but not full 1 to 31 (if there was no ticket on 1, there won't be any row for that). To overcome that you can try below one.

index=xxx source=yyy |table "ticket","date" | rex field=date "^\d+\/(?<num>\d+)" |chart count by num | append [| gentimes start=-1 | eval num=mvrange(1,32) | table num | mvexpand num | eval count=0 ]
| stats max(count) as count by num

Re: how to correct/modify x-axis

niketn — Fri, 09 Jun 2017 19:11:37 GMT

If your index has event time (ie. _time) same as your date field in the question you have provided you can use timechart

index=xxx source=yyy
| timechart span=1d count
| fieldformat _time=strftime(_time,"%d")

If _time field is not same as date and date field is string time, you need to convert the same to epoch and use chart instead.

index=xxx source=yyy
| eval date=strptime(date,""%d/%m/%Y %H:%M:%s")
| chart span=1d count over date
| fieldformat date=strftime(date,"%d")

PS: Ideally with timechart and chart empty time bucket should be filled with 0 when using count. If not you can always pipe fillnull command at the end i.e.

| fillnull value=0 count

With chart or timechart span=1d is used to created time bucket for 1 day as per your need. Final fieldformat command formats the epoch time field to show only date as string and retains underlying time field as epoch time.

Re: how to correct/modify x-axis

woodcock — Mon, 12 Jun 2017 04:08:01 GMT

You need makecontinuous:
https://docs.splunk.com/Documentation/SplunkCloud/6.6.0/SearchReference/Makecontinuous

Like this:

| makeresults 
| eval raw="1,1 2,1 6,1"
| makemv raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<num>.*),(?<count>.*)$"
| fields - _*

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

| makecontinuous num span=1
| fillnull value="0"