Salesforce data analysis leveraging time-based Splunk lookups

 As a Sales Engineering manager I recently wanted to perform some analysis of customer meeting data from Salesforce - specifically when one of the Architects on the team brought in an overlay. My default tool of analysis is Splunk (he says shocking no one) and while I could write a very long case statement or use a lookup I was left with a challenge. Time. Given a long enough time period, there were Architect to territory alignments that straight up changed OR Architects were temporarily assigned to territories to support gaps in coverage. Regular lookups don't work in this case. What to do?

Time-based lookups to the rescue! Now, in my ~12yrs of using Splunk I've never used these. The predominant use cases are for transitory associations for things like IP addresses and hosts in VPNs or DHCP. And while Splunk's documentation is good and I expected to find threads from the Splunk Community site, initial Google search results didn't yield the clean answers I had hoped to find.

In the end you need a few things

• Your data needs to have time captured in the field "_time" (read below if that isn't the case for your data)
• Create a lookup that contains a field for time - can be named whatever but as a rule, don't use spaces
• Create a lookup definition associated with your lookup where you click the "Configure time-based lookup" box, enter your lookup's time field name, and then the format of your time field
• Unless your data/use case is crazy you probably don't want or need to mess with anything in the advanced settings

I should note that as I progressed through this effort I had to slightly change the question I was wanting my lookup to answer. I started with "was the Architect in the event the assigned territory Architect?" to "Who was the assigned territory Architect at the time of the event?" This is a subtly different question but all works out in the end AND makes it easy to do some additional levels of analysis.

The Lookup File

Because of how my data looks, and how the time based analysis is done, I needed 3 fields: Architect name, Outside Sales Person's name, & Date. Date for me was the day that Architect was aligned to this particular seller. Depending on how you think about such things, taking your lookup field matching criteria into account, Splunk will grab the date/time of your event and effectively look backwards in your lookup until it finds a match. If Wuzzle was assigned to territory 1 on Jan 1 2023 I simply create a single line with Wuzzle's name, the Territory Owner, and 1/1/2024. If Shmedlee is assigned to that territory on July 1 2024 I repeat the process. Note the relative location of those two lines on the lookup doesn't matter - they do not need to be adjacent. Now if events are after Jan 1 2024, Wuzzle is the assigned Architect and if they are after July 1 2024 it is Schmedlee.

To make life easier I used a different field name in my lookup for the Architect's name than what was in my data. This allowed me to do a field comparison. If the strings were the same then 'assigned'; otherwise is an overlay. Something like this

| lookup eng_rsm_assignments RSM OUTPUT Primary

| eval Assigned_Role = if(Assigned=Primary, "Primary", "Overlay")

(not sure the colors help?)

RSM = Regional Sales Manager aka Territory owner. This field is the same in my data and lookup so I only need to enter it once vs map field names containing the territory owner (which is easy enough to do if needed)

Assigned = field in data that contained the Architect's name

Primary = field in the lookup containing who was the Architect assigned to the territory at the time of the event 

Which leads to....

Time

Since this data is from Salesforce and is ultimately a lookup file of its own it doesn't have time captured in the "_time" field. Simple enough process to create it.

| eval _time = strptime(Date, "%m/%d/%Y")

Where Date is the field in the Salesforce data which contains the date of the event.

Full Logic

So pulling together both query bits looks a little something like this

| eval _time = strptime(Date, "%m/%d/%Y")
| lookup eng_rsm_assignments RSM OUTPUT Primary
| eval Assigned_Role = if(Assigned=Primary, "Primary", "Overlay")
| fields - Primary _time

For full effect and ease of creating dashboards I'd recommend throwing all of that in a macro. That way you don't have to monkey with it for each query and if you need to make changes you edit just one location. Now if you wanted to see something like which Architect brought in which overlay you'd want to keep that Primary field. That could end up looking something like this. Note - I've inserted the scrub command which scrambles names

Or this