Splunk Search

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
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
1 Solution

IRHM73
Motivator

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

View solution in original post

0 Karma

IRHM73
Motivator

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

0 Karma

javiergn
Super Champion

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.

0 Karma

javiergn
Super Champion

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.

0 Karma

IRHM73
Motivator

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

0 Karma

javiergn
Super Champion

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

0 Karma

IRHM73
Motivator

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

0 Karma

chimell
Motivator

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

0 Karma

ngatchasandra
Builder

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

IRHM73
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

chimell
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]

IRHM73
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

chimell
Motivator

thank don't forget to vote

0 Karma

chimell
Motivator

just replace null value with something
for example

|fillnull value="-" accessed

0 Karma

IRHM73
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

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

Index This | Divide 100 by half. What do you get?

November 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Stay Connected: Your Guide to December Tech Talks, Office Hours, and Webinars!

❄️ Celebrate the season with our December lineup of Community Office Hours, Tech Talks, and Webinars! ...

Splunk and Fraud

Watch Now!Watch an insightful webinar where we delve into the innovative approaches to solving fraud using the ...