Splunk Search

Sum of Unique Values in One Field of a Stats Table

SplunkLunk
Path Finder

Greetings,

I'm creating a stats table which shows Logon attempts to different workstations. I have a column that shows the distinct workstations involved (even though they may logon to a machine more than once during the day). Now I want to add a column that adds up the Unique workstations so the app owners can see who jumps from workstation to workstation the most. My initial search is below and I want to add a column in the output called "# of Unique Workstatons". I tried adding another, separate stats dc() statement but that didn't get me anywhere. Any help would be appreciated.

index=[my index] sourcetype=[sourcetype] source=[source] Action=0 OR Action=1
|bin _time as Day span=1d
|strcat LastName ", " FirstName User
|eval Action=if(Action=1,"Success","Failed")
|stats count(Terminal) as "Logon Attempts",values(User) as User,values(Terminal) as Terminal, values(Action) as Action by Logon Day
|where mvcount(Terminal)>5
|rename Terminal as "Unique Workstations" _time as Time
|convert timeformat="%a %b %d, %Y" ctime(Day) As Day
|Table Logon, User, Day "Logon Attempts", "Unique Workstations", Action
|sort Logon, -Day

0 Karma
1 Solution

tmarlette
Motivator

So in this search, are you trying to do a | dc(Terminal) by user? or something? I'm not quite clear on what you are counting here

you can always use |streamstats which you can use inline with |stats.

View solution in original post

0 Karma

tmarlette
Motivator

So in this search, are you trying to do a | dc(Terminal) by user? or something? I'm not quite clear on what you are counting here

you can always use |streamstats which you can use inline with |stats.

0 Karma

SplunkLunk
Path Finder

Yeah, I'm having a hard time explaining it. It's not real clear. Basically the above search produces a table with Logon, User, Day, Logon Attempts, Unique Workstations, and Action. The Logon Attempts are the total number of logon attempts (success or failure) for a particular user during one day (provided it's five or more). The Unique Workstations column is the distinct workstations used by a user to try and logon to an application we're looking at. For example, the first row shows user "X" had 9 logon attempts over 6 different workstations on Monday.

What I want to do is add a column which is the value of the unique workstations. This way the app owners will get a CSV file and instead of counting the workstations by hand, they can just look and see that it was six different workstations. If they wanted to they could sort the file by the logon attempts that involved the most, unique workstations and see why that user jumped around to so many workstations during the day. Right now they can't do that without visually inspecting the Unique Workstations column and saying "Hmmmm . . . that looks like a lot."

0 Karma

tmarlette
Motivator

Just so i'm understanding, let me repeat what I'm understanding.

You want a distinct count of terminals, by user INSTEAD of the values of the Terminal?

IF that's the case, try this:

|stats count(Terminal) as "Logon Attempts",values(User) as User,dc(Terminal) as Terminal, values(Action) as Action by Logon Day

IF you want an ADDITIONAL field with this data, try this:

|stats count(Terminal) as "Logon Attempts",values(User) as User,values(Terminal) as Terminal, values(Action) as Action by Logon Day
|streamstats dc(Terminal) as totLogons by User 

then add the totLogons field to your table.

0 Karma

SplunkLunk
Path Finder

Thanks for your help. I greatly appreciate it. So, your comment helped me get closer. I want the ADDITIONAL field (2nd option). Adding that statement gives me the values, but it causes a new wrinkle. I'm running the report for the "previous week" but using the bin command to separate each day. The problem is the same user might show on the report for multiple days.

As a result, the most current event for those users will show the total amount of unique workstations for the whole week, not just for that day. So if the user hit the report on Monday, Tuesday, and Wed. the value for Monday will be correct. The value for Tuesday will be the number of unique machines over Monday and Tuesday. The value for Wed. will be the unique values for all three days.

Is there a way to create the values just for the day? So the user will have a separate line item for each day they hit the report and the sum of the Unique workstations for each line item, not the total over several days?

0 Karma

SplunkLunk
Path Finder

Never mind. I think I got it. I just added Day to the end of the streamstats argument like I did to the original stats argument and that appears to have done the trick. So I added the argument:

|streamstats dc("Unique Workstations") as TotLogons by Logon Day

below the "rename" argument in the search I listed and that appears to be what I'm looking for. Thanks for the help on the streamstats command.

0 Karma

tmarlette
Motivator

Worst case scenario, add the default field date_day, then remove the field at the end of your search with |fields - date_day

https://answers.splunk.com/answers/564747/if-then-and-in-search.html

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...