Splunk Search

Search query: Unique values based on time

siddharth1479
Path Finder

Hi Community,
I'm using the search query to search for the user activity and I get the results with duplicate rows with the same user with the same time. The time format is as follows: YYYY-DD-MM HH:MM:SS:000. I get the result as following:

USER | TIME
abcd | 2020-06-01 08:58:51
abcd | 2020-06-01 08:58:51
abcd | 2020-06-01 08:58:51

abcd | 2020-06-01 09:32:27
abcd | 2020-06-01 09:32:27
abcd | 2020-06-01 09:32:27


The output I desire is:

USER | TIME
abcd | 2020-06-01 08:58:51
abcd | 2020-06-01 09:32:27


Search query I'm using is:
index="uam" User="abcd" | eval access_time=strftime(_time, "%Y-%d-%m %H:%M:%S") | fields "USER" "TIME"


How do I get the unique values, because it seems that Splunk compares the time upto milliseconds.
Can anyone please help me out?

Thanks,
Sid

0 Karma
1 Solution

willemjongeneel
Communicator

Hello,

Maybe you can try to dedup using a stringconcat on user and time fields?

Something like:

index="uam" User="abcd"
| eval access_time=strftime(_time, "%Y-%d-%m %H:%M:%S")
| rename access_time as TIME
| eval key=tostring(TIME)+tostring(User)
| dedup key
| table User, TIME

Kind regards,
Willem Jongeneel

View solution in original post

0 Karma

Vijeta
Influencer

@siddharth1479 Try this

index="uam" User="abcd" | eval access_time=strftime(_time, "%Y-%d-%m %H:%M:%S") | rename access_time as TIME|fields "USER" "TIME"| dedup USER TIME
0 Karma

siddharth1479
Path Finder

Hi,
Thanks for the help. Dedup logic seems to be working.

0 Karma

willemjongeneel
Communicator

Hello,

Maybe you can try to dedup using a stringconcat on user and time fields?

Something like:

index="uam" User="abcd"
| eval access_time=strftime(_time, "%Y-%d-%m %H:%M:%S")
| rename access_time as TIME
| eval key=tostring(TIME)+tostring(User)
| dedup key
| table User, TIME

Kind regards,
Willem Jongeneel

0 Karma

siddharth1479
Path Finder

Hi,
Thanks for the help. Seems to be working with this logic and also made some changes to the logs itself to uniquely identify each entry only once.

0 Karma

heissenberg
New Member

index="uam" User="abcd"
| eval access_time=strftime(_time, "%Y-%d-%m %H:%M:%S")
| fields "USER" "TIME"
| dedup "USER","TIME"

0 Karma

siddharth1479
Path Finder

Hi,
I think you need to rename the access_time as TIME, and yes dedup logic seems to be working.

Thanks for the help.

0 Karma

to4kawa
Ultra Champion
index="uam" User="abcd" 
 | eval access_time=strftime(_time, "%Y-%d-%m %H:%M:%S")
 | stats values(User) as USER by access_time
 | rename access_time as TIME

hi, try this.

0 Karma

siddharth1479
Path Finder

Hi, sorry for late reply. I tried this but it isn't helping. I still get duplicate "Seconds".

0 Karma

to4kawa
Ultra Champion

@siddharth1479
|stats values(User) as USER by access_time
This will be aggregated by the access_time string.
Are they really duplicates?
Please show me the results.

0 Karma

to4kawa
Ultra Champion
| makeresults
|eval _raw="USER,TIME
abcd ,2020-06-01 08:58:51
abcd ,2020-06-01 08:58:51
abcd ,2020-06-01 08:58:51

abcd ,2020-06-01 09:32:27
abcd ,2020-06-01 09:32:27
abcd ,2020-06-01 09:32:27"
| multikv forceheader=1
| rename COMMENT as "your result"
| table USER,TIME
| stats values(USER) as USER by TIME
| table USER,TIME

This is OK.

0 Karma

wmyersas
Builder

stats is your friend here

Try something like this:

index="uam" User="abcd" 
| eval Timeime=strftime(_time, "%Y-%d-%m %H:%M:%S")
| stats count by User Time
| fields - count
0 Karma

siddharth1479
Path Finder

Hey @wmyersas ,
Thanks for the help.

As I said, Splunk compares the time difference by milliseconds and not by seconds, its still shows the same result which it showed before.

Thanks,
Sid

0 Karma

wmyersas
Builder

If you've already formatted the time into a new format, then you don't need to worry about the milliseconds 🙂

0 Karma

siddharth1479
Path Finder

Hi, sorry for late reply. I tried this but it isn't helping. I still get duplicate "Seconds".

0 Karma

wmyersas
Builder

Then change your time format to something less granular 🙂

0 Karma

sheamus69
Communicator

Looks like a typo there:

 index="uam" User="abcd" 
 | eval access_time=strftime(_time, "%Y-%d-%m %H:%M:%S")
 | rename access_time as TIME
 | stats count by USER TIME
 | fields - count

Should sort it

0 Karma

wmyersas
Builder

I was going off OP - but you're right, he was missing a rename in there 🙂

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...