topic Re: How to remove duplicate data and how to prevent having duplicate data? in Getting Data In

How to remove duplicate data and how to prevent having duplicate data?

edrivera3 — Fri, 30 Oct 2015 15:43:47 GMT

I have many configuration text file which basically looks like this:
Owner Name: AAAAA AAAAA
Product Name: AAAA AAAA
Product ID: NNNNN-NN Serial ID: NN-NN-NN-NNNNN

Sometimes there is change in the product ID or serial ID and I want to index the new change but I don't want to keep old event. Basically, I want to replace the old configuration file with the new one.

I tried the below inputs.conf because some files where are not getting index because of the similarity between them. Every thing was fine until I found out that every time there is change in the configuration text file, the file is index but it doesn't replace the old one. So now I have multiple configuration files with the same source which is a problem.

[Monitor://Some directory]
index = my_index
sourcetype = my_sourcetype
crcSalt = <SOURCE>

(1) Right now I need to delete all events that have already a new version of them based on the _indextime.
(2) I need a new inputs.conf setup that will prevent this behavior.

Re: How to remove duplicate data and how to prevent having duplicate data?

richgalloway — Fri, 30 Oct 2015 16:00:43 GMT

Splunk does not have a "index this only if it's not already indexed" feature. The performance of such feature probably would be poor. Nor will it replace or update anything already indexed.
You can remove duplicate data (or any data) by piping a search to the delete command.

Re: How to remove duplicate data and how to prevent having duplicate data?

edrivera3 — Fri, 30 Oct 2015 16:07:57 GMT

Ok. That's too bad. But how to make Splunk delete events that has a new version of them? I know about the delete command, but I haven't been successful to select the appropriate data. With the below command I've been able to see the indextime and which files have more than two files for the same source.
... | eval indextime=strftime(_indextime,"%Y-%m-%d %H:%M:%S") | stats count by source| where count>1

Re: How to remove duplicate data and how to prevent having duplicate data?

woodcock — Fri, 30 Oct 2015 16:44:36 GMT

This should be the opposite of dedup:

... | eventstats max(_indextime) AS latestIndexTime by source | where _indextime<latestIndexTime

Then you just pipe that to delete by adding this:

... | delete

Re: How to remove duplicate data and how to prevent having duplicate data?

edrivera3 — Fri, 30 Oct 2015 16:51:36 GMT

The only way I have found is deleting file at a time which is very inefficient.

Re: How to remove duplicate data and how to prevent having duplicate data?

richgalloway — Fri, 30 Oct 2015 17:10:35 GMT

You need to have permission to use the delete command. That's the best way to remove events from Splunk.

Re: How to remove duplicate data and how to prevent having duplicate data?

edrivera3 — Fri, 30 Oct 2015 17:12:35 GMT

I have permission to use the delete command, the problem is that I don't know how to select the appropriate data for deleting.

Re: How to remove duplicate data and how to prevent having duplicate data?

woodcock — Fri, 30 Oct 2015 17:22:57 GMT

See my Answer!

Re: How to remove duplicate data and how to prevent having duplicate data?

edrivera3 — Fri, 30 Oct 2015 17:28:27 GMT

Your command is perfect for selecting the events, but I encountered the following error when added the delete command.
Error in 'delete' command: This command cannot be invoked after the non-streaming command 'eventstats'.
The search job has failed due to an error. You may be able view the job in the Job Inspector.

I am going to retry to run the command, but for some reason it takes so much time to run.

Re: How to remove duplicate data and how to prevent having duplicate data?

edrivera3 — Fri, 30 Oct 2015 17:32:41 GMT

I got the same error. My roles are can_delete, user, power.

Re: How to remove duplicate data and how to prevent having duplicate data?

richgalloway — Fri, 30 Oct 2015 17:33:28 GMT

The first part of the query can be as simple as index=foo sourcetype=my_sourcetype.

Re: How to remove duplicate data and how to prevent having duplicate data?

richgalloway — Fri, 30 Oct 2015 17:42:52 GMT

Try this instead.

index=foo sourcetype=my_sourcetype | eval oldest=relative_time(now(),"-1d@d") | where _indextime<oldest

Adjust the arguments to relative_time as needed.

Re: How to remove duplicate data and how to prevent having duplicate data?

woodcock — Fri, 30 Oct 2015 17:52:27 GMT

My solution will not work then, because evidently the use of eventstats precludes the use of delete (which, IMHO, is definitely a bug).

Re: How to remove duplicate data and how to prevent having duplicate data?

edrivera3 — Fri, 30 Oct 2015 17:52:44 GMT

I don't see how this solve my problem. Could you elaborate or explain more your solution?
now()= is the time when the search started
oldest = is 1 day before the search request started

Basically I need a solution that provides the same results than woodcock's solution but without using eventstat.

Re: How to remove duplicate data and how to prevent having duplicate data?

woodcock — Fri, 30 Oct 2015 18:19:56 GMT

OK, try this then:

... NOT [... | sort - _indextime | dedup source | fields _raw]

If this looks correct (has only the bad stuff), then it should be safe to pipe this to | delete.

Re: How to remove duplicate data and how to prevent having duplicate data?

edrivera3 — Fri, 30 Oct 2015 18:52:27 GMT

It is very interesting your idea, but it didn't work and I am not sure why.

The right side search provides all the good data, so basically the Boolean operator NOT should eliminate the good data from the total data leaving only the bad data.

Re: How to remove duplicate data and how to prevent having duplicate data?

woodcock — Fri, 30 Oct 2015 19:05:49 GMT

What is the result of this search?

... | sort - _indextime | dedup source | fields _raw | format

It should have 1 field called search that has a list of OR on _raw.

Re: How to remove duplicate data and how to prevent having duplicate data?

edrivera3 — Fri, 30 Oct 2015 19:11:26 GMT

This search provided all the right data. If I look in Statistics I have a table with one row:

_raw                           search
                                    NOT ()

Re: How to remove duplicate data and how to prevent having duplicate data?

woodcock — Fri, 30 Oct 2015 19:24:23 GMT

That's all I've got. Play around and see if you can make it work and update this Q&A with what you find.

Re: How to remove duplicate data and how to prevent having duplicate data?

edrivera3 — Tue, 29 Sep 2020 07:47:20 GMT

This solution comes basically from wookcook's idea.

I eliminate the

| sort - indextime

because it wasn't running correctly for me.
I replaced the _raw field for two fields in my data and the indextime field.

... | eval indextime=strftime(_indextime,"%Y-%m-%d %H:%M:%S") | search index=* NOT [...| eval indextime=strftime(_indextime,"%Y-%m-%d %H:%M:%S") | dedup source | fields field1, field2 ,indextime]