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
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...