![]() Right now they can't do that without visually inspecting the Unique Workstations column and saying "Hmmmm. 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. 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. What I want to do is add a column which is the value of the unique workstations. For example, the first row shows user "X" had 9 logon attempts over 6 different workstations on Monday. The Unique Workstations column is the distinct workstations used by a user to try and logon to an application we're looking at. The case statements will always result in 1 or null, so the results can only be something like 1,1,null,1,null. 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). SELECT COUNT( DISTINCT CASE WHEN status true THEN 1 END ) AS trues, COUNT( DISTINCT CASE WHEN status false THEN 1 END ) AS false FROM table This will always be 1 or 0. Basically the above search produces a table with Logon, User, Day, Logon Attempts, Unique Workstations, and Action. Yeah, I'm having a hard time explaining it. |Table Logon, User, Day "Logon Attempts", "Unique Workstations", Action |convert timeformat="%a %b %d, %Y" ctime(Day) As Day |rename Terminal as "Unique Workstations" _time as Time |stats count(Terminal) as "Logon Attempts",values(User) as User,values(Terminal) as Terminal, values(Action) as Action by Logon Day |eval Action=if(Action=1,"Success","Failed") Index= sourcetype= source= Action=0 OR Action=1 I tried adding another, separate stats dc() statement but that didn't get me anywhere. My initial search is below and I want to add a column in the output called "# of Unique Workstatons". ![]() ![]() 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. ![]() I have a column that shows the distinct workstations involved (even though they may logon to a machine more than once during the day). I'm creating a stats table which shows Logon attempts to different workstations. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |