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
SplunkTrust
SplunkTrust

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

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

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

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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...