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
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
.
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
.
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."
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.
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?
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.
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