I wrote a query that joins two sourcetypes from my Salesforce instance, counts a total number of events from midnight today to now, midnight yesterday to "the now time" yesterday, same for one week ago, and one month ago, and then aggregates the counts by a breakdown of regions & locations. Query works great and gives me a table that's easy to read, but the stakeholder came back asking if I can add "delta columns" to the output, giving the difference between today & yesterday, today & 1wk ago, today & 1mo ago in their own columns. I've got this written with a stats command at the end, and can't figure out how to incorporate those differences since each column in the stats is a sum of values I'm flagging in the search. I'm not sure if there's a way to add it in the stats command, or if I should scrap the stats command and visualize this in another way (pivot table?). The time juggling is b/c Salesforce gives wonky time stamps. Join is b/c LoginEvent doesn't contain the fields I need to aggregate, which are on the User object (custom fields). index=sfdc sourcetype=sfdc:LoginEvent earliest=-32d Status=Success | rename UserId as Id | join Id [search sourcetype=sfdc:User] | eval EventDateEpoch=strptime(EventDate,"%Y-%m-%dT%H:%M") | eval TodayMidnightEpoch=relative_time(now(), "@d+0") | eval TodayMidnightM1dEpoch=relative_time(now(),"-d@d") | eval TodayMidnightM7dEpoch=relative_time(now(),"-7d@d") | eval TodayMidnightMMoEpoch=relative_time(now(),"-1mon@d") | eval NowM1dEpoch=relative_time(now(), "-1d") | eval NowM7dEpoch=relative_time(now(),"-7d") | eval NowMMoEpoch=relative_time(now(),"-1mon") | eval EventToday=if((EventDateEpoch>=TodayMidnightEpoch) and (EventDateEpoch<=now()), "1" , "") | eval EventYesterday=if((EventDateEpoch>=TodayMidnightM1dEpoch) and (EventDateEpoch<=NowM1dEpoch), "1", "") | eval EventLastWeek=if((EventDateEpoch>=TodayMidnightM7dEpoch) and (EventDateEpoch<=NowM7dEpoch), "1", "") | eval EventLastMonth=if((EventDateEpoch>=TodayMidnightMMoEpoch) and (EventDateEpoch<=NowMMoEpoch), "1", "") | stats sum(EventToday) AS Today sum(EventYesterday) as Yesterday sum(EventLastWeek) as "Today -7d" sum(eventLastMonth) as "Today -1Mo" by State, Location
... View more