Splunk Search

Join Statement Not Retrieving All Records

IRHM73
Motivator

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

0 Karma
1 Solution

IRHM73
Motivator

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

View solution in original post

gyslainlatsa
Motivator

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]
0 Karma

IRHM73
Motivator

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

0 Karma

IRHM73
Motivator

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

jkat54
SplunkTrust
SplunkTrust

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.

jkat54
SplunkTrust
SplunkTrust

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]

gyslainlatsa
Motivator

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

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...