Splunk Search

Search query: Unique values based on time

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

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

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

Path Finder

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

0 Karma

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

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

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

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

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

Path Finder

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

0 Karma

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

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

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

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

Builder

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

0 Karma

Path Finder

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

0 Karma

Builder

Then change your time format to something less granular 🙂

0 Karma

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

Builder

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

0 Karma