topic Re: How to group and count first and last timestamp in Splunk Search

How to group and count first and last timestamp

glm_cybaze — Fri, 09 Oct 2020 12:04:02 GMT

Hi to everyone,

I have some trouble on setting a correct output for a search query.

This is the start situation of the logs:

I've created a regex for a cleaner situation:

host="xxxxx" | rex "time\":\"(?<time>[^\"]+)" | rex "fullname\":\"(?<fullname>[^\"]+)" | rex "confname\":\"(?<confname>[^\"]+)" | table time, fullname, confname

So now i have this situation:

It's clear but i need a situation where i can see the first and last time a user login (the system logs timestamp for users as long as the user is logged)

something like: Time start | Time Stop | full name | confname
Someone has a some suggestions?

p.s.
For helping others people in my situation, this is the logs of Big Blue Button software

Re: How to group and count first and last timestamp

ITWhisperer — Fri, 09 Oct 2020 13:41:21 GMT

| stats earliest(_time) as "Time Start" latest(_time) as "Time Stop" by fullname, confname

Re: How to group and count first and last timestamp

glm_cybaze — Sun, 11 Oct 2020 16:55:18 GMT

Hi tanks,

I replaced the string

| table time_first, time_last, fullname, confname

With

| stats earliest(_time) as "Time Start" latest(_time) as "Time Stop" by fullname, confname

Result is:

I think because the timestamp is: 2020-10-10T12:14:06.969Z
any suggestion?

Re: How to group and count first and last timestamp

ITWhisperer — Sun, 11 Oct 2020 17:32:26 GMT

Is it that you want the timestamps displayed as date time?

| fieldformat 'Time Start'=strftime('Time Start',"%Y-%m-%dT%H:%M:%S.%Q") | fieldformat 'Time Stop'=strftime('Time Stop',"%Y-%m-%dT%H:%M:%S.%Q")

Re: How to group and count first and last timestamp

glm_cybaze — Sun, 11 Oct 2020 19:57:46 GMT

Yes, it works fine! A summary for others:

host="xxxxxxx" | rex "time\":\"(?<time>[^\"]+)" | rex "fullname\":\"(?<fullname>[^\"]+)" | rex "confname\":\"(?<confname>[^\"]+)" | stats earliest(_time) as "Time Start" latest(_time) as "Time Stop" by fullname, confname | fieldformat "Time Start"=strftime('Time Start',"%Y-%m-%dT%H:%M:%S.%Q") | fieldformat "Time Stop"=strftime('Time Stop',"%Y-%m-%dT%H:%M:%S.%Q")