Skip to main content

Hi eveyone, 

I want to create a search to give me the time of the first login, why the query bellows give-me an error ?

 

 

 

principal.user.userid = "xpto-bind" OR target.user.userid = "xpto-bind"

match:
principal.user.userid, metadata.event_timestamp
order:
metadata.event_timestamp desc
limit:
1

 

 

 

thanks

There are few different ways to do this but let's look at a few things we can do to.


The first thing I would do is if I want limit to logins is to add the field metadata.event_type because in the current search you will get process launches, file creations, anything that has a userid with that value.


Second, you ask for either principal or target userid but then you are only matching on principal. It would be wise to take a look at the user login events you have to determine where you are more likely to see that user name, in the example below I decided to put greater preference to target v principal and used the coalesce function that will return the first one seen as the match variable. Alternatively you could group by both individually or you could concatenate them together.


The metadata.event_timestamp field can be represented in seconds or nanos, so the full field would need to be in the match section using the method you are following. The problem there is that nice date format goes away when performing an aggregation and you end up with epoch time. Using the timestamp.get_timestamp variable and then aggregating on it will make this format in a more readable manner.


We can use that value in the order section but when we do, we will want to go ascending order since the time we want is the first in the window and therefore would be the lowest value.


Here is what my search looks like. Again, this is one way to do this and there are other methods but this looks like what you are trying to achieve...



metadata.event_type = "USER_LOGIN"
principal.user.userid = /tim.smith/ OR target.user.userid = /tim.smith/
$userid = strings.coalesce(target.user.userid, principal.user.userid)
$time = timestamp.get_timestamp(metadata.event_timestamp.seconds)
match:
$userid, $time
order:
$time asc
limit:
1


Entity graph - user - has the First time seen field, maybe that's the info you can direct look for? 



just search like below:
user
= "tim.smith_admin"

and check the Entity summary 


Reply