Dashboards & Visualizations

How to insert a value like "not_defined" into a null field of an xml file

Explorer

How can I insert a value like "not_defined" into any null field of an XML file during a splunk search.

Please note that the XML file has the one field occurring multiple times in the one event.

Example: (single event)

<RIMP>
    <server>
        <name>abc1</name>
        <version>123</version>
        <ip>1.2.3.4</ip>
    </server>
    <server>
        <name>abc2</name>
        <version>123</version>
        <ip>1.2.3.5</ip>
    </server>
    <server>
        <name></name>
        <version></version>
        <ip>1.2.3.6</ip>
    </server>
    <server>
        <name>abc4</name>
        <version></version>
        <ip>1.2.3.7</ip>
    </server>
</RIMP>
0 Karma

SplunkTrust
SplunkTrust

I wouldn't recommend sed'ing through _raw for this.

Instead, you should extract your fields as normal and can then set default values for fields in your search like this:

base search | eval field = coalesce(field, "not_defined") | ...

That'll set field to "not_defined" if and only if field is null. Move this to a macro if you intend to use it often.

0 Karma

SplunkTrust
SplunkTrust

That's what both fillnull and coalesce() do. Could it be that some cells actually contain empty strings or spaces rather than null values? Alternatively, do the other columns contain multi-value fields so there actually are only three rows?

0 Karma

SplunkTrust
SplunkTrust

From what I can read in that table it did insert not_defined into a field that used to be null. How does that differ from what you're looking for?

0 Karma

Explorer
 RIMP.server.name    RIMP.server.version    RIMP.server.ip
 abc1               123         1.2.3.4
 abc2               123         1.2.3.5
                    not_defined     1.2.3.6
 abc4               not_defined     1.2.3.7
 abc5               not_defined     1.2.3.8

Looking for something like this, where "not_defined" value is populated everywhere it encounters a null value for that field.

0 Karma

Explorer

Martin, thank you for your reply but this did not work as I wanted:

What it did was exactly "fillnull" was doing.

base search | eval RIMP.SERVER.VERSION = coalesce(RIMP.SERVER.VERSION, "not_defined") | table fields...

RIMP.server.name    RIMP.server.version RIMP.server.ip
abc1            123         1.2.3.4
abc2            123         1.2.3.5
            not_defined     1.2.3.6
abc4                        1.2.3.7
abc5                        1.2.3.8

What I was looking for was "not_defined" specified for every entry when I pipe it to Table command.

0 Karma

Motivator

Hello! Here his what you can do:

First extract null values(As you wil see, since it is not possible to extract a null value, my null values here are <), and then replace them with not_defined in your events, using mod=sed, (but i will replace them with >not_defined<) and i think you wll understand why.

...|rex field=_raw mode=sed "s/\>(?<myfilnul>\<)/>not_defined</g"

Now in your events you will have for example rows like

<version>not_defined</version>

You can now extract both fields and their values, and display them in your table, like this:

...|rex field=_raw mode=sed "s/\>(?<myfilnul>\<)/>not_defined</g"|rex field=_raw "\<(?<myfield>[^\/\>]+)\>(?<myfilnul>[^\<]+)"|table myfield myfilnul

Thanks.

0 Karma

Explorer

Below is the output when piped to "table" command:

RIMP.server.name    RIMP.server.version RIMP.server.ip
abc1            123         1.2.3.4
abc2            123         1.2.3.5
                            1.2.3.6
abc4                        1.2.3.7
abc5                        1.2.3.8

"not_defined" not coming up anywhere..

0 Karma

Explorer

This is the actual query I am using, similar to field names in the simplified XML I gave as an example.

index=test sourcetype=iloscan | search RIMP.INFRA2.ENCL=*-po-* | table RIMP.INFRA2.ENCL, RIMP.INFRA2.BLADES.BLADE.NAME, RIMP.INFRA2.BLADES.BLADE.SPN, RIMP.INFRA2.BLADES.BLADE.MGMTFWVERSION, RIMP.INFRA2.BLADES.BLADE.STATUS | dedup RIMP.INFRA2.ENCL
0 Karma

Motivator

OK! I will help you get the table above another way. Please try this and let me know if you have something:

 index=test sourcetype=iloscan|rex field=_raw max_match=0 mode=sed "s/\>(?<myfilnul>\<)/>not_defined</g"|rex field=_raw max_match=0 "\<name\>(?<servername>[^\<]+)"|rex field=_raw max_match=0 "\<version\>(?<version>[^\<]+)"|rex field=_raw max_match=0 "\ip\>(?<ip>[^\<]+)"|table servername version ip
0 Karma

Explorer

This looks great! How would the sed cmd solution to work for an empty shortcut element
i.e. no closing tag
<DriverVersion />
Thanks

0 Karma

SplunkTrust
SplunkTrust

You certainly can build a regular expression for that, something along these lines: s/<([^>]+)/>/<\1>not_defined</\1>/g
There may be more work necessary to accommodate attributes, and maybe more.

0 Karma

Motivator

Let me get the search query you are using please.

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes and swag!