Splunk Enterprise Security

Repeat offenders risk


I have a weighted score for repeat offenders using the following formula

| table _time id priority.name username hostname subject category.name
| eventstats count as UserCatCount by username category.name
| lookup myweightingtable.csv Category AS category.name OUTPUT Weight
| eval Weight=coalesce(Weight,1)
| eval TotalWeight=UserCatCount * Weight
| rename category.name as cname
| eval UserCatCount = tostring(cname)." => ".UserCatCount." @ ".Weight." = ".TotalWeight
| stats count as UserEventCount sum(TotalWeight) as UserEventWeight values(UserCatCount) as UserCatCount by username

The result displays a column with the username, usereventcount, usercatcount & usereventweight
which gives a row is something like this

userX 66 Improper Usage => 1 @ 5 = 5 158
Malicious => 5 @ 5 = 25
Phishing => 9 @ 5 = 45
Attempted Access => 1 @ 10 = 10
Threat Hit => 6 @ 6 = 36
Unauthorized Access => 2 @ 20 = 40
Uncategorized => 2 @ 1 = 2

I have had great assistance from @DalJeanis and managed to get the formula's working on my system but I am looking to go a little further now.

I want to get a user risk that's assigned to the user for a temporary period of time (which I can change we will say 10 days in default) so it auto resets after 10 days if that user does not have any further events. So I am trying to differentiate the user's historical score which could be 100+ (they get 1 point for doing something bad). So if I could have this separate field I could then sort users by this risk score and I would have the users who are at the most temporary risk and then the second I would sort by the riskiest users over time i.e their historical score.

thanks in advance


0 Karma


I would write their daily "points" to a summary index on a daily basis. That way, your search for all time is just a grand summary of the summary index.

To the same index, on a daily basis, I could add a "probation" record when they hit a certain number of points in a certain length of time (Z points over T days). That way, your search for bad guys is just a search on that index for a probation record within the given length of time,

Assuming that once on probation, they have a higher multiple, then that would just be an additional column (WeightProb) on the lookup table, and let the presence or absence of a recent Probation record determine which multiple to use when calculating that day's points.

Also each day, you would decide whether to write a new probation record to extend the probation. Z points over T days, or X points today with a probation record within Y days, or whatever business rule(s) you want to set.

0 Karma


Thanks, @woodcook but we are using dynamic lookup's as far as i know. It would help if you assisted me with coding this out.

Also @dalJeanis could you assist in coding this out as this is how i learn. I understand that i could just look at the last 24 hours and if a user has an event & its not a false positive then that would indicate they have done something of note. But i think a Risk field which is counts 5 days behind and 5 days forward would be better. So like a counter, it would add if the user had an event in this period. If they do then an action would be triggered such a communicating with another program to put them on a watchlist for X period of time. When the time expires and the user is at zero then another alert is triggered to take them off the watch list.

Really appreciative of the assistance here.

0 Karma


Also another query is with the line

| eval UserCatCount = tostring(cname)." => ".UserCatCount." @ ".Weight." = ".TotalWeight
| stats count as UserEventCount values(UserCatCount) as UserCatCount sum(TotalWeight) as UserEventWeight by username

How do i get another column i want to see in the result? So i want to see the Id of the event which is called 'ID" and i also want to display updated_on & created_on in the columns. Can anyone assist? thanks

0 Karma


Another issue is getting the last 24 hours, I can't do this at the moment due to the way my data is being pulled into Splunk so I need to specify that I only want updated tickets for the last 24H which I am doing via this search

| search status.name="Resolved" AND updated_on="2017-09-0"

I'd appreciate a query to say last 24 hours rather than this wildcard date, ive tried -24h, earliest etc. but didnt work, thanks

0 Karma

Esteemed Legend
0 Karma