topic Re: How can I remove a second log entry from a query that seems to be a duplicate. in Splunk Dev

How can I remove a second log entry from a query that seems to be a duplicate.

jkinny — Fri, 30 Nov 2018 17:28:37 GMT

I am using the transaction function to group several log entries by a 'claimID' field. I've noticed that when I do this, for each 'claimID' I am getting an extra log entry that gets returned 'outside' of the transaction. For all intents and purposes this second entry is a duplicate of the 'endswith' parameter of the transaction. I've tried using the 'dedup' function by the 'claimID' field, but it only seems to throw away the oldest result and keep the newest, as in, it removes the transaction block that I want, but keeps the duplicate. (in the attached screenshot, dedup would keep the top entry and remove the bottom transaction-fied entry)

query: index=prd sourcetype=app source="/logs/app.log" ("editCode=CA010" OR "status=A") | transaction claimID endswith="status=A"

Re: How can I remove a second log entry from a query that seems to be a duplicate.

tom_frotscher — Mon, 03 Dec 2018 10:23:32 GMT

Hi,

you can still use dedup. Just use the sortby clause of dedup. For your example, you could use | dedup claimID sortby +_time

You can also use sortby -_time if you need it the other way around for other searches.

Over all, i think you can improve your search in regard to performance by using a clever combination of stats or streamstats and some more splunk spl magic. If needed, you get some more examples here: https://answers.splunk.com/answers/103/transaction-vs-stats-commands.html and here https://www.splunk.com/blog/2012/11/29/book-excerpt-when-to-use-transaction-and-when-to-use-stats.html

Greetings,

Tom

Re: How can I remove a second log entry from a query that seems to be a duplicate.

jkinny — Tue, 29 Sep 2020 22:16:50 GMT

Thanks for the response Tom, I'll respond to your second answer first, unless I'm misunderstanding, I think that based on what I'm trying to do, I might need to stick with transaction. I'm basically trying to find instances where status=A exists, but there was no prior log message for the same claimID that shows editCode=CA010. The last section to the query here should turn up any 'orphans' (I don't know what you call an orphan that has a closing entry, but no beginning entry).

so I tried adding both | dedup claimID sortby +_time and | dedup claimID sortby -_time to the end of my query, and unfortunately both seem to return the same result, and neither is the one that I want. If you look at my screenshot, adding the dedup/sortby command is returning only the top result in my log (@ .716) not the bottom (@.256)

Re: How can I remove a second log entry from a query that seems to be a duplicate.

tom_frotscher — Tue, 29 Sep 2020 22:15:49 GMT

Thats strange,
i tried it for myself with a transaction search and it worked like i thought it would work.

However, you can still do some streamstats and search magic. Append something like this to your search:

| sort limit=0 _time | streamstats count by group | search count=1 | sort limit=0 -_time

Does this help?

Re: How can I remove a second log entry from a query that seems to be a duplicate.

jkinny — Tue, 29 Sep 2020 22:17:26 GMT

Thanks!! It turned out to be a mix, I ended up doing this: | sort limit=0 _time | dedup claimID sortby +_time

The 'sort limit' seems to have arranged the list in the correct order for the 'sortby' to work correctly this time. I honestly don't know why your first answer didn't work, but glad to have gotten this anyway!

Re: How can I remove a second log entry from a query that seems to be a duplicate.

tom_frotscher — Tue, 04 Dec 2018 16:43:13 GMT

Nice!

be aware, sort is quite performance heavy. Therefore, it is limited to 10,000 events by default, which in this case we exceeded with the help of the limit=0 option.

But if your search is still fast enough for your use case, you now have a solution 🙂