Splunk Search

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

samfisher1
Engager

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?

Labels (1)
Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

gcusello
SplunkTrust
SplunkTrust

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
Super Champion

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

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