topic Re: Need help with XML extraction in Splunk Search

Need help with XML extraction

Mahipal456 — Tue, 03 Aug 2021 10:20:13 GMT

Hi All,

I need to extract the fields from the below xml data tried xpath and xmlkv but not working as expected.

<item>

<field name="name">Johnson David</field>

I have tried with below xpath but it just extracts name,max_score but not values.

| xpath outfield=max_score "//field/@name"

Re: Need help with XML extraction

ITWhisperer — Tue, 03 Aug 2021 10:28:41 GMT

| xpath outfield=name "//field/@name" | spath output=value "item.field"

Re: Need help with XML extraction

Mahipal456 — Tue, 03 Aug 2021 11:10:17 GMT

No luck! I just tried with below query but still extracts the

| xpath outfield=max_score "//field/@name"
|spath output=value "item.field"

but not values

Re: Need help with XML extraction

ITWhisperer — Tue, 03 Aug 2021 11:18:27 GMT

Since you didn't seem to get anything extracted in the value field, I am guessing that the path used in the spath isn't correct. Perhaps if you shared the full event we could determine what the path should be or you could adjust it yourself to fit your actual data

Re: Need help with XML extraction

Mahipal456 — Tue, 03 Aug 2021 11:42:52 GMT

Below is the raw event:

i need to extract name, max_score, YP,YM,BM,J,CV,LI,CF,BB,GO, score filed values from the below xml file

<item> <field name="name">David Thomson</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,14.098001709571708,0.0,0.0,19.133111345911498,0.0,0.0</field> <field name="score">33.23111305548321</field> </item> <item> <field name="name">Fran Cabezas</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,42.8637215072008,0.0,0.0,5.287992581426981,0.0,0.0</field> <field name="score">48.15171408862778</field> </item> <item> <field name="name">Maureen Unga</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.12443642256864788,1.2907225990899451,0.0,0.0,0.35709749477908853,0.6991740341680656,0.0</field> <field name="score">2.4714305506057475</field> </item> <item> <field name="name">Ronald Hanna</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,0.0,15.14610095862175,0.0,0.0,0.0,0.0</field> <field name="score">15.14610095862175</field> </item> <item> <field name="name">Robert Kim</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,2.460623352990376,0.0,0.0,5.048603083462512,0.0,0.0</field> <field name="score">7.509226436452888</field> </item> <item> <field name="name">David Fasano</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,0.07512772055229844,0.0,0.0,2.993704678772839,0.0,0.0</field> <field name="score">3.0688323993251374</field> </item> <item> <field name="name">Lou Hileman</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,0.19377260451274092,0.0,0.0,0.2838113559132797,0.0,0.0</field> <field name="score">0.4775839604260206</field> </item> <item> <field name="name">Derek Hegyi</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,5.784199833165305,0.0,0.0,3.0649478257754486,0.0,0.0</field> <field name="score">8.849147658940751</field> </item> <item> <field name="name">Edith Rivera</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.6713152358528098,2.0069451989378324,0.0,0.0,0.16834174584798137,0.0,0.0</field> <field name="score">2.8466021806386235</field> </item> <item> <field name="name">Sarah Barahona</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,1.8593191943529368,0.0,0.0,5.747710129525515,0.0,0.0</field> <field name="score">7.607029323878452</field> </item> <item> <field name="name">Robert Horner</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,0.36834735015558323,0.0,0.0,0.5259259909542762,0.0,0.0</field> <field name="score">0.8942733411098593</field> </item> <item> <field name="name">Joan Pace</field> <field name="max_score">69.49894379732375</field> <field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,4.133199143516765,0.0,0.0,5.511552044443158,0.0,0.0</field> <field name="score">9.644751187959924</field> </item>

Re: Need help with XML extraction

ITWhisperer — Tue, 03 Aug 2021 11:55:46 GMT

Here is a runanywhere example show the two extractions working

