topic Re: xpath not giving result in Splunk Search

xpath not giving result

payal23 — Tue, 29 Sep 2020 18:33:55 GMT

I want to extract NewValue when Network Settings is International Roaming Bar.

Tried with | xpath outfield=NewValue "//SiebelMessage/ListOfVfNetworkSettings/VfNetworkSettings/[NetworkSetting=\"International Roaming Bar\"]/NewValue"

But No results.

UnbarredAdministration BarBarring123abcdNANHL|BarredStatusAUUnbarredIDD BarBarring123abcdNANHL|StatusAUUnbarredIncoming CallsBarring123abcdNANHL|StatusAUUnbarred*International Roaming BarBarring123abcdNANHL|***StatusAUUnbarred

Re: xpath not giving result

payal23 — Sun, 18 Mar 2018 03:27:23 GMT

<SiebelMessage TransactionName="A" IntObjectName="B" IntObjectFormat="C"><ListOfVfNetworkSettings><VfNetworkSettings><CurrentValue>Unbarred</CurrentValue><NetworkSetting>Administration Bar</NetworkSetting><Type>Barring</Type><IMSI>123</IMSI><MSISDN>abcd</MSISDN><SecondarySubs>N</SecondarySubs><NeServiceName>ANHL|</NeServiceName><NewValue>Barred</NewValue><Attribute>Status</Attribute><ServiceProvider>AU</ServiceProvider></VfNetworkSettings><VfNetworkSettings><CurrentValue>Unbarred</CurrentValue><NetworkSetting>IDD Bar</NetworkSetting><Type>Barring</Type><IMSI>123</IMSI><MSISDN>abcd</MSISDN><SecondarySubs>N</SecondarySubs><NeServiceName>ANHL|</NeServiceName><NewValue/><Attribute>Status</Attribute><ServiceProvider>AU</ServiceProvider></VfNetworkSettings><VfNetworkSettings><CurrentValue>Unbarred</CurrentValue><NetworkSetting>Incoming Calls</NetworkSetting><Type>Barring</Type><IMSI>123</IMSI><MSISDN>abcd</MSISDN><SecondarySubs>N</SecondarySubs><NeServiceName>ANHL|</NeServiceName><NewValue/><Attribute>Status</Attribute><ServiceProvider>AU</ServiceProvider></VfNetworkSettings><VfNetworkSettings><CurrentValue>Unbarred</CurrentValue><NetworkSetting>International Roaming Bar</NetworkSetting><Type>Barring</Type><IMSI>123</IMSI><MSISDN>abcd</MSISDN><SecondarySubs>N</SecondarySubs><NeServiceName>ANHL|</NeServiceName><NewValue/><Attribute>Status</Attribute><ServiceProvider>AU</ServiceProvider></VfNetworkSettings><VfNetworkSettings><CurrentValue>Unbarred

Re: xpath not giving result

niketn — Sun, 18 Mar 2018 04:40:07 GMT

@payal23 I think you would need to edit question and re-post the code and XML using code button 101010 provided on Splunk Answers so that special characters do not escape. Also if your indexed data is xml you can set KV_MODE=xml in props.conf to have xml nodes extracted automatically during search time.

Other option would be to pipe spath to your base search.

<yourBaseSearch>
| spath
| table *

Re: xpath not giving result

niketn — Sun, 18 Mar 2018 05:35:27 GMT

[Updated Answer] Query for correlating NewValue with NetworkSetting
Following query will find Barred NewValue for International Roaming Bar NetworkSetting

| makeresults
| eval _raw="<SiebelMessage TransactionName=\"A\" IntObjectName=\"B\" IntObjectFormat=\"C\">
    <ListOfVfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>Administration Bar</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue>Barred</NewValue>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>IDD Bar</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue/>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>Incoming Calls</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue/>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>International Roaming Bar</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue/>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
    </ListOfVfNetworkSettings>
