Identify users with unusual activities in their role

[amarrtya]

I am starting off with Splunk and trying to solve a problem. I have a data set with millions of log records. The use case is to identify events that are unusual for a specific role and highlight the event and the user. The below table gives a snapshot of the data. The task is to append the last two columns and for each role, identify the appended events that has happened relatively lower than other appended events in the same role.

user_name  role         event_name     event_type
    A1     Provider     Open Session    Patient
    A1     Provider     Open Session    Patient
    A1     Provider     View Session    Patient
    B1     Provider     Search Session  Admin
    B1     Provider     Search Session  Admin
    B1     Provider     Search Session  Patient
    B1     Provider     Search Session  Admin
    B1     Provider     Open Session    Admin
    C1     Physician    Open Session    Patient
    C1     Physician    Modify Session  Patient
    C1     Physician    Modify Session  Patient
    C2     Physician    Open Session    Patient
    C2     Physician    Open Session    Patient
    C3     Physician    Modify Session  Admin

If I want to find unusual events for the role "Provider" the output should be

user_name    role        appended_event     
    A1     Provider     View Session Patient    
    B1     Provider     Search Session Patient
    B1     Provider     Open Session Admin

Similarly, if I want to find the unusual events for the role "Physician" the output should be

 user_name    role        appended_event     
        C3     Physician        Modify Session Admin   

I am also looking for a way to visualize such a report. Any help on this would be great.

[mayurr98]

Hey you can try this run anywhere search

| makeresults 
| eval user_name="A1",role="Provider",event_name="Open Session",event_type="Patient" 
| append 
    [| makeresults 
    | eval user_name="A1",role="Provider",event_name="Open Session",event_type="Patient" ] 
| append 
    [| makeresults 
    | eval user_name="A1",role="Provider",event_name="View Session",event_type="Patient" ] 
| append 
    [| makeresults 
    | eval user_name="B1",role="Provider",event_name="Search Session",event_type="Admin" ] 
| append 
    [| makeresults 
    | eval user_name="B1",role="Provider",event_name="Search Session",event_type="Admin" ] 
| append 
    [| makeresults 
    | eval user_name="B1",role="Provider",event_name="Search Session",event_type="Patient" ] 
| append 
    [| makeresults 
    | eval user_name="B1",role="Provider",event_name="Search Session",event_type="Admin" ] 
| append 
    [| makeresults 
    | eval user_name="B1",role="Provider",event_name="Open Session",event_type="Admin" ] 
| append 
    [| makeresults 
    | eval user_name="C1",role="Physician",event_name="Open Session",event_type="Patient" ] 
| append 
    [| makeresults 
    | eval user_name="C1",role="Physician",event_name="Modify Session",event_type="Patient" ] 
| append 
    [| makeresults 
    | eval user_name="C1",role="Physician",event_name="Modify Session",event_type="Patient" ] 
| append 
    [| makeresults 
    | eval user_name="C2",role="Physician",event_name="Open Session",event_type="Patient" ] 
| append 
    [| makeresults 
    | eval user_name="C2",role="Physician",event_name="Open Session",event_type="Patient" ] 
| append 
    [| makeresults 
    | eval user_name="C3",role="Physician",event_name="Modify Session",event_type="Admin" ] 
| search role="Provider" 
| eval appended_event=event_name." ".event_type 
| table user_name role appended_event 
| stats count by user_name role appended_event 
| where count=1 
| fields- count

In your environment, you should write

<table with 4 columns as mentioned> 
| search role="Provider" 
| eval appended_event=event_name." ".event_type 
| table user_name role appended_event 
| stats count by user_name role appended_event 
| where count=1 
| fields- count

Well, I suggest you to create a drop-down filter in the dashboard for role and in the search pass it as a token like this | search role="$role$"

for the filter, you can put this in XML

<fieldset submitButton="false">
    <input type="dropdown" token="role" searchWhenChanged="true">
      <label>Select Role</label>
      <choice value="*">ALL</choice>
      <fieldForLabel>role</fieldForLabel>
      <fieldForValue>role</fieldForValue>
      <search>
        <query>index=&lt;your_index&gt; | dedup role | table role</query>
        <earliest>-60m@m</earliest>
        <latest>now</latest>
      </search>
    </input>
  </fieldset>

Let me know if this helps!

[mayurr98]

I may be wrong but you can try rare command .
have a look at this doc
http://docs.splunk.com/Documentation/Splunk/7.0.2/SearchReference/Rare#2._Return_the_least_common_va...

 | rare limit=<int> appended_event by user_name role

specify <int> accordingly.

let me know if this helps!

[493669]

Try like this:

...| search role="Provider" |stats count by user_name event_name event_type role|where count=1 

Or try:

| search role="Provider" 
|stats count by user_name event_name event_type role| eventstats min(count) as min by role|where count<=min

[mayurr98]

How do you find an unusual event? Suppose if you want to find unusual events for the role "Provider" then so every row that comes under row-provider you want to show?
As in example, there are 8 events for provider and in the output, you have shown only 3 events?

[amarrtya]

Hi ,

Thanks for responding. The reason there are three outputs for "Provider" is because the appended_events,
" View Session Patient", "Search Session Patient" and "Open Session Admin" have occurred the least number of times for the role "Provider" relative to all other events which have occurred more times. Hence these may be unusual. So relative frequency of occurrence within a role is the key to identify unusual activities.

[mayurr98]

Okay, I have given a solution for you. Try and let me know!

[amarrtya]

This is great. However, I am not just looking at a count of 1. In many cases it might happen that an event has happened more than once, but on looking at other events for same role that have happened significantly more number of times, the count of the unusual event is low. So I am looking for a way to quantify it as a percentage of occurrence of other more frequent events for the same role.

Eg: Total unique users for a role is 5. Total unique events in that role is 50. Total number of transactions for that role is 5000. But there are two events which has been done by 2 users only 3 times.
Those two events along with the users should get populated in the table.

Hope I have been able to shed some clarity to what I am trying to achieve.