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
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 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
Hi,
Thanks for the help. Dedup logic seems to be working.
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
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.
index="uam" User="abcd"
| eval access_time=strftime(_time, "%Y-%d-%m %H:%M:%S")
| fields "USER" "TIME"
| dedup "USER","TIME"
Hi,
I think you need to rename the access_time as TIME, and yes dedup logic seems to be working.
Thanks for the help.
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.
Hi, sorry for late reply. I tried this but it isn't helping. I still get duplicate "Seconds".
@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.
| 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.
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
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
If you've already formatted the time into a new format, then you don't need to worry about the milliseconds 🙂
Hi, sorry for late reply. I tried this but it isn't helping. I still get duplicate "Seconds".
Then change your time format to something less granular 🙂
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
I was going off OP - but you're right, he was missing a rename in there 🙂