count for multiple fields with values after initial count with where clause

[wgawhh5hbnht]

I'm attempting to get a count for multiple fields Description and ActionDescription with the values for them AFTER counting by another field with a where clause over a period of time. This is what I'm wanting:

UserName	Description	DescriptionCount	ActionDescription	ActionDescriptionCount	Count	_time
Andy	SSO Send to home update password	1 1 1 1	Sign in Sign in successful	1 1	4	10/5/2021 15:00
Bob	Authentication Successful Sending to SecondFactor Sent token via SMS Successfully Authorized	1 2 1 3 1	Sign in Sign in successful Sign in failed	1 1 2	8	10/5/2021 17:00

This is the closest I've got but there are times where either the DescriptionCount or ActionDescriptionCount has missed a count for the Description or the ActionDescription:

index=foo source=bar
| bin _time span=1h
| fillnull value="0"
| eventstats count by UserName _time
| where count > 500
| rename count as UserNameCount
| eventstats count by Description
| rename count as DescriptionCount
| eventstats count by ActionDescription
| rename count as ActionDescriptionCount
| stats values(ActionDescription) as ActionDescriptionValues values(ActionDescriptionCount) as ActionDescriptionCount values(Description) as Description values(DescriptionCount) as DescriptionCount values(_time) as "Time Frame(s)" count by UserName
| convert ctime("Time Frame(s)")

[ITWhisperer · Accepted solution]

To be honest, I am not completely clear what it is you are after, but does this get you any closer?

index=foo source=bar
| bin _time span=1h
| fillnull value="0"
| eventstats count by UserName _time
| where count > 500
| rename count as UserNameCount
| eventstats count as DescriptionCount by Description UserName _time
| eventstats count as ActionDescriptionCount by ActionDescription UserName _time
| eval DescriptionCount = DescriptionCount."!".Description 
| eval ActionDescriptionCount = ActionDescriptionCount."!".ActionDescription
| stats values(DescriptionCount) as DescriptionCountValues values(ActionDescriptionCount) as ActionDescriptionCountValues values(UserNameCount) as UserNameCount by UserName _time
| eval DescriptionValues = DescriptionCountValues 
| eval DescriptionValues = mvmap(DescriptionValues,mvindex(split(DescriptionValues,"!"),1))
| eval DescriptionCountValues = mvmap(DescriptionCountValues,mvindex(split(DescriptionCountValues,"!"),0))
| eval ActionDescriptionValues = ActionDescriptionCountValues 
| eval ActionDescriptionValues = mvmap(ActionDescriptionValues,mvindex(split(ActionDescriptionValues,"!"),1))
| eval ActionDescriptionCountValues = mvmap(ActionDescriptionCountValues,mvindex(split(ActionDescriptionCountValues,"!"),0))

[wgawhh5hbnht]

Thank you @ITWhisperer! This is exactly what I was attempting to do!
What does the "!" do in this eval commands?

| eval DescriptionCount = DescriptionCount."!".Description 

The docs only show it being used for !=, nothing else is coming up when I search, and when I run it by itself then I get the count before the Description with a ! between, e.g.:

4!Success

& then later it appears as though you're removing the ! with the mvindex? I'm very confused on how you got this to work

[ITWhisperer]

The issue you had with your search and what you were apparently trying to do was associate the counts with the descriptions. By creating a string with the count followed by the description separated by a character that didn't appear to be present in either, I chose "!" for this but "#" or "%" might have worked equally well, this created a field value with both in. This field could then be gathered by the stats command grouped by UserName and _time. Then, you just needed to split the two values apart into count and description. Because these fields were gathered into multivalue fields by the stats values aggregators, we had to use the mvmap function to manipulate the elements in the mv fields.

[wgawhh5hbnht]

Thank you for the explanation, that makes sense! Much appreciated!!!