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
Esteemed Legend

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
Esteemed Legend

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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...