How can I change the time output from this timechart to show time when there is no event?

[patilsh]

This is my code, the data includes a field labeled "callId" (for this particular search there are 3 distinct callId) and the stats are as below:

The yellow highlighted portion is where the first callId ends and the next line is where the new callId starts. Now as seen after 17:50:39, the next call happened at 18:08:57
So instead after 17:50:39 I want 17:50:40 with corresponding column as 0, 17:50:41 and so on till it reaches 18:08:57.

The time is eventTime and not _time and hence I am not able to use timechart with span=1s. So can someone please help how to go about with this?

[DalJeanis · Accepted solution]

@patilsh -

I'm not surprised it's not working, because the code never made sense to me. Formatting a time field into display format before you bucket it and stats it isn't a useful strategy.

Here's the debug steps...

First, run this and see if it creates a valid date.

 index="*******" userId="******"   
| head 5
| rename eventTime.$date as eventTime
| eval eventTime=strftime((eventTime/1000)-25200, "%Y-%m-%d %H:%M:%S")

If not, then let me know and we'll circle back.

If the output eventTime looks good, then try this...

 index="*******" userId="******" 
| rename eventTime.$date as eventTime
| eval eventTime=(eventTime/1000)-25200
| bucket eventTime span=1s
| stats list(eventData.nearTalk) by eventTime
| makecontinuous eventTime span=1s
| eval eventTime=strftime(eventTime, "%Y-%m-%d %H:%M:%S")

[patilsh]

Hey that is perfect, It worked ,
But just a quick question Why shouldn't the time be formatted before ? And also (eventTime/1000)-25200 , I did this to convert epoch time from milliseconds to seconds and then convert to my time zone, my query was even if I dont convert to seconds , will the span=1s not take care of it ?

[patilsh]

Hello,

When I use make continuous and then convert time, the timestamp has some different value, it is continuous but time is some other value.

So can you please tell me what could be the possible reason for this.

[woodcock]

You can format it anywhere if you use fieldformat to do it (which only changes the way that it is displayed) but for efficiency, never do any formatting to be pretty until the very end (much fewer things to format).

[DalJeanis]

@patilsh - once you use strftime, the result field is not a number, it's a bunch of characters. "2" "0" "1" "7" "-" and so on. It doesn't make send to "span" a character-based field. You have to do the span while the value is still a number.

As a general case, just always put formatting and "prettying up" at the end, just before output, and it will tend to be more efficient.

[patilsh]

Makes sense! Thanks a lot!!

[woodcock]

Like this:

search
| rename eventTime.$date as eventTime
| eval eventTime=strftime((eventTime/1000)-25200, "%Y-%m-%d %H:%M:%S")
| bucket eventTime span=1s
| stats list(eventData.nearTalk) by eventTime
| makecontinuous eventTime span=1s

[patilsh]

This doesn't work, I tried this as well.

[patilsh]

index="*******" userId="******" |rename eventTime.$date as eventTime|eval eventTime=strftime((eventTime/1000)-25200, "%Y-%m-%d %H:%M:%S")|bucket eventTime span=1s|stats list(eventData.nearTalk) by eventTime|makecontinuous eventTime span=1s

I tried the same , this shows total Events(4573) and Statistics (0)

[DalJeanis]

wouldn't that be ...?

| makecontinuous eventTime span=1s 

[woodcock]

You are quite correct. I have updated the answer; it should work now.