</SiebelMessage>"
| eval _raw=replace(_raw,"\<NewValue\/\>","<NewValue>null</NewValue>")
| spath
| rename SiebelMessage.ListOfVfNetworkSettings.VfNetworkSettings.NewValue as NewValue SiebelMessage.ListOfVfNetworkSettings.VfNetworkSettings.NetworkSetting as NetworkSetting
| stats count by NewValue NetworkSetting
| search NewValue="Barred" AND NetworkSetting="International Roaming Bar"

PS: First two pipes makeresult and eval _raw is to mock data. You can use your base search instead.

@Payal23, Following is one of the options with spath (run anywhere search added based on sample data). I have replaced empty <NewValue/> with some default value for 1:1 mapping of CurrentValue and NewValue multi-value fields.
PS: As stated earlier if the event being indexed to Splunk is XML you can turn on KV_MODE=xml in props.conf

| makeresults
| eval _raw="<SiebelMessage TransactionName=\"A\" IntObjectName=\"B\" IntObjectFormat=\"C\">
    <ListOfVfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>Administration Bar</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue>Barred</NewValue>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>IDD Bar</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue/>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>Incoming Calls</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue/>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>International Roaming Bar</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue/>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
    </ListOfVfNetworkSettings>
</SiebelMessage>"
| eval _raw=replace(_raw,"\<NewValue\/\>","<NewValue>null</NewValue>")
| spath
| table *CurrentValue *NewValue

PS: If you take out the final table command you will see all the fields. If you do not want spath to extract all the fields similar to xpath, you can provide path for field extraction and output field name. Refer to spath command.

Re: xpath not giving result

payal23 — Sun, 18 Mar 2018 15:02:28 GMT

@niketnilay Actually my requirement is to calculate the count of the events when NewValue is Barred for International Roaming Bar.

And the xml pattern is like

International Roaming Bar can be in any VfNetworkSettings tag.
Tags present in between VfNetworkSettings tag can be in any sequence.

So, based on this condition can you help me in understanding how KV_MODE=xml or spath will help?

And if i ignore the 2nd point and just try to get the NewValue from the sample attached with the help of xpath, am not getting results.

|xpath outfield=NewValue "//SiebelMessage/ListOfVfNetworkSettings/VfNetworkSettings/[NetworkSetting=\"International Roaming Bar\"]/NewValue"|table NewValue

<SiebelMessage TransactionName="VF Update Network Settings" IntObjectName="VF Network Settings Update Request" IntObjectFormat="Siebel Hierarchical"><ListOfVfNetworkSettings><VfNetworkSettings><ARII>AU</ARII><CurrentValue>Unbarred</CurrentValue><NetworkSetting>International Roaming Bar</NetworkSetting><Type>Barring</Type><IMSI>234</IMSI><MSISDN>123</MSISDN><NeServiceName/><NewValue>Barred</NewValue><Attribute>Status</Attribute></VfNetworkSettings><VfNetworkSettings><ARII>AU</ARII><CurrentValue>Barred</CurrentValue><NetworkSetting>IDD Bar</NetworkSetting><Type>Barring</Type><IMSI>456</IMSI><MSISDN>123</MSISDN><NeServiceName/><NewValue>Unbarred</NewValue><Attribute>Status</Attribute></VfNetworkSettings></ListOfVfNetworkSettings></SiebelMessage>

Re: xpath not giving result

niketn — Sun, 18 Mar 2018 16:15:00 GMT

@payal23, KV_MODE=xml extracts all the field from XML data during search so that you do not have to go after all the field extractions. Similarly with spath command on _raw all xml nodes will be extracted automatically. You can choose the fields you are interested in.

Since your single xml has multiple <VfNetworkSettings> you would be working with multiple-values. Also some of the <NewValue> nodes in <VfNetworkSettings> are null. Which means multiple values might not get mapped one to one. For which I have populated null values in the raw event.

