Splunk Search

How do I dedup duplicate values including that value itself?

charlottelimcl
Engager

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

Labels (2)
0 Karma

charlottelimcl
Engager

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

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...