| makeresults | eval _raw="<item> <field name=\"name\">David Thomson</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,14.098001709571708,0.0,0.0,19.133111345911498,0.0,0.0</field> <field name=\"score\">33.23111305548321</field> </item> <item> <field name=\"name\">Fran Cabezas</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,42.8637215072008,0.0,0.0,5.287992581426981,0.0,0.0</field> <field name=\"score\">48.15171408862778</field> </item> <item> <field name=\"name\">Maureen Unga</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.12443642256864788,1.2907225990899451,0.0,0.0,0.35709749477908853,0.6991740341680656,0.0</field> <field name=\"score\">2.4714305506057475</field> </item> <item> <field name=\"name\">Ronald Hanna</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,0.0,15.14610095862175,0.0,0.0,0.0,0.0</field> <field name=\"score\">15.14610095862175</field> </item> <item> <field name=\"name\">Robert Kim</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,2.460623352990376,0.0,0.0,5.048603083462512,0.0,0.0</field> <field name=\"score\">7.509226436452888</field> </item> <item> <field name=\"name\">David Fasano</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,0.07512772055229844,0.0,0.0,2.993704678772839,0.0,0.0</field> <field name=\"score\">3.0688323993251374</field> </item> <item> <field name=\"name\">Lou Hileman</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,0.19377260451274092,0.0,0.0,0.2838113559132797,0.0,0.0</field> <field name=\"score\">0.4775839604260206</field> </item> <item> <field name=\"name\">Derek Hegyi</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,5.784199833165305,0.0,0.0,3.0649478257754486,0.0,0.0</field> <field name=\"score\">8.849147658940751</field> </item> <item> <field name=\"name\">Edith Rivera</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.6713152358528098,2.0069451989378324,0.0,0.0,0.16834174584798137,0.0,0.0</field> <field name=\"score\">2.8466021806386235</field> </item> <item> <field name=\"name\">Sarah Barahona</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,1.8593191943529368,0.0,0.0,5.747710129525515,0.0,0.0</field> <field name=\"score\">7.607029323878452</field> </item> <item> <field name=\"name\">Robert Horner</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,0.36834735015558323,0.0,0.0,0.5259259909542762,0.0,0.0</field> <field name=\"score\">0.8942733411098593</field> </item> <item> <field name=\"name\">Joan Pace</field> <field name=\"max_score\">69.49894379732375</field> <field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,4.133199143516765,0.0,0.0,5.511552044443158,0.0,0.0</field> <field name=\"score\">9.644751187959924</field> </item>" | xpath outfield=max_score "//field/@name" | spath output=value "item.field"

Re: Need help with XML extraction

Mahipal456 — Thu, 05 Aug 2021 11:18:10 GMT

Could someone please look at it. thank you!

Re: Need help with XML extraction

ITWhisperer — Thu, 05 Aug 2021 12:07:07 GMT

Re: Need help with XML extraction

Mahipal456 — Mon, 09 Aug 2021 11:28:43 GMT

Hi @ITWhisperer ,

Thank you for the query it works like charm exactly what i was looking for. But, if you could help me to extract score field also from the below data . Thank you!

For now i see only 3 fields are being extracted

name, max_score, "YP,YM,BM,J,CV,LI,CF,BB,GO"

Re: Need help with XML extraction

ITWhisperer — Mon, 09 Aug 2021 11:35:38 GMT

The runanywhere example I posted works with extracting score as well as the other three fields. Can you post the exact search you used and sample events showing it not working?

Re: Need help with XML extraction

Mahipal456 — Sun, 15 Aug 2021 15:23:00 GMT

Hi,

Thank you for the email,

one last question about this, if my events are coming as individuals like below can i still extract them?

please help with any query...

and for the 1st event it should map like YP=0.0, YM=0.0, BM=0.0, J=0.2791684435174566, CV= 0.5595851129682144, LI=0.0, CF=0.0, BB=0.0, GO=0.0

8/10/21
4:56:00.000 PM

8/10/21
4:56:00.000 PM

8/10/21
4:56:00.000 PM

<field name="name">Richard Oakley</field>

8/10/21
4:56:00.000 PM

