topic Re: timestamp from XML multivalue in Getting Data In

timestamp from XML multivalue

jbanhome — Sun, 07 May 2017 17:45:28 GMT

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,

Re: timestamp from XML multivalue

jkat54 — Sun, 07 May 2017 23:14:30 GMT

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

Re: timestamp from XML multivalue

dineshraj9 — Mon, 08 May 2017 05:18:11 GMT

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.

Re: timestamp from XML multivalue

jbanhome — Mon, 08 May 2017 19:51:37 GMT

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

Re: timestamp from XML multivalue

jbanhome — Mon, 08 May 2017 20:22:49 GMT

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

Re: timestamp from XML multivalue

jkat54 — Mon, 08 May 2017 23:01:22 GMT

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

Re: timestamp from XML multivalue

jbanhome — Tue, 29 Sep 2020 13:59:33 GMT

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

Re: timestamp from XML multivalue

dineshraj9 — Tue, 29 Sep 2020 13:59:35 GMT

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

Re: timestamp from XML multivalue

jbanhome — Tue, 29 Sep 2020 13:59:39 GMT

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?

Re: timestamp from XML multivalue

jbanhome — Tue, 09 May 2017 09:34:27 GMT

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>

Re: timestamp from XML multivalue

dineshraj9 — Tue, 09 May 2017 10:52:58 GMT

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

Re: timestamp from XML multivalue

jbanhome — Tue, 09 May 2017 11:04:05 GMT

sure but timestamp values still none.

Re: timestamp from XML multivalue

jbanhome — Tue, 29 Sep 2020 14:01:02 GMT

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>

Re: timestamp from XML multivalue

cstump_splunk — Tue, 29 Sep 2020 19:41:43 GMT

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.