topic Re: XML data - multi value field extraction without using xpath in Splunk Search

XML data - multi value field extraction without using xpath

meno — Mon, 29 Nov 2010 00:11:42 GMT

I got stuck with extracting a multi value field from XML data:

<Results>
    <Result>
        <Grade>Error</Grade>
        <MachinesFound>0</MachinesFound>
        <Machines>
        </Machines>
    </Result>
    <Result>
        <Grade>Critical</Grade>
        <MachinesFound>3</MachinesFound>
        <Machines>
            <Machine path="some data">BIZ\TOTO</Machine>
            <Machine path="some data">BIZ\TATA</Machine>
            <Machine path="some data">BIZ\TUTU</Machine>
        </Machines>
    </Result>
    <Result>
        <Grade>Warning</Grade>
        <MachinesFound>2</MachinesFound>
        <Machines>
            <Machine path="some data">BIZ\TCTC</Machine>
            <Machine path="some data">BIZ\TZTZ</Machine>
        </Machines>
    </Result>
    <Result>
        <Grade>Passed</Grade>
        <MachinesFound>1</MachinesFound>
        <Machines>
            <Machine path="some data">BIZ\TETE</Machine>
        </Machines>
    </Result>
    <Result>
        <Grade>NotPerformed</Grade>
        <MachinesFound>0</MachinesFound>
        <Machines>
        </Machines>
    </Result>
</Results>

I am already extracting Grade and the concerning amount of MachinesFound:

[gradeextr]
REGEX = \<Grade\>([^\<]+)\</Grade\>.*?\<MachinesFound\>(\d+)\</MachinesFound\>
FORMAT= $1::$2

What is missing now are the concerning Machine names.

xpath is easy...

 *| xpath "//Results/Result[Grade=\"Critical\"]/Machines/Machine" outfield=critbox

...but unfortunately not an option.

So I want to go via props/transforms.conf:

REGEX = \<Grade\>Critical\</Grade\>.+?\<Machines\>.+\<Machine path="[^\>]*"\>([^\<]+)\</Machine\>.+
FORMAT = critbox::$1

When I try to extract multi values with...

MV_ADD = true

...the critbox field has 2 similar(!) values.

Is it possible to extract a multi value field without xpath using props/transforms.conf ?

I tried also with using fields.conf and TOKENIZER= without any success.

Thanks for any new ideas...

Re: XML data - multi value field extraction without using xpath

araitz — Mon, 29 Nov 2010 10:08:43 GMT

From your explanation, I can't see a reason that you must extract using that one regex. This works better:

[regex1]
REGEX = \<Grade\>([^\<]+)\</Grade\>
FORMAT= grade::$1

[regex2]
REGEX = <MachinesFound\>(\d+)\</MachinesFound\>
FORMAT= machine_count::$1

[regex3]
REGEX = \<Machine path="[^\>]*"\>([^\<]+)
FORMAT= machine_name::$1
MV_ADD= true

Then:

... | stats sum(machine_count) as total_count values(machine_name) as machine_names by grade

Re: XML data - multi value field extraction without using xpath

meno — Mon, 29 Nov 2010 23:58:47 GMT

I added more data to show the idea behind it.

Re: XML data - multi value field extraction without using xpath

meno — Tue, 30 Nov 2010 00:00:14 GMT

I do not need the path field. I want to create reports based on the amount of machines and their machinename, that are critical, error, warning,... machines. So critical, error, warning and so on must be connected with the concerning amount of machines.

Re: XML data - multi value field extraction without using xpath

araitz — Tue, 30 Nov 2010 00:15:01 GMT

Ah, I see. I will add another extraction above.

Re: XML data - multi value field extraction without using xpath

meno — Thu, 02 Dec 2010 01:25:41 GMT

Opened a bounty as this question gets a showstopper. Anybody could help us finding the error or confirm that this is not possible with props/transforms? Thanks a lot...

Re: XML data - multi value field extraction without using xpath

ziegfried — Sun, 05 Dec 2010 00:52:47 GMT

Here you go:

props.conf

[<your_sourcetype>]
REPORT-extract-crit=extract_crit

transforms.conf

[extract_crit]
REGEX = (?ms)\<Grade\>Critical\</Grade\>.+?\<Machines\>\s*(.+?)\s*\</Machines\>
FORMAT = critbox::$1

fields.conf

[critbox]
TOKENIZER=\<Machine.+?\>([^\<]+)

It would be possible without the TOKENIZER if the PCRE implementation would support variable-length look-behinds with an expression like this:

[test_crit]
REGEX = (?ms)(?<=\<Grade\>Critical\</Grade\>.+?\<Machines\>.+?(?\<Machine path="[^\>]*"\>[^\<]+\</Machine\>\s+)?)\<Machine path="[^\>]*"\>([^\<]+)\</Machine\>
FORMAT = critbox::$1
MV_ADD=true

I think support for this has been added to python 2.7 (Splunk's using 2.6.4)

Re: XML data - multi value field extraction without using xpath

meno — Sun, 05 Dec 2010 19:55:17 GMT

Hi Siegfried, you are my RegEx Hero! It's working now...
Bounty is for you 😉

Ich habe mir daran echt die Zähne ausgebissen.
Die Lösung bleibt im Alpenraum 😉 Viele Grüsse aus der Schweiz...

Re: XML data - multi value field extraction without using xpath

ziegfried — Mon, 06 Dec 2010 01:40:36 GMT

Freut mich, dass ich helfen konnte 😉 Sieht man sich im Jänner in San Francisco? Liebe Grüße aus Wien.