How do I combine my three searches using multiple joins or some other efficient method to get expected results?

[IRHM73]

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

I'm trying to perform the following:

1. For every user account set up,
2. Check to see whether they have logged on in the last 12 months,
3. In addition add the date on each user row when the account was created/amended.

I have set the first search which searches for all user accounts:

|rest /services/authentication/users splunk_server=local 
|fields title
|rename title as user

I have then set the second search which highlights the accounts where the user hasn't logged on within the last 12 months:

index=_audit action="login attempt" earliest=-12mon
    |eval timestamp=strptime(timestamp, "%m-%d-%Y %H:%M:%S.%3N") 
    |convert timeformat="%d/%b/%Y" ctime(timestamp)
    |stats max(timestamp) as "Last Date Account Accessed" by user

I have then set up my third search which extracts when the users account was created or amended.

index=_audit action=edit_user operation=edit earliest=0
    |rename object as user
    |eval timestamp=strptime(timestamp, "%m-%d-%Y %H:%M:%S.%3N") 
    |convert timeformat="%d/%b/%Y" ctime(timestamp)
    |stats min(timestamp) as "Date Account Created" by user

I have then joined them together:

|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 _time by user, sourcetype
 ]
 | where isnull(sourcetype)
 | fields - sourcetype
|join user type=outer [
    search index=_audit action=edit_user operation=edit earliest=0
    |rename object as user
    |eval timestamp=strptime(timestamp, "%m-%d-%Y %H:%M:%S.%3N") 
    |convert timeformat="%d/%b/%Y" ctime(timestamp)
    |stats min(timestamp) as "created" by user
]

NB. I have set the earliest time to zero to capture this information from the beginning of time.

The problem I have is that it doesn't insert a date of account created against all the names, only some, and this seems to be because it is using the 'timepicker' date in the search panel rather than the hardcoded date.

I'm not sure whether my logic is correct, so I'd like if at all possible please for someone with a 'fresh pair of eyes' to look at this please and check my logic. If possible, please suggest perhaps a more efficient way of writing this, if indeed there is one.

Many thanks and kind regards

Chris

[javiergn · Accepted solution]

Your operation type in your last join should be "create" instead of "edit" or * if you want any type of attempt:

| 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 _time by user, sourcetype
]
| where isnull(sourcetype)
| fields - sourcetype
| join user type=outer [
     search index=_audit action=edit_user operation=create earliest=0
     | rename object as user
     | eval timestamp=strptime(timestamp, "%m-%d-%Y %H:%M:%S.%3N") 
     | convert timeformat="%d/%b/%Y" ctime(timestamp)
     | stats min(timestamp) as "created" by user
]

[laduran]

Thanks for the reply. But...
I gotta say, the syntax for doing multiple joins is very convoluted and not intuitive compared with SQL.

[IRHM73]

Hi @javiergn, thank you once more for coming back to me with this and for the guidance, once more 🙂

The change with my existing code works spot on.

Many thanks and kind regards

Chris

[jplumsdaine22]

First have a look at this flowchart http://docs.splunk.com/Documentation/Splunk/6.3.3/Search/Abouteventcorrelation
You don't actually need a join at all (which is good as join has limitations around how many results can be returned as well as being slow)

You can simply use booleans to obtain the whole dataset in one search, then use eval and some stats

 index=_audit earliest=-12mon [|rest /services/authentication/users splunk_server=local |fields title |rename title as user] AND ((action=edit_user operation=edit ) OR (action="login attempt" )) 
 | eval creationtime=case(action=="edit_user",_time) 
 | eval logintime=case(action=="login attempt",_time) 
 | convert timeformat="%d/%b/%Y" ctime(creationtime) 
 |convert timeformat="%d/%b/%Y" ctime(logintime) 
 |stats earliest(creationtime) AS "Date Account Created" latest(logintime) AS "Last Date Account Accessed"  by user

Note I am not sure if action=edit_user operation=edit is accurate - I use LDAP myself so I can't say either way

[IRHM73]

Hi, thank you very much for coming back to me with this.

I have tried the query you kindly provided and unfortunately this doesn't work. Instead of extracting the accounts which have been inactive in last 12 months. It is extracting all the user accounts.