topic How to retrieve specific timestamps for specific values ? in Splunk Search

How to retrieve specific timestamps for specific values ?

malice — Sun, 14 Nov 2021 13:30:13 GMT

Hello everyone,

I am currently developing a use case in which I have the below info:

Username	User Status	User Code	Time of Event per User Status update
user A	0 0 1 1	xxxxx	2021-11-13 22:22:15 2021-11-13 23:40:09 2021-11-13 23:45:09 2021-11-13 23:50:09
user B	0 1	yyyyy	2021-11-13 22:40:09 2021-11-13 22:50:09
user A	0 1 1	ggggg	2021-11-13 22:50:09 2021-11-13 22:55:09 2021-11-13 22:58:09

I would like to find for each user the time difference between the timestamps of the first occurrence of status 0 and then the first occurrence of status 1.

So based on the above table, I would like to extract the timestamp of 2021-11-13 22:22:15 for User Status 0 and the timestamp 2021-11-13 23:45:09 for User Status 1 for the user A.

So my search query so far looks like it:

| my index
| sort UserStatus

| transaction mvlist=true Username UserStatus | search eventcount >1 | UserStatus =0 and UserStatus=1

Any help will be much appreciated! Thanks

Re: How to retrieve specific timestamps for specific values ?

ITWhisperer — Sun, 14 Nov 2021 13:52:45 GMT

Try something like this

| stats earliest(_time) as time by Username 'User Status' | eval time0=if('User Status'=0,time,null()) | eval time1=if('User Status'=1,time,null()) | stats values(time0) as time0 values(time1) as time1 by Username | eval diff=time1-time0

Re: How to retrieve specific timestamps for specific values ?

PickleRick — Sun, 14 Nov 2021 18:00:04 GMT

What is your original data? Because this already looks like some calculated stats table.