Getting Data In

Timestamp in 1/1/1900 format

craigkleen
Communicator

Hi,

I'm trying to get data in from a file where data is in the following format (anonymized):

{"seq":55619,"ntp_time":[3809782725,1802580594],"reporting_id":{"tugid":"server","ep_type":"sip","side":"SS","mac":"aa:bb:cc:dd:ee:ff","user":"username","dn":"43128"},"stream_id":{"sip_callid":"hexstring","local_uri":"sips:emailstring:5061","remote_uri":"sips:emailstring:5061;transport=tls","ep_stream_id":5053},"event":"rtcp_tx","rtcp_block":{"addr_local":"ipaddr:24794","addr_remote":"ipaddr5036","cname":"emailstring","snd_ssrc":680275594,"recv_ssrc":3888553685,"snd_pktcnt":206158433963,"snd_bcnt":4121132523374324448,"rx_loss_total":139753940844544,"rx_loss_fract":0,"rx_jtr":-139758235811834,"rtt":139753940844544},"rtp_stats":{"observed_pt":0,"observed_codec":"RTP_CODEC_G711_U"}}

So, a nice JSON.  But, that pair of integers in ntp_time{} are seconds since 1/1/1900 and a fractional second, not 1/1/1970.  I'm really, really hoping I don't have to write a second script that writes out the correct timestamp.

On my indexers, for the sourcetype I've defined for this, I've the following:

[baddate]
REGEX = ntp_time\":\[(?<baddate>\d+)

INGEST_EVAL = gooddate = baddate - 2208988800

I also have props.conf calling the transform, and fields.conf setting "INDEXED=True" for baddate.  But I don't get the field in search yet.  Would this even work though?  Does anyone have any other strategies I can try?  I don't really care about the fractional second, but would work it in if I can get something to work.

Labels (1)
0 Karma
1 Solution

craigkleen
Communicator

I ended up just resolving this "well enough" with some field extractions.  I made two field extractions to get the "timestamp_1900" and "timestamp_1900fract", then made a calculated field for "timestamp" with this sourcetype.  The calculation looks like:

 

strftime(timestamp_1900 - 2208988800 + round(timestamp_1900fract / 4294967295, 3), "%m-%d-%Y %H:%M:%S.%3N")

View solution in original post

Tags (1)
0 Karma

craigkleen
Communicator

I ended up just resolving this "well enough" with some field extractions.  I made two field extractions to get the "timestamp_1900" and "timestamp_1900fract", then made a calculated field for "timestamp" with this sourcetype.  The calculation looks like:

 

strftime(timestamp_1900 - 2208988800 + round(timestamp_1900fract / 4294967295, 3), "%m-%d-%Y %H:%M:%S.%3N")

Tags (1)
0 Karma
Got questions? Get answers!

Join the Splunk Community Slack to learn, troubleshoot, and make connections with fellow Splunk practitioners in real time!

Meet up IRL or virtually!

Join Splunk User Groups to connect and learn in-person by region or remotely by topic or industry.

Get Updates on the Splunk Community!

Accelerate Splunk App Development with AI

  Learn how to automate the repetitive parts of your build cycle.   Watch On-Demand   Replay the Accelerate ...

Splunk Agent Observability: Measuring AI Value with Splunk Tokenomics on AWS

The AI landscape is moving faster than ever, and our latest 2026 State of Observability report provides the ...

Enterprise Security Watch Party: What’s New in 8.8 - Recap

Splunk Community HQ hosted a Splunk Enterprise Security Watch Party: What’s New In ES 8.8 last Thursday. I had ...