Need help with XML extraction

Mahipal456 · ‎08-03-2021

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"

ITWhisperer · ‎08-03-2021

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

Mahipal456 · ‎08-03-2021

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

ITWhisperer · ‎08-03-2021

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"

ITWhisperer · ‎08-03-2021

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

Mahipal456 · ‎08-03-2021

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>

Mahipal456 · ‎08-05-2021

Could someone please look at it. thank you!

ITWhisperer · ‎08-05-2021

| 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"
| eval namevalue=mvzip(max_score,value,"|")
| fields - max_score value
| mvexpand namevalue
| eval key=mvindex(split(namevalue,"|"),0)
| eval value=mvindex(split(namevalue,"|"),1)
| eval {key}=value
| fields - key value namevalue
| stats list(*) as *
| eval row=mvrange(1,mvcount(name)+1)
| mvexpand row
| foreach *
    [| eval <<FIELD>>=if("<<FIELD>>"!="row",mvindex('<<FIELD>>',row-1),'<<FIELD>>')]
| fields - row

Mahipal456 · ‎08-09-2021

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"

ITWhisperer · ‎08-09-2021

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?

Mahipal456 · ‎08-15-2021

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

ITWhisperer · ‎08-15-2021

| 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

Mahipal456 · ‎08-17-2021

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

ITWhisperer · ‎08-17-2021

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?

Mahipal456 · ‎08-17-2021

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>

ITWhisperer · ‎08-17-2021

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?

Mahipal456 · ‎08-17-2021

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

ITWhisperer · ‎08-17-2021

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

| 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 _time
| streamstats count as row by _time
| eval row=floor((row-1)/4)
| stats values(*) as * by _time row
| fields - row

Need help with XML extraction

other

Join Us for Splunk University and Get Your Bootcamp Game On!

.conf24 | Learning Tracks for Security, Observability, Platform, and Developers!

Announcing Scheduled Export GA for Dashboard Studio