topic Re: How do I dedup duplicate values including that value itself? in Splunk Search

How do I dedup duplicate values including that value itself?

charlottelimcl — Mon, 03 Oct 2022 09:14:19 GMT

Hi everyone, I am new to splunk. I am looking at windows event logs for the EventCode=4725 for all usernames within a week's timeframe. What I want is to remove username results if there are more than 1 count for this eventcode including that username, and then list in a table to show the timestamp and username when the eventcode occurred.

Example:

Usernames with EventCode=4725 recorded within 1 week:

Day 1 10pm : anna

Day 1 11pm : betty

Day 3 10pm : anna

Day 3 1pm : charlie

Day 7 2pm : zach

Final result I want is:

Day 1 11pm : betty

Day 3 1pm : charlie

Day 7 2pm : zach

From the above we have 'anna' removed completely from as her event showed up more than once.

This is my original query:

index=wineventlog EventCode=4725
| fields *
| eval timestamp=strftime(_time, "%Y-%m-%dT%H:%M"%S")
| stats count by username | where username = 1

I then realised the problem with using stats count by, because I wouldnt be able to show the timestamp for the results result this is in statistics.

I have thought of using dedup to remove duplicate values, but I have not found a way to remove duplicate values including that value itself.

Please help. Thank you

Re: How do I dedup duplicate values including that value itself?

gcusello — Mon, 03 Oct 2022 09:26:06 GMT

Hi @charlottelimcl,

let me understand:

you want to display only usernames that are only one time in your events, is this corret?

if this is your need, please try this:

index=wineventlog EventCode=4725 | stats count BY username | where count=1

Ciao.

Giuseppe

Re: How do I dedup duplicate values including that value itself?

charlottelimcl — Mon, 03 Oct 2022 09:50:10 GMT

Thanks for the reply. I wanted to have the timestamp of the occurrence as well. I went to do more research and apparently I can add this:

| stats count as count, earliest(_time) by username | where count=1

Re: How do I dedup duplicate values including that value itself?

gcusello — Mon, 03 Oct 2022 09:55:59 GMT

Hi @charlottelimcl,

yes, it's correct.

index=wineventlog EventCode=4725 | stats count earliest(_time) AS timestamp BY username | where count=1 | eval timestamp=strftime(timestamp,"%Y-%m-%dT%H:%M"%S")

Ciao.

Giuseppe