topic Re: Regex to extract numbers from non unique string in Splunk Search

Regex to extract numbers from non unique string

ahogbin — Mon, 29 Aug 2016 04:18:01 GMT

Hello,

I am hoping that someone with far more knowledge than myself can help with a bit of a puzzling problem I have with trying to extract some numbers from a non unique string

The numbers I am trying to extract are in the <_0:EndOfTermAmount>999.30</_0:EndOfTermAmount>
but this issue I have is that this is no a unique identifier (ie the same string appears in several other places within the XML.

The only unique thing is that the numbers I am after are always contained within

<_0:AALNet>
            <_0:AnnualAmount>0.00</_0:AnnualAmount>
            <_0:MonthlyAmount>0.00</_0:MonthlyAmount>
            <_0:FortnightlyAmount>0.00</_0:FortnightlyAmount>
            <_0:EndOfTermAmount>999.30</_0:EndOfTermAmount>
            <_0:ComparisonAmount>0.00</_0:ComparisonAmount>
            <_0:InstallmentGapAmount>0.00</_0:InstallmentGapAmount>
          </_0:AALNet>

Is there a way of using the _0:AALNET as a key to then extract the required number (in this case 999.30) using regex (or some other means) ? - the length of the numbers to be extracted can vary.

I am totally stumped on this one so any help or pointers will be greatly appreciated.

Cheers.

Alastair

Re: Regex to extract numbers from non unique string

svenwendler — Mon, 29 Aug 2016 04:35:16 GMT

Try this:

(?s)<_0:AALNet>.*<_0:EndOfTermAmount>(?P<mynumber>[^<]+)

(?s) modifies to include newline characters - i.e. make it a single line. And "mynumber" will be 999.30

Re: Regex to extract numbers from non unique string

acharlieh — Mon, 29 Aug 2016 04:43:19 GMT

If you're doing this in a search, could you use either the xpath command or the spath command to help?

Based on your fragment, and not an entire sample, maybe something like this:

... | xpath outfield=EndOfTermAmount "//*[local-name()='AALNet']/*[local-name()='EndOfTermAmount']"

(Depending on the overall structure of the XML, there could be nuances to xpath and spath, but hopefully this points you in a good direction... stupid xpath and namespace issues... you may be interested in this stack overflow answer also this other stackoverflow answer)

EDIT TO ADD: Out of frustration with the xpath command and it not supporting namespaces unless done in a really ugly manner using namespace-uri()= at each level, I logged P4 case 389100 asking to have namespace binding added to the xpath command.

Re: Regex to extract numbers from non unique string

ahogbin — Mon, 29 Aug 2016 05:03:22 GMT

Perfect... both options worked it is just the xpath command allows me to extract 'all' variances (I had not realised what I thought was unique was repeated).

Re: Regex to extract numbers from non unique string

ahogbin — Mon, 29 Aug 2016 05:11:54 GMT

One further question... I end up with 3 values but I only need one. The XML component for the value I am after also contains <_0:AnnualAmount> could I somehow use this in the xpath query to limit the results returned ?

Cheers

Re: Regex to extract numbers from non unique string

acharlieh — Mon, 29 Aug 2016 05:29:11 GMT

xpath itself is a rather powerful language with lots of functions available and all kinds of ways to add additional qualifiers. (I'm not quite sure what version Splunk uses under the hood, so couldn't tell you what would work and what wouldn't without trying some things)...

But taking a whack at your request, select an EndOfTermAmount whose parent AALNet element also has a child AnnualAmount element would be something like:

... | xpath outfield=test "//*[local-name()='AALNet' and *[local-name()='AnnualAmount']]/*[local-name()='EndOfTermAmount']"

I figured that out from another stackoverflow post

Re: Regex to extract numbers from non unique string

ahogbin — Mon, 29 Aug 2016 05:32:02 GMT

Works a treat.. thank you so much.

Alastair

Re: Regex to extract numbers from non unique string

acharlieh — Mon, 29 Aug 2016 05:47:58 GMT

One other note, I see you're using the "award points" link. Instead of doing that, for content (questions, answers, comments) that you believe is particularly good (helpful, useful, amusing, etc.), you can instead use the vote up link (^). Not only does this not cost you any karma points, but it marks that the content was particularly worthwhile to someone (and still awards karma points to the creator of said worthwhile content).

As a moderator, I gave you the 101 points that you gave away in this manner so far. While you can still use award points for something that is mind blowing beyond belief, that requires both a vote up and then extra points, save your karma for those special occasions, and vote first 🙂

Re: Regex to extract numbers from non unique string

ahogbin — Mon, 29 Aug 2016 06:27:23 GMT

Thank you so much 🙂