I've been having a hard time trying to get a Splunk search that will give me a count of all records in my Lead object in Salesforce where OwnerId = Id of the queue I'm using to manage intake and created date = Today, but every time I search our index I'm getting way more records than I should (last check was 513 in Splunk and 413 in SFDC Production).
My query is below. Just to explain the current state of the query and what I've been trying, I've created a CleanNow that's just today's date in Year-Month-Day and a CleanCreatedDate converting the Salesforce CreatedDate to the same Year-Month-Day, and my last attempt to limit the search scope was a subselect to find Ids for records where the owner is not my queue and dump them. The added date columns on the table are just to "idiot check" and try to find why I'm getting a delta.
index=sfdc sourcetype=sfdc:lead OwnerId="[Id of my queue]" | eval CleanNow=strftime(now(), "%Y-%m-%d") | eval CleanCreatedDate=strftime(strptime(CreatedDate,"%Y-%m-%d"),"%Y-%m-%d") | where CleanCreatedDate=CleanNow | search NOT [search sourcetype=sfdc:lead OwnerId!="[Id of my queue]" | fields Id] | table Id, Status, OwnerId, CleanNow, CleanCreatedDate, CreatedDate, LastModifiedDate
Looking at what the Splunk query gives me and searching Prod with those Ids, I can see Splunk is returning things to me as New / owned by my queue that in Prod are actually Converted / assigned to a human. And not even ones that were just now converted, I mean ones that were converted hours ago.
I took one of those Ids returned by Splunk as New / owned by the queue but Prod said was converted hours ago, and did a search
index=sfdc sourcetype=sfdc:lead Id="[Id of the delta record]"
And I get three events. 1. Status is New and is owned by my queue (this is what I actually want to see in the return) 2. Status is New and it's owned by a human being (do not want to see) 3. Status is Converted and it's owned by a human being (do not want to see)
That Id only appears once in the results from Splunk, and the fact that we had two other entries that don't match what I consider a "hit" means I should not have seen it at all, including the one time it logged as New / owned by the queue. Not entirely sure I'm explaining it right, but basically I need to find a way to recursively search the results to find any Ids that match the original query, but are in there a second (or third, or fourth...) time with a different owner, and drop them from the return.