Re: Need help with XML extraction

ITWhisperer — Sun, 15 Aug 2021 16:09:37 GMT

| makeresults | eval lines="<field name=\"name\">David Thomson</field>|<field name=\"max_score\">69.49894379732375</field>|<field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,14.098001709571708,0.0,0.0,19.133111345911498,0.0,0.0</field>|<field name=\"score\">33.23111305548321</field>|<field name=\"name\">Fran Cabezas</field>|<field name=\"max_score\">69.49894379732375</field>|<field name=\"YP,YM,BM,J,CV,LI,CF,BB,GO\">0.0,0.0,0.0,42.8637215072008,0.0,0.0,5.287992581426981,0.0,0.0</field>|<field name=\"score\">48.15171408862778</field>" | eval lines=split(lines,"|") | mvexpand lines | rename lines as _raw | streamstats count as row | xpath outfield=fieldname "//field/@name" | spath output=fieldvalue "field" | eval fieldname=split(fieldname,",") | eval fieldvalue=split(fieldvalue,",") | eval namevalue=mvzip(fieldname,fieldvalue,"!") | mvexpand namevalue | eval fieldname=mvindex(split(namevalue,"!"),0) | eval fieldvalue=mvindex(split(namevalue,"!"),1) | eval {fieldname}=fieldvalue | fields - fieldname fieldvalue namevalue | stats values(*) as * by row | fields - row

Re: Need help with XML extraction

Mahipal456 — Tue, 17 Aug 2021 10:41:20 GMT

Thanks for the quick email, At my final result i am trying to create a table with name, score but there is no mapping to the fields.

If i run the table command against the data it give me the result like below there is no mapping between them is this something issue with the data format or can we extract it to fit as per the requirement please help

Re: Need help with XML extraction

ITWhisperer — Tue, 17 Aug 2021 10:46:38 GMT

Because you have split the contents of <item> across multiple events, you need a way to join them together again. What do the original events look like and what SPL have you used to split them up?

Re: Need help with XML extraction

Mahipal456 — Tue, 17 Aug 2021 12:00:11 GMT

Please see below table data itself breaks like that while ingesting, and unable to map name along side score to get a proper table view.

	8/10/21 4:56:00.000 PM	<item>
	8/10/21 4:56:00.000 PM	<field name="score">0.8387535564856712</field>
	8/10/21 4:56:00.000 PM	<field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,0.2791684435174566,0.5595851129682144,0.0,0.0,0.0,0.0</field>
	8/10/21 4:56:00.000 PM	<field name="max_score">94.8686621869273</field>
	8/10/21 4:56:00.000 PM	<field name="name">Richard Oakley</field>
	8/10/21 4:56:00.000 PM	<item>
	8/10/21 4:56:00.000 PM	</item>
	8/10/21 4:56:00.000 PM	<field name="score">1.285899750959314</field>
	8/10/21 4:56:00.000 PM	<field name="YP,YM,BM,J,CV,LI,CF,BB,GO">0.0,0.0,0.0,0.0,0.0,0.0,1.285899750959314,0.0,0.0</field>
	8/10/21 4:56:00.000 PM	<field name="max_score">94.8686621869273</field>
	8/10/21 4:56:00.000 PM	<field name="name">Ronald Dumas</field>
	8/10/21 4:56:00.000 PM	<item>

Re: Need help with XML extraction

ITWhisperer — Tue, 17 Aug 2021 12:13:36 GMT

Are they always in groups of 6 (or 4 if you remove the item events)?

Can you fix the ingestion to keep item and its sub-elements together?

Have you tried transaction startswith="<item>" endswith="</item>" to regroup them?

Re: Need help with XML extraction

Mahipal456 — Tue, 17 Aug 2021 13:25:03 GMT

Unfortunately the re indexing is not working.... can it be possible to extract the combinations with the current format of the logs please?

Re: Need help with XML extraction

ITWhisperer — Tue, 17 Aug 2021 14:05:04 GMT

Assuming all the items have 4 elements and that they have the same timestamp, you could try something like this