How can I delete the event data when some event fields value is "None" or "Nan" in Splunk?

[samfisher1]

Hello Guys,
Sorry for blasting...
When I input data into Splunk, I find some field values in the events are "None" or "Nan" or "". How can I delete these events which contain the blank values in Splunk? Or is there any way to drop these events when inputting these data?

[gcusello · Accepted solution]

Hi @samfisher1,
You have three ways to delete events in Splunk:

1. before indexing;
2. from Splunk interface using the delete command;
3. in CLi using the clean command.

In detail:

1)
you can filter events before indexing using the steps described at https://docs.splunk.com/Documentation/Splunk/8.0.4/Forwarding/Routeandfilterdatad#Filter_event_data_... , in few words you have to find a regex to take all the events (if you share a sample of the logs to filter I can help you) and put in props.conf:

[your_sourcetype]
TRANSFORMS-null= setnull

In transforms.conf:

[setnull]
REGEX = your_regex
DEST_KEY = queue
FORMAT = nullQueue

As regex you could use

REGEX = None|Nan

for the first two values, but per the value="" I cannot help you without a sample of these logs.

This is the best way to filter events because you do this before indexing so you don't consume license.

2)
you can use the delete command at the end of a search but it isn't an efficient method because it's a logic deletion, so the events remain in the buckets and you already consumed license for indexing.
In addition, it isn't a best practice to give the role "can_delete" to many users, so i cannot hint this method: I use it only in development on test archives and with much, much attention, changing my role to can_delete only for a short time!

3)
the third method, I think, isn't useful for you because permits to delete an entire index, it isn't selective, and anyway you already indexed logs, so you consumed license.

At the end the best approach is the first one.

Ciao.
Giuseppe

[493669]

If you want to remove those field values at search time you can remove using following query-

...|eval field=if(field="None" OR field="Nan" OR field="",NULL,field )|where isnotnull(field)

here replace field with actual field name