| eval _raw=replace(_raw,"\<NewValue\/\>","<NewValue>null</NewValue>")

As far as xpath is concerned I notice that the documented feature of the command does not seem to be working as expected. The following works | xpath outfield=NewValue "//SiebelMessage/ListOfVfNetworkSettings/VfNetworkSettings/NewValue" but this one does not | xpath outfield=NewValue "//SiebelMessage/ListOfVfNetworkSettings/VfNetworkSettings[NetworkSetting=\"Barred\"]/NewValue"

| makeresults 
| eval _raw="<SiebelMessage TransactionName=\"A\" IntObjectName=\"B\" IntObjectFormat=\"C\">
    <ListOfVfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>Administration Bar</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue>Barred</NewValue>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>IDD Bar</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue/>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>Incoming Calls</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue/>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
        <VfNetworkSettings>
            <CurrentValue>Unbarred</CurrentValue>
            <NetworkSetting>International Roaming Bar</NetworkSetting>
            <Type>Barring</Type>
            <IMSI>123</IMSI>
            <MSISDN>abcd</MSISDN>
            <SecondarySubs>N</SecondarySubs>
            <NeServiceName>ANHL|</NeServiceName>
            <NewValue/>
            <Attribute>Status</Attribute>
            <ServiceProvider>AU</ServiceProvider>
        </VfNetworkSettings>
    </ListOfVfNetworkSettings>
</SiebelMessage>" 
| eval _raw=replace(_raw,"\<NewValue\/\>","<NewValue>null</NewValue>") 
| xpath outfield=NewValue "//SiebelMessage/ListOfVfNetworkSettings/VfNetworkSettings[NetworkSetting=\"Barred\"]/NewValue" field=_raw

PS: Also noticed another issue with xpath command where extracting two fields in the same search where both fields are multi-valued then first field becomes single value instead of multi-values.

| xpath outfield=NewValue "//SiebelMessage/ListOfVfNetworkSettings/VfNetworkSettings/NewValue" 
| xpath outfield=NetworkSetting "//SiebelMessage/ListOfVfNetworkSettings/VfNetworkSettings/NetworkSetting"

Try my solution with spath from my previous answer and confirm.
Add BUG tag to your question.
If you have valid Splunk Entitlement please reach out to Splunk Support with the issue.

PS: On a different note, please add comment to specific thread rather than posting a new Answer 🙂

Re: xpath not giving result

payal23 — Mon, 19 Mar 2018 01:39:46 GMT

🙂

@niketnilay
I tried with spath.. but I am not sure why it is not retrieving any results. 😞

|spath output=NewValue path=SiebelMessage.ListOfVfNetworkSettings.VfNetworkSettings.NewValue|table NewValue

Re: xpath not giving result

niketn — Mon, 19 Mar 2018 01:59:12 GMT

@payal23, if my run anywhere example with makeresults is working then just adding | spath should extract all the fields. If it is not doing so you will need to confirm whether your individual event logged in Splunk is xml or does it have any text prefixed/suffixed to it? First you would need to strip out any content apart from xml from the raw data for spath to work. Please check data and event.

PS: following worked for me with makeresults and eval instead of <YourBaseSearch>

<YourBaseSearch>
| eval _raw=replace(_raw,"\<NewValue\/\>","<NewValue>null</NewValue>")
| spath output=NewValue path=SiebelMessage.ListOfVfNetworkSettings.VfNetworkSettings.NewValue
| spath output=NetworkSetting path=SiebelMessage.ListOfVfNetworkSettings.VfNetworkSettings.NetworkSetting

Re: xpath not giving result

payal23 — Mon, 19 Mar 2018 02:40:55 GMT

@niketnilay Yes.. makeresults query is working fine.

And also the event is not purely XML.. it has few lines before XML. If i want to strip out the data have to create a new sourcetype and filter the events to get XML

