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>
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.
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?
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?
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.
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.
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.
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..
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
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
This looks great! How would the sed cmd solution to work for an empty shortcut element
i.e. no closing tag
<DriverVersion />
Thanks
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.
Let me get the search query you are using please.