Splunk Search

How to retrieve specific timestamps for specific values ?

malice
Observer

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 

Labels (1)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...