Splunk Enterprise

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

wgawhh5hbnht
Communicator

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:

UserNameDescriptionDescriptionCountActionDescriptionActionDescriptionCountCount_time
AndySSO
Send to home
update password
1
1
1
1
Sign in
Sign in successful
1
1
410/5/2021 15:00
BobAuthentication 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
810/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)")


Screenshot 2021-10-05 153204.png

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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))

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

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
Communicator

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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
Communicator

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

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...