Getting Data In

timestamp from XML multivalue

jbanhome
New Member

Hello,

I am new to Splunk and I have task where I need to configure timestamp from XML file.

<root>
    <day>11</day>
    <month>11</month>   
    <year>17</year>
    <time>123055<time>
    … 
</root>

Do you have any hint how to configure this.

If I understand this correctly I must use transforms.conf to parse day, month, year and time into timestamp because with timestamp I cannot take date from multi-value?
And then after trasform I can use something like %d%m%y %H%M%S

You dont need to write regex or anything, please just point me into right direction so I can try to figure it out.

Thank you,

Tags (3)
0 Karma

dineshraj9
Builder

Try something like this in props.conf -

TIME_PREFIX = day\>
TIME_FORMAT = %d\<\/day\>[\r\n\s]+\<month\>%m\<\/month\>[\r\n\s]+\<year\>%y\<\/year\>[\r\n\s]+\<time\>%H%M%S\<time\>
MAX_TIMESTAMP_LOOKAHEAD = 100

If you want to do it at search time, then extract all time fields and then set the _time(with epoch time) field and it will reflect across all events.

0 Karma

cstump_splunk
Splunk Employee
Splunk Employee

Quick Update for Splunk 7.1.0.

To get the Time_Format to extract a time stamp in 7.1.0 you can use @dineshraj9 answer if you lose the escape characters ().

A colleague and I were trying to extract a timestamp across multiple xml tags:
<fiveminute id="28"><date><year>2018</year><month>05</month><day>22</day></date><time><hour>17</hour><minute>05</minute></time><rx>578489</rx><tx>1800555</tx></fiveminute>

We were able to get the timestamp extracted in 7.1.0 with the following configurations:
[sourcetype]
TIME_PREFIX = year\>
TIME_FORMAT = %Y%m%d%H%M

Notice that the escape character was used in the TIME_PREFIX, that value is still regex, but the TIME_FORMAT is not regex and therefore does not make use of the escape character.

jbanhome
New Member

Unfortunatelly still no luck.

props.conf is something like this
[test2xml]
DATETIME_CONFIG =
MAX_TIMESTAMP_LOOKAHEAD = 150
NO_BINARY_CHECK = true
TIME_FORMAT = %d<\/day>[\r\n\s]+\%m<\/month>[\r\n\s]+\%y<\/year>[\r\n\s]+\%H%M%S\
TIME_PREFIX = day>
category = Custom
pulldown_type = true

0 Karma

dineshraj9
Builder

Here the TIME_FORMAT is missing start of the month tag and start of the year tag.
It should be - TIME_FORMAT = %d\<\/day\>[\r\n\s]+\<month\>%m\<\/month\>[\r\n\s]+\<year\>%y\<\/year\>[\r\n\s]+\<time\>%H%M%S

Also, is Splunk breaking the events properly? A single event should be .... and Splunk not be treating each line as an event.
LINE_BREAKER = ([\n\r]+)(?=\s*\<root\>)

0 Karma

jbanhome
New Member

still nothing. How can I see error log or something so I can see what I need to search. When I change props.conf is that real time change in Search?
I also tried to add - KV_MODE=xml and that will give me root.day, root.month but I simply not getting timestamp from xml.
do I need to TIME_FORMAT <\/day> or I just need to format value\n value\n value ...
How can I see what's I am doing wrong?

0 Karma

jbanhome
New Member

this is real example \nwhitespacevalue...

<LOG>
 <DAY>09</DAY>
 <MONTH>05</MONTH>
 <YEAR>17</YEAR>
 <TIME>102632</TIME>
 <CC>0488444333809518</CC>
 <AMOUNT>64957.01</AMOUNT>
</LOG>
0 Karma

dineshraj9
Builder

Could you try changing the fields to captial and try -

TIME_FORMAT = %d\<\/DAY\>[\r\n\s]+\<MONTH\>%m\<\/MONTH\>[\r\n\s]+\<YEAR\>%y\<\/YEAR\>[\r\n\s]+\<TIME\>%H%M%S

0 Karma

jbanhome
New Member

sure but timestamp values still none.

0 Karma

jbanhome
New Member

I think I'll just give up with this hint as from 1000 examples none of it's works for me. Always in WebUI \r\n\s. Above examples does not works also.

props
LINE_BREAKER = ([\n\r]+)(?=\s*)
MAX_TIMESTAMP_LOOKAHEAD = 65
NO_BINARY_CHECK = 1
SHOULD_LINEMERGE = true
TIME_FORMAT = %d<\/DAY>[\r\n\s]+\%m<\/MONTH>[\r\n\s]+\%y<\/YEAR>[\r\n\s]+\%H%M%S
TIME_PREFIX = DAY>

0 Karma

jbanhome
New Member

unfortunately no luck. : (
still \n and spaces give me problems. 😄

0 Karma

jbanhome
New Member

When I do it in WebUI and go with mouse over orange icon it's like
cannot parse time 11\n 11\n 17 ....
so now my question is, do I need to put <\/day>.... in TIME_FORMAT props? or I only need to escape \n4xWhitespace

0 Karma

jkat54
SplunkTrust
SplunkTrust

I changed his response just a little bit. Can you try it again?

0 Karma

jkat54
SplunkTrust
SplunkTrust

Do you want to extract the timestamp at search time or index time?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...