Dashboards & Visualizations

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

ayenumula
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

martin_mueller
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

martin_mueller
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

martin_mueller
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

ayenumula
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

ayenumula
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

stephanefotso
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.

SGF
0 Karma

ayenumula
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

ayenumula
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

stephanefotso
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
SGF
0 Karma

calebwidmer
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

martin_mueller
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

stephanefotso
Motivator

Let me get the search query you are using please.

SGF
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...