topic Re: Join Statement Not Retrieving All Records in Splunk Search

Join Statement Not Retrieving All Records

IRHM73 — Fri, 19 Feb 2016 08:42:58 GMT

Hi, I wonder whether someone may be able to help me please for which may seem a really dumb question.

I'm using the query below to extract user accounts with a creation date which returns 430 records.

| rest /services/authentication/users splunk_server=local   
| fields title  
| rename title as user  
| join user [search index=_audit action=edit_user operation=create
         | rename object as user       
         | stats list(timestamp) as "created" by user]

The problem I have is that I should have a list of 440 which I then want to add the date against.

Could someone tell me please why I'm not able to create the full list.

I do know that some of the accounts don't have "operation=create" value in the raw data but rather "operation=edit", but either way I would have thought the full list should be created and then if the subsearch doesn't match then the date entry will be blank.

Many thanks and kind regards

Chris

Re: Join Statement Not Retrieving All Records

gyslainlatsa — Fri, 19 Feb 2016 09:09:53 GMT

the operation field has other values other than create and edit?

Re: Join Statement Not Retrieving All Records

jkat54 — Fri, 19 Feb 2016 09:14:08 GMT

Since you're just getting the username from the first search, but there's a username in the second search, there's no need to join. just drop the first search so that you're left with this:

index=_audit action=edit_user (operation=create OR operation=edit)
              | rename object as user       
              | stats list(timestamp) as "created" by user

If you really must join though, add max=0, and you might also need to look into limits.conf settings.

| rest /services/authentication/users splunk_server=local   
     | fields title  
     | rename title as user  
     | join user max=0 [search index=_audit action=edit_user operation=create OR operation=edit
              | rename object as user       
              | stats list(timestamp) as "created" by user]

Re: Join Statement Not Retrieving All Records

IRHM73 — Fri, 19 Feb 2016 09:21:55 GMT

Hi @jkat54, thank you for coming back to me with this.

As you were kindly writing your reply, I was finding a solution myself and came up with the following:

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]

I know it's not the most efficient query because of it's runtime, but it is now returning all the records.

Many thanks for your help and kind regards

Chris

Re: Join Statement Not Retrieving All Records

gyslainlatsa — Fri, 19 Feb 2016 09:22:07 GMT

try like this:

| rest /services/authentication/users splunk_server=local   
     | fields title  
     | rename title as user  
     | join user [search index=_audit action=edit_user (operation=create OR operation=edit)
              | rename object as user       
              | stats list(timestamp) as "created" by user]

Re: Join Statement Not Retrieving All Records

IRHM73 — Fri, 19 Feb 2016 09:25:57 GMT

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

I did try the query you kindly provided, but unfortunately this doesn't retrieve all the records.

Many thanks and kind regards

Chris

Re: Join Statement Not Retrieving All Records

jkat54 — Fri, 19 Feb 2016 10:24:21 GMT

I see what you did there and I think it's pretty swift. I've just got one user locally but the search completes rather quickly for me. I changed your comment to answer so that you may mark it as such.