how to get _time-object-kpi1-kpi2...kpiN columns format for graphing?

[HattrickNZ]

I have this search:
...| timechart span=d sum(kpi1) as "kpi1" sum(kpi2) as "kpi2" by userLabel
which gives the following format(1):

_time   kpi1: obj1  kpi1: obj2  kpi2: obj1  kpi2: obj2
1   2016-02-04  7560856 7046562 1009480 946519

I have this search:
... | chart sum(kpi1) as "kpi1" sum(kpi2) as "kpi2" by userLabel -- want this format but with _time column
which gives the following format(2):

userLabel   kpi1    kpi2
1   obj1    7560856 1009480
2   obj2    7046562 946519

So what I want to do is achieve this

    _time       userLabel   kpi1    kpi2   kpiN
1   2016-02-04  obj1    7560856 1009480    777
2   2016-02-04  obj2    7046562 946519     777

how do I do this? i have been trying xyseries and untable, but no joy yet.
A bit frustrated as I am sure I have done this before. I think I might have to go with chart(2 above) above and use bucket. Can anyone assist?

[somesoni2 · Accepted solution]

Try something like this

your base search | bucket span=1d _time | stats sum(kpi1) as "kpi1" sum(kpi2) as "kpi2" by  _time,userLabel

[HattrickNZ]

tks, thats the one, not sure why it took me so long to get there!! DOH!

[javiergn]

What about?

...
| bucket _time span=1d 
| chart sum(kpi1) as "kpi1" sum(kpi2) as  "kpi2" over _time by userLabel

[HattrickNZ]

tks, thats what I was trying to remember but that gave me this format:

 _time    kpi1: obj1    kpi1: obj2    kpi2: obj1    kpi2: obj2
 1    2016-02-04    7560856    7046562    1009480    946519

but with a bit of fiddling I got this to work for 1 day

... | bucket _time span=1d | eval _time = strftime(_time,"%Y-%m-%d") |
chart values(_time) as _time sum(kpi1) as "kpi1" sum(kpi2) as "kpi2" by userLabel
| fields + _time userLabel *

     _time         userLabel    kpi1    kpi2   
 1    2016-02-04    obj1    7560856    1009480  
 2    2016-02-04    obj2    7046562    946519 

but if I do 7 days I get this whis is not what I want:

    _time   userLabel   kpi1    kpi2
1   2016-01-29,2016-01-31,2016-02-01,2016-02-02,2016-02-05  obj1    3290858 436760
2   2016-01-29,2016-01-31,2016-02-01,2016-02-05 obj2    2160879 290454

[javiergn]

I see, in that case I think what you are looking for was already provided above by @somesoni2:

| bucket span=1d _time 
| stats sum(kpi1) as "kpi1" sum(kpi2) as "kpi2" by  _time, userLabel