Getting Data In
Provide Splunk Cloud feedback in this confidential UX survey by June 17
for a chance to win a $200 Amazon gift card!

Replace space in timestamp with a 0 (with datetime.xml possibly?)

pestatp
Path Finder

I have events that unfortunately use a space instead of a 0 in their timestamp field.  The timestamp goes down to 6 decimal places, so there can be as many as 5 leading spaces in the decimal seconds section.  Each event starts with the timestamp as below.  As you can see, it has a leading space and I'd like to change that to a 0

[12-02-2020:08.31.44. 15133] SIP IN: 

I have tried using SEDCMD in the props.conf, but it didn't seem to work on the newly indexed events, is my regex not correct or am I way off?

[sip_sbc]
SEDCMD-replace_space=s/^(\[[0-9-:\.]{20}\]) ()/\10\2/g

 

Edit: This is my current props.conf settings.  This works fine for the timestamps that have 6 digits after the . but any of them that have leading spaces fail to get the proper timestamp

 

 

[sip_sbc]
BREAK_ONLY_BEFORE_DATE = 
DATETIME_CONFIG = 
LINE_BREAKER = ----------------------------------------------------------------------------------------([\r\n]+)
NO_BINARY_CHECK = true
SHOULD_LINEMERGE = false
TIME_FORMAT = %m-%d-%Y:%H.%M.%S.%6N
TIME_PREFIX = [
category = Custom
pulldown_type = 1
BREAK_ONLY_BEFORE_TIME = 
disabled = false
MUST_BREAK_AFTER = 

 

 

 

Edit No. 2:  I have added a custom datetime.xml file for the app.  It does have an effect, but it's not working quite right.  It doesn't pad the leading spaces to 0, it just removes the space and therefor causes the subseconds to be much higher than they are supposed to be on some timestamps.  

 

 

<datetime>
    <define name="custom_dateformat" extract="month, day, year">
        <text><![CDATA[\[(\d+)-(\d+)-(\d+)]]></text>
    </define>
    <define name="custom_timeformat" extract="hour, minute, second, subsecond">
        <text><![CDATA[\[\d+-\d+-\d+:(\d+).(\d+).(\d+).\s*(\d+)]]></text>
    </define>
    <timePatterns>
        <use name="custom_timeformat" />
    </timePatterns>
    <datePatterns>
        <use name="custom_dateformat" />
    </datePatterns>
</datetime>

 

 

This datetime.xml caused an event with the timestamp: 01-08-2021:11.28.23.  8213 (note the 2 spaces before 8213)  to be parsed as 1/8/21 11:28:23.821  (this is 8 10ths of a second after the timestamp should be)

It should be 1/8/21 11:28:23.008213

Labels (1)
Tags (3)
0 Karma

scelikok
Champion

Hi @pestatp,

Can you try below datetime.xml?

<datetime>
    <define name="custom_dateformat" extract="month, day, year">
        <text><![CDATA[\[(\d+)-(\d+)-(\d+)]]></text>
    </define>
    <define name="custom_timeformat" extract="hour, minute, second, subsecond">
        <text><![CDATA[\[\d+-\d+-\d+:(\d+).(\d+).(\d+).\s*(\d+)]]></text>
    </define>
    <timePatterns>
        <use name="custom_timeformat" />
    </timePatterns>
    <datePatterns>
        <use name="custom_dateformat" />
    </datePatterns>
</datetime>

 

If this reply helps you an upvote is appreciated.

If this reply helps you an upvote is appreciated.

pestatp
Path Finder

 

@scelikok 

That is the closest I have gotten it so far, but it doesn't look like it's treating the spaces as zeros, it's just ignoring them.

It indexed 01-08-2021:11.28.23. 8213 as 1/8/21 11:28:23.821 AM  -  that is better than what it was doing before, but still not quite right.  It should be indexed as 1/8/21 11:28:23.008213 AM

 

0 Karma

pestatp
Path Finder

After some more searching, I came across datetime.xml.  It looks like that can be used to do custom datetime extraction although, I am a bit confused by the documentation.  Does anyone know if that would work for this scenario?

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Yes, the datetime.xml file can be customized.  It should be pretty straightforward to add support for 9 decimal places.  Look for where subseconds are extracted from the pattern that matches your time format.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Can you ignore the microseconds part of the timestamp?

IIRC, SEDCMD executes after timestamp extraction so there's nothing it can do to a timestamp.

Perhaps Cribl can do the job.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

pestatp
Path Finder

No, I cannot ignore it.  These are SIP messages and they need to be in as precise order as possible to ensure the call flow is correct.  Many of the messages are within just a few thousandths of a second of each other.

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!