Splunk Search
Highlighted

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 accesstime=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
Highlighted

Re: Search query: Unique values based on time

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
Highlighted

Re: Search query: Unique values based on time

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
Highlighted

Re: Search query: Unique values based on time

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
Highlighted

Re: Search query: Unique values based on time

Builder

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

0 Karma
Highlighted

Re: Search query: Unique values based on time

Builder

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

0 Karma
Highlighted

Re: Search query: Unique values based on time

Path Finder

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

0 Karma
Highlighted

Re: Search query: Unique values based on time

Builder

Then change your time format to something less granular 🙂

0 Karma
Highlighted

Re: Search query: Unique values based on time

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
Highlighted

Re: Search query: Unique values based on time

Path Finder

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

0 Karma