Timestamp in 1/1/1900 format

craigkleen — Tue, 22 Sep 2020 18:38:35 GMT

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.

Re: Timestamp in 1/1/1900 format

craigkleen — Thu, 01 Oct 2020 18:31:07 GMT

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

topic Re: Timestamp in 1/1/1900 format in Getting Data In

Timestamp in 1/1/1900 format

Re: Timestamp in 1/1/1900 format