Search query: Unique values based on time

[siddharth1479]

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

[Vijeta]

@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

[siddharth1479]

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

[willemjongeneel · Accepted solution]

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

[siddharth1479]

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.

[heissenberg]

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

[siddharth1479]

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

Thanks for the help.

[to4kawa]

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.

[siddharth1479]

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

[to4kawa]

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

[to4kawa]

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

[wmyersas]

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

[siddharth1479]

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

[wmyersas]

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

[siddharth1479]

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

[wmyersas]

Then change your time format to something less granular 🙂

[sheamus69]

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

[wmyersas]

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