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?

[IRHM73]

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.

[IRHM73 · Accepted solution]

Hi all, especially @javiergn, who helped a great deal, I just wanted to post the two solutions that I came up with.

The main purpose of my query was to highlight those users who hadn't accessed their account in the last 12 months, but I needed to also show when the accounts were created, so I didn't delete accounts that had only been created in the last few weeks. @javeirgn actually helped me here to work out inactive accounts, so I adapted this as follows, so this only displays those users who haven't accessed their account over the last 12 months.

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=left [
    | search index=_audit action="login attempt" earliest=-12mon
    | stats max(timestamp) as "accessed" by user, sourcetype
 ]
 | where isnull(sourcetype)
 | fields - sourcetype accessed

My second solution includes both the date account was created then the last date the user logged on.

I would then use a 'date diff' function to highlight the number of days.

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=left [
    | search index=_audit action="login attempt" earliest=-12mon
    | stats max(timestamp) as "accessed" by user, sourcetype]
 | fields - sourcetype

I hope this helps.

Regards

Chris

[javiergn]

Hi,

I think your query is almost fine.
Take a look at this approach (remove the comments before trying please):

# List all the available users
| rest /services/authentication/users splunk_server=local 
| fields title
| rename title as user
# For each available user return creation or last edition date. It should never be null so normal join will do
| join user [
    search index=_audit action=edit_user operation=create OR operation=edit
    | rename object as user
    | stats max(timestamp) as created by user
]
# For each available user with a creation date return the last time the user logged in. It can be null (empty) if the user never did that and therefore we use a left join.
| join user type=left [
    search index=_audit action="login attempt"
    | stats max(timestamp) as accessed by user, sourcetype
]  
| fields - sourcetype

In my case, after I performed the following search in my test lab I got the following:

user    accessed    created
admin   02-18-2016 04:18:26.741     02-18-2016 04:26:19.082
javier      02-17-2016 14:17:17.489 

Which is expected as the user "javier" has never logged in before.

Hope that helps.

[javiergn]

By the way, I would probably make a minor change.
In the second join, the only reason you need "operation = edit" is because your admin account is never created. It already exists from the beginning so therefore if you want to display this more accurately I would do the following:

| join user [
     search index=_audit (action=edit_user AND object=admin) OR (operation=create AND object!=admin)
     | rename object as user
     | stats min(timestamp) as created by user
 ]

Totally personal so up to you basically.

[IRHM73]

Hi @javiergn, sincere thanks for this it is greatly appreciated.

Unfortunately the query doesn't work because it doesn't return the full list of users.

I split the query down and it's something to do with this subsearch:

 | join user [
      search index=_audit (action=edit_user AND object=admin) OR (operation=create AND object!=admin)
      | rename object as user
      | stats min(timestamp) as created by user
  ]

Many thanks and kind regards

Chris

[javiergn]

Can you post (even if obfuscated) the output you get after you run this:

index=_audit (action=edit_user AND object=admin) OR (operation=create AND object!=admin)
| rename object as user
| stats min(timestamp) as created by user

And also the output of the big query I posted above?
In principle if the first query (the rest call one) is listing all your users, the second shouldn't filter anything out because every user has to be created at some point (apart from admin obviously)
The third query uses a left join so that wont' filter anything either.

By the way, are you using LDAP at all? Could some of those users not showing up be LDAP ones? In that case, maybe the operation you are looking for is not create. Just guessing, I don't have LDAP in my lab to test this.

Thanks,
J

[IRHM73]

Hi @javiergn, thank you for replying.

Firstly and perhaps the easiest to provide details with is that we don't use a LDAP facility.

When I used the smaller query you provided in your last post, the results returned were all the user names current and those deleted. It also had a list of var files, if that helps. The approx. number of users it returned was 580.

When I used the large query rather than returning the full list of users with the "accessed" and "created" dates against them, the list of users was reduced to 289 and all had the "created" date completed but on some the "accessed" date was missing.

It is almost as if it is filtering, but where I am not sure.

Putting my original query to one side, is there by any chance a completely different way of writing this perhaps using the stats list command? Just a thought.

Many thanks and kind regards

[chimell]

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

[ngatchasandra]

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]

[IRHM73]

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

[chimell]

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]

[IRHM73]

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

[chimell]

thank don't forget to vote

[chimell]

just replace null value with something
for example

|fillnull value="-" accessed

[IRHM73]

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

[IRHM73]

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