Splunk Search
Highlighted

How do I join these 3 searches to get all user accounts, the date each account was created, and the last login date for each user?

Motivator

Hi, I wonder whether someone may be able to help me please.

Firstly, I will admit that I don't find writing join searches easy, but I'm certainly learning as I write more.

I've put together the following search which lists all user accounts and when they were created:

index=_audit action=edit_user operation=edit OR operation=create
|rename object as user
     |eval timestamp=strptime(timestamp, "%m-%d-%Y %H:%M:%S.%3N") 
     |convert timeformat="%d/%b/%Y" ctime(timestamp)
     |stats max(timestamp) as "created" by user
 |join user [|rest /services/authentication/users splunk_server=local 
 |fields title
 |rename title as user]

The search works fine, although a little slow.

But I'm now trying to adapt this by adding the last time the user logged on to the account which I have the following search for:

search index=_audit action="login attempt"       
| stats max(timestamp) as _time by user, sourcetype  ]  
| where isnull(sourcetype)  
| fields - sourcetype

The problem I have is that I'm not sure how to add a third subsearch and the order in which this will run in conjunction with the other two.

What I'm trying to achieve is:

  • All user accounts,
  • Then against each account the date they were created, then
  • The last login date for each user.

I just wondered whether someone could possibly look at this please and offer some guidance on how I may go about achieving this.

Many thanks and kind regards

Chris

UPDATE

I have tried the search kindly provided by Chimell and that I came up with myself, and unfortunately, this inserts false dates.

I have then tried the following:

index=_audit action=edit_user operation=edit OR operation=create
|rename object as user
     |stats max(timestamp) as "created" by user
 |join user [|rest /services/authentication/users splunk_server=local 
 |fields title
 |rename title as user]
 |join user type=outer[search index=_audit action="login attempt"
 |stats max(timestamp) as "accessed" by user, sourcetype]  
 |fields - sourcetype
 |where created !=""

But the problem I have with this is that there are some records which are null in the "accessed" column, but when I've looked at the raw data, there should be a date shown in this column.

0 Karma
Highlighted

Re: How do I join these 3 searches to get all user accounts, the date each account was created, and the last login date for each user?

Motivator

Hi
Try this search code

index=_audit action=edit_user operation=edit OR operation=create
 |rename object as user
      |eval timestamp=strptime(timestamp, "%m-%d-%Y %H:%M:%S.%3N") 
      |convert timeformat="%d/%b/%Y" ctime(timestamp)
      |stats max(timestamp) as "created" by user
  |join user [|rest /services/authentication/users splunk_server=local 
  |fields title
  |rename title as user]|appendcols[ search index=_audit action="login attempt"       
 | stats max(timestamp) as last_logging by user]
Highlighted

Re: How do I join these 3 searches to get all user accounts, the date each account was created, and the last login date for each user?

Motivator

Hi @chimell, thank you for taking the time to come back to me with this, it is greatly appreciated.

I was just running the exact same solution that I had come up with and it works great.

Kind regards

Chris

0 Karma
Highlighted

Re: How do I join these 3 searches to get all user accounts, the date each account was created, and the last login date for each user?

Motivator

thank don't forget to vote

0 Karma
Highlighted

Re: How do I join these 3 searches to get all user accounts, the date each account was created, and the last login date for each user?

Motivator

just replace null value with something
for example

|fillnull value="-" accessed

0 Karma
Highlighted

Re: How do I join these 3 searches to get all user accounts, the date each account was created, and the last login date for each user?

Motivator

Hi, thank you for this.

The problem is, is that doesn't solve the problem. What about those accounts that have never been accessed and should be blank in this field?

Kind Regards

Chris

0 Karma
Highlighted

Re: How do I join these 3 searches to get all user accounts, the date each account was created, and the last login date for each user?

Motivator

Hi, I just wanted to provide an update to this.

When I looked at the results more closely, I realised that they are incorrect because it is adding a date to the "accessed" column, even though I know they haven't logged into the system since the account was created.

Many thanks and kind regards

Chris

0 Karma
Highlighted

Re: How do I join these 3 searches to get all user accounts, the date each account was created, and the last login date for each user?

Hi IRHM73,

Try this:

| set intersect [search index=_audit action=edit_user operation=edit OR operation=create
 |rename object as user
      |eval timestamp=strptime(timestamp, "%m-%d-%Y %H:%M:%S.%3N") 
      |convert timeformat="%d/%b/%Y" ctime(timestamp)
      |stats max(timestamp) as "created" by user
  |join user [|rest /services/authentication/users splunk_server=local 
  |fields title
  |rename title as user] ] 
  [ search index=_audit action="login attempt"       
 | stats max(timestamp) as _time by user, sourcetype  ]  
 | where isnull(sourcetype)  
 | fields - sourcetype]
0 Karma
Highlighted

Re: How do I join these 3 searches to get all user accounts, the date each account was created, and the last login date for each user?

Motivator

Hi, thank you very much for taking the time to come back to me with this.

I have tried the solution, and I fixed the extra ']' but I then received the following message:

Error in 'set' command: Three arguments are required: [] [+(union) | -(diff) | ^(intersect)] [].

Many thanks and kind regards

Chris

0 Karma
Highlighted

Re: How do I join these 3 searches to get all user accounts, the date each account was created, and the last login date for each user?

Motivator

just replace null value with something
for example
|fillnull value="-" accessed

0 Karma