.## 18 Mar 2018 11:10:07 [INFO] BusinessIdentifier : 123 **** MessageIdentifier : bc2 **** InterfaceName : UpdateNetworkSettings **** ServiceLayerName : CRMServiceMgmtCVS **** ServiceLayerOperation : VF Update Network Settings **** ServiceLayerPipeline : requestPipeline ErrorCode : **** ConsumerErrorMessage : **** FusionErrorCode : **** FusionErrorMessage : **** FaultingServiceErrorCode : **** FaultingServiceErrorMessage : <ListOfVfNetworkSettings>.. rest xml

With the help of regex or anything else can we extract the same? 😞

Re: xpath not giving result

niketn — Mon, 19 Mar 2018 03:00:02 GMT

@payal23 try the following rex to extract xml data from your raw events as first step.

PS: As per the xml sample data shared initially the root node should be <SiebelMessage not <ListOfVfNetworkSettings. Since Regular Expression is based on pattern match, the rex command will work only with correct pattern based on your actual event. I have created Regular Expression based on the following to be the pattern: FaultingServiceErrorMessage : <SiebelMessage. Please correct if the pattern is different.

(?<ms>) has been added to rex command to make dot (.) to match new line character as well in case xml has new line characters. Other option would be to remove new line character \n\r from _raw data before applying rex.

The rex pattern ends at <\/SiebelMessage> to get xml data as _raw.

<YourBaseSearch>
| rex "(?ms)FaultingServiceErrorMessage : (?<_raw>\<SiebelMessage.*\<\/SiebelMessage\>)"
| eval _raw=replace(_raw,"\<NewValue\/\>","<NewValue>null</NewValue>")
| spath
| rename SiebelMessage.ListOfVfNetworkSettings.VfNetworkSettings.NewValue as NewValue SiebelMessage.ListOfVfNetworkSettings.VfNetworkSettings.NetworkSetting as NetworkSetting
| stats count by NewValue NetworkSetting
| search NewValue="Barred" AND NetworkSetting="International Roaming Bar"

Re: xpath not giving result

payal23 — Tue, 20 Mar 2018 06:14:08 GMT

@niketnilay Regex is not working.

Is this regex working on _raw events.. I am not that good in regex.

| rex "(?ms)FaultingServiceErrorMessage : (?<_raw>\)"|table _raw

When I do this it is not filtering _raw logs.

Re: xpath not giving result

niketn — Tue, 20 Mar 2018 06:48:50 GMT

Following is the link to regex I have tested with the sample data provided on regex101

Like I have mentioned before, regular expressions are heavily based on pattern in the text data. You should at least check the raw sample event whether it matches the pattern of data you have or not.

Re: xpath not giving result

payal23 — Tue, 20 Mar 2018 14:01:50 GMT

@niketnilay yayyyyy...its working finally!!!

Thankyou so much 🙂

|rex "FaultingServiceErrorMessage : [\n]+(?<_raw>\<SiebelMessage.*\<\/SiebelMessage\>)"

Re: xpath not giving result

rakesh_498115 — Thu, 22 Mar 2018 02:35:45 GMT

Hi Payal..

You can use the below regex for extracting the New value tag for International Roaming call...

(?<=VfNetworkSettings).International\sRoaming\sBar.?NewValue>(?[^<]+)<

In Case of this field is null , you may need to check the isnull function of splunk to create a appropriate field.

i.e like below

<< Your base query " | rex "(?<=VfNetworkSettings).International\sRoaming\sBar.?NewValue>(?[^<]+)<"| eval ActualField = if(isnull(MyField),"NA",MyField) | stats count by ActualField

This will give u the count based on NewValue msg types,if its null we are creating "NA" msg.

Hope this helps !!

Happy Splunking 🙂

Re: xpath not giving result

niketn — Thu, 22 Mar 2018 05:26:15 GMT

@payal23 glad you found a solution working for you.

Seems like you accepted your own answer instead of mine. You can either accept my answer or up vote the comments that helped 🙂