Splunk Search

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

patilsh
Explorer

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:

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

0 Karma
1 Solution

DalJeanis
Legend

@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")

View solution in original post

DalJeanis
Legend

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

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 ?

0 Karma

patilsh
Explorer

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.

0 Karma

woodcock
Esteemed Legend

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

0 Karma

DalJeanis
Legend

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

Makes sense! Thanks a lot!!

woodcock
Esteemed Legend

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
0 Karma

patilsh
Explorer

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

0 Karma

patilsh
Explorer
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)

0 Karma

DalJeanis
Legend

wouldn't that be ...?

| makecontinuous eventTime span=1s 

woodcock
Esteemed Legend

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

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...