I found that the ‘SavedSearches.conf’ file is using 15 digit id and splunk search being case insensitive so it links multiple users to same eventlog.
search=index=* eventtype=sfdc_user IsActive=true | sort 0 - _time | dedup Id | rename Id as UserId | fields + UserId,FirstName,City,Country,IsActive,LastLoginDate,LastName,MobilePhone,Name,PostalCode,State,Username,UserRoleId,UserType,Email,CompanyName,ProfileId,Profile.PermissionsApiEnabled,Profile.PermissionsModifyAllData,Profile.PermissionsViewSetup | fields - _* | eval USER_ID=substr(UserId, 1, len(UserId)-3) | outputlookup lookup_sfdc_usernames.csv
I want to know why Salesforce App in splunk uses 15 digit id. If we use 18 digit id then it will solve the issue.
Hmmm. Splunk is only case insensitive in a couple of contexts, like a root search. dedup
is not one of them. lookup
is not one of them. So, I have no idea why case sensitivity is part of this question.
On the other hand, clearly the search is stripping off the last three digits of the UserId field, no matter what its initial length is, to create the USER_ID field. If the last three digits are part of what makes the UserId field unique, then the outputlookup is going to have duplicate records in it. The USER_ID field will have the first 15 digits, and UserId will have the full 18 digits for each record. If you need to connect something to the 18-digit user id, then use UserId.
Run this to see which records are duplicated and which events have a different UserId length .
| inputlookup lookup_sfdc_usernames.csv
| eventstats count as dupcount by USER_ID
| eval mylen=len(USER_ID)
| where dupcount>1 OR mylen!=15
Ah, according to this answer, you can set any lookup to be case insensitive.
https://answers.splunk.com/answers/514983/how-to-ignore-case-sensitive-input-in-lookup-files.html
You can run this to see if there are any collisions in your file based on differences in case.
| inputlookup lookup_sfdc_usernames.csv
| eval USER_ID2 = upper(USER_ID)
| eval UserId2 = upper(UserId)
| eventstats dc(USER_ID) as dcUSER by USER_ID2
| eventstats dc(UserId) as dcUser by UserId
| where dcUSER>1 OR dcUser>1
Hi DalJeanis,
Thanks for your email. When I run the queries you mentioned then I do not get any records.
I am trying to run below query -
Here URI is basically who have accessed a particular object. But I get a duplicate record for a User who don't even have access to that object. I am not sure what is wrong with my query.
source="sfdc_event_log://EventLog" URI = /a2m USER_ID="MentionUserId"| top limit=10 Name
Name↕ count↕ percent↕
Va*** Ba**** 83 100.000000
Lu** Li** 83 100.000000