topic Re: Search query: Unique values based on time in Splunk Search

Search query: Unique values based on time

siddharth1479 — Wed, 30 Sep 2020 03:36:09 GMT

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

Re: Search query: Unique values based on time

wmyersas — Tue, 07 Jan 2020 19:47:22 GMT

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

Re: Search query: Unique values based on time

siddharth1479 — Tue, 07 Jan 2020 21:12:01 GMT

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

Re: Search query: Unique values based on time

to4kawa — Tue, 07 Jan 2020 21:42:33 GMT

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.

Re: Search query: Unique values based on time

sheamus69 — Wed, 08 Jan 2020 16:10:35 GMT

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

Re: Search query: Unique values based on time

wmyersas — Thu, 09 Jan 2020 15:54:42 GMT

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

Re: Search query: Unique values based on time

wmyersas — Thu, 09 Jan 2020 15:55:15 GMT

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

Re: Search query: Unique values based on time

siddharth1479 — Mon, 13 Jan 2020 15:43:20 GMT

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

Re: Search query: Unique values based on time

siddharth1479 — Mon, 13 Jan 2020 15:43:41 GMT

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

Re: Search query: Unique values based on time

heissenberg — Wed, 30 Sep 2020 03:39:01 GMT

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

Re: Search query: Unique values based on time

willemjongeneel — Wed, 30 Sep 2020 03:42:59 GMT

Hello,

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

Something like:

Kind regards,
Willem Jongeneel

Re: Search query: Unique values based on time

to4kawa — Mon, 13 Jan 2020 16:50:12 GMT

@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.

Re: Search query: Unique values based on time

to4kawa — Mon, 13 Jan 2020 16:58:12 GMT

| 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.

Re: Search query: Unique values based on time

Vijeta — Mon, 13 Jan 2020 17:18:43 GMT

@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

Re: Search query: Unique values based on time

wmyersas — Mon, 13 Jan 2020 22:13:25 GMT

Then change your time format to something less granular 🙂

Re: Search query: Unique values based on time

siddharth1479 — Tue, 14 Jan 2020 16:55:42 GMT

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.

Re: Search query: Unique values based on time

siddharth1479 — Tue, 14 Jan 2020 16:57:26 GMT

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

Thanks for the help.

Re: Search query: Unique values based on time

siddharth1479 — Tue, 14 Jan 2020 16:58:34 GMT

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