Dashboards - avg() and count() compilation error

What I think you are looking for is what we call multi-stage queries where you are getting some sort of calculation in one stage of the search and then taking that calculated value and creating another calculated values from that.

The part I am a little unclear about is that second calculation. The query as written groups the data by day or the week and log source (for the users in the search). What is the average supposed to be? Is that the average of all the logs for that day? or by day and by log source for all of the users? I’m wondering if the user should be in the match section of the search if you are looking for a count per user per day of week per log source. There is a bit of ambiguity in the question I am trying to flesh out a bit.

That all said, I do want to show you how multi-stage could work and provide a template you could leverage.

I pulled the comments and user out of this. In this example, you calculate the count grouped by day of week and log source. For a multi-stage, these 3 fields, the match and outcome variables are available for future stages. In this example, I am just calculating an average of the event count from stage a (notice the stage and the bracket around the stage). In the outcome section that is in the root stage (the one without the brackets), we have an outcome variable that is calculating the average of the value from the outcome variable in the first stage. To make sure we use that value we use the variable that denotes that stage $a followed by the outcome variable. The result of this is a single value which is the average across the entire population set of the first query you wrote.

stage a {
$day_of_month = timestamp.get_date(udm.metadata.event_timestamp.seconds)
$day_of_week_num = timestamp.get_day_of_week(udm.metadata.event_timestamp.seconds)
$day_of_week_num >= 2 and $day_of_week_num <= 6
udm.metadata.log_type = "AZURE_AD" or udm.metadata.log_type = "UMBRELLA_DNS" or udm.metadata.log_type = "SLACK_AUDIT" or udm.metadata.log_type = "SALESFORCE" or udm.metadata.log_type = "WORKSPACE_ACTIVITY" or udm.metadata.log_type = "CS_EDR" or udm.metadata.log_type = "AWS_CLOUDTRAIL"
$log_source = udm.metadata.log_type
match:
 $day_of_week_num, $log_source
outcome:
 $event_count = count(udm.metadata.id)
order:
 $day_of_week_num asc
}
outcome:
 $avg_of_event_count = avg($a.event_count)

Now that may not be what you had in mind because you want the average broken out by day perhaps. Same thing for the stage a, but now in the root, we are going to group this average calculation by just the day, meaning we have event counts for each log source for a day and we want the average value across those log sources. So now in the filtering section we are defining a match variable by defining new_stage_day_of_week_num and associating that with that match variable in stage a. We could use the same name for the variable in the root stage but i didn’t want to confuse things by doing that. With that being the aggregation, in the outcome section, I am calculating that same average that I did above but then also pulling in an array of the values of the log sources for each of those days, mainly so you can see them and how we arrived at the result.

stage a {
$day_of_month = timestamp.get_date(udm.metadata.event_timestamp.seconds)
$day_of_week_num = timestamp.get_day_of_week(udm.metadata.event_timestamp.seconds)
$day_of_week_num >= 2 and $day_of_week_num <= 6
udm.metadata.log_type = "AZURE_AD" or udm.metadata.log_type = "UMBRELLA_DNS" or udm.metadata.log_type = "SLACK_AUDIT" or udm.metadata.log_type = "SALESFORCE" or udm.metadata.log_type = "WORKSPACE_ACTIVITY" or udm.metadata.log_type = "CS_EDR" or udm.metadata.log_type = "AWS_CLOUDTRAIL"
$log_source = udm.metadata.log_type
match:
 $day_of_week_num, $log_source
outcome:
 $event_count = count(udm.metadata.id)
order:
 $day_of_week_num asc
}
$new_stage_day_of_week_num = $a.day_of_week_num
match:
 $new_stage_day_of_week_num
outcome:
 $first_stage_event_count = array($a.event_count)
 $avg_of_event_count = avg($a.event_count)

Depending on how you want to slice the data as it pertains to the users, these examples are probably not your final outcome but hopefully this gets you going in the right direction. I hope to get to writing some blogs on multi-stage join because it is a nice addition to the library but takes a little getting used to in the meantime.

I’ve narrowed down my search to just do it by individual log sources instead of all of them. I’ve used the first query you sent, and I’m getting a warning message “to sort search results, include order in your query”. I assume because this is included in stage a it won’t be an issue. But when I run the query I’m getting this error message: 

I updated the time period to be shorter and used the second query, but it looks like it’s still not averaging. If the time period is 2 weeks it’ll give me around 40000 events and if the time period is a month it gives me around 80000 events. 

Let’s revisit the user bit. I think I demonstrated with both queries that i can generate an average based on the results from a search and i do think your idea about shrinking the initial data set you are working with is a good idea to make it manageable until you get a stat you feel good about, but I feel like you are calculating one thing in the first stage and then you want to calculate and average of a slightly different thing in the second pass. 

The first stage is calculating an event count by day and log source. Should this be by user and day and log source? If so, is the average then just by user?

I think I’m getting rid of the specific user bit and just doing the average of all users per day for now. We’re trying to understand working patterns, and we’ll likely try narrow this down at a later stage but for now just want it for all users. 

OK so I removed users from both searches. The second search then generates the count per day and log source and the root stage then rolls up the average events per day and the while displaying the array of the values that generated that average. Is that what you need?

I think so, I just want a bar graph that shows the average events per day (grouped by log source if possible - but could also just do separate queries for each log source)

If you keep the stage you could create a daily average of events across the specified log sources like this.

Or you could do the initial search without the stage and get a daily count by event type, the group type provides the ability to change the way it is displayed.

I still feel like you are looking for something else. Are you looking for the daily average to be overlayed or something else? 

What’s your updated query?

The first image is from the second example I showed, i just didn’t include a group by in the bar chart.

The second image is based on this search where there isn’t the second pass across the data.

$day_of_month = timestamp.get_date(udm.metadata.event_timestamp.seconds)
$day_of_week_num = timestamp.get_day_of_week(udm.metadata.event_timestamp.seconds)
$day_of_week_num >= 2 and $day_of_week_num <= 6
udm.metadata.log_type = "AZURE_AD" or udm.metadata.log_type = "UMBRELLA_DNS" or udm.metadata.log_type = "SLACK_AUDIT" or udm.metadata.log_type = "SALESFORCE" or udm.metadata.log_type = "WORKSPACE_ACTIVITY" or udm.metadata.log_type = "CS_EDR" or udm.metadata.log_type = "AWS_CLOUDTRAIL"
$log_source = udm.metadata.log_type
match:
 $day_of_week_num, $log_source
outcome:
 $event_count = count(udm.metadata.id)
order:
 $day_of_week_num asc

When using the second query, doesn’t look to be averaging, when I increase the time frame, the avg_of_event_count increases. 

Let me attempt to roughly mirror your time window. How big is the search window you are using?

3 months

OK, multi-stage search (public preview) is limited to one month but this is what my data looks like. I am replaying the same data in and i have 4 of your log types which is why my data that i am calculating the average from only has 4 values in the array. i would expect you would have up to 7 values in that array.