Calculating deltas between two stats columns

[arist0telis]

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

[johnhuang]

Try something  like this:

You may need to adjust the field names you want to group by.

index=sfdc ((sourcetype=sfdc:LoginEvent* AND Status=Success) OR sourcetype="sfdc:user") earliest=-1mon@mo
| fields _time Id sourcetype City State Name Email
| eventstats last(City) AS City last(State) AS State last(Email) AS Email by Id
| search sourcetype="*Login*"
| eval current_day_events=IF(_time>=relative_time(now(), "-0d@d"), 1, 0)
| eval last_day_events=IF(_time>=relative_time(now(), "-1d@d") AND _time<=relative_time(now(), "-0d@d"), 1, 0)
| eval last_week_events=IF(_time>=relative_time(now(),"-1w@d"), 1, 0)
| eval last_mon_events=IF(_time>=relative_time(now(), "-1mon@d"), 1, 0)
| table _time Id current_day_events last_day_events last_week_events last_mon_events City State Name Email
| stats sum(*_events) AS *
| table current_day_events last_day_events last_week_events last_mon_events

[gcusello]

Hi @arist0telis,

to calculate differences between two fields or execute any kind of calculation, you have to use the eval command after the stats command, something like this:

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
| eval 
   diff_Yesterday=Yesterday-Today, 
   diff_1Week="Today -7d"-Today,
   diff_1Month="Today -1Mo"-Today

Ciao.

Giuseppe

[arist0telis]

Looks like that did what I was looking for, thanks. I'm still working to optimize it b/c with my limited understanding of time parsing & joining two super chonky sourcetypes it runs slow, but yeah gives results I'm expecting. Had to change my column aliases b/c they were throwing errors in the eval.

| stats sum(EventToday) AS Today sum(EventYesterday) as Yesterday sum(EventLastWeek) as LastWeek sum(eventLastMonth) as LastMonth by State, Location
| eval DeltaYesterday=Yesterday-Today
| eval DeltaLastWeek=LastWeek-Today
| eval DeltaLastMonth=LastMonth-Today

[PickleRick]

One more thing - you should definitely work on your date parsing. Since you're using other field except _time for your timerange calculation you can't effectively limit your search range. If you had your EventDate (which I assume is the primary timestamp for your events) parsed as _time, you could limit the search timerange from the beginning, effectively accelerating your search greatly, by doing

<your search> earliest=@d OR (earliest=-1d@d latest=-1d) OR ...

 Then you could simply bin the events by _time with span of 1d and it would be easy to count different days separately.

[arist0telis]

Thanks for this, I'm still learning the most efficient way to do a lot of what I would have done in other systems, this helps. Also +1 Karma just for your username.