New Authentication from Previously Unseen Source

[MikeElliott]

Hi Team,

I'm wondering if anyone can assist?

I'm attempting to write a search that will identify when a privileged user authenticates from a new asset - New asset as in, the privileged user has not been seen authenticating from this asset in at least 30 days.

I'm using the Authentication data model, which is accelerated.

Pseudocode:

Search the past 24 hours
Identify and group all "Source Assets" that an individual user has authenticated from
Compare to all "Source Assets" the user has authenticated from over the past 30 days
Alert where the user has authenticated from a Source Asset that they have not authenticated from over the past 30 days

Current Logic:

| tstats earliest(_time) as Earliest latest(_time) as Latest values(Authentication.src) AS Source FROM datamodel=Authentication WHERE Authentication.action=* BY Authentication.user sourcetype 
| rename Authentication.src AS Source_Asset 
| join type=left Source_Asset 
    [| tstats values(Authentication.src) AS Prev_Asset FROM datamodel=Authentication WHERE earliest=-30d by Authentication.user 
    | where Source_Asset!=Prev_Asset 
    | fields Prev_Asset] 
| `ctime(Earliest)` 
| `ctime(Latest)` 
| `drop_dm_object_name("Authentication")` 
| fields sourcetype Earliest Latest user Source_Asset

The ctime(Earliest) & ctime(Latest) macros are just formatting the earliest/latest times from epoch to human readable.

Now, am I being stupid, or has something gone awry with my logic?

Any assistance would be greatly appreciated.

[damien_chillet]

How about this:
Retrieve earliest, latest authentication per user, asset in the last 30 days.
Keep the records where earliest is in the last 24h (means the user/asset couple was not seen before in the last 30 days).
I'll let you add the privileged user lookup filter to tryout.

| tstats earliest(_time) as Earliest latest(_time) as Latest FROM datamodel=Authentication WHERE nodename=Authentication.Successful_Authentication Authentication.user!=*$ Authentication.src!=*DCT* earliest=-30d BY Authentication.user, Authentication.src
| where Earliest > relative_time(now(), "-24h")
| convert ctime(Earliest) ctime(Latest)

[MikeElliott]

I have managed to write logic that meets my requirements. It's really, really ugly logic, but it is not overly resource intensive on the search heads.

If anyone can improve the logic, please feel free to do so, otherwise I will mark this question as answered in a few days.

| tstats earliest(_time) as Earliest latest(_time) as Latest values(Authentication.src) AS Source_Assets_24h FROM datamodel=Authentication WHERE nodename=Authentication.Successful_Authentication Authentication.user!=*$ Authentication.src!=*DCT* BY Authentication.user 
| appendcols 
    [| tstats values(Authentication.src) AS Source_Assets_30d FROM datamodel=Authentication WHERE nodename=Authentication.Successful_Authentication Authentication.user!=*$ Authentication.src!=*DCT* earliest=-30d BY Authentication.user] 
| search 
    [| inputlookup Priv_User_List 
    | rename Priv_User as Authentication.user 
    | fields Authentication.user] 
| makemv Source_Assets_24h 
| mvexpand Source_Assets_24h 
| makemv Source_Assets_30d 
| mvexpand Source_Assets_30d 
| where NOT match(Source_Assets_24h, Source_Assets_30d) 
| stats values(Source_Assets_24h) as Source_Assets_24h values(Source_Assets_30d) as Source_Assets_30d values(Earliest) as Earliest values(Latest) as Latest by Authentication.user 
| `ctime(Earliest)` 
| `ctime(Latest)` 
| rename Source_Assets_24h AS New_Asset
| fields Earliest Latest Authentication.user 

[damien_chillet]

I don't think that search would return expected results because of how multivalue fields are being manipulated.
Also if the appendcols subsearch returns too much results it might get truncated and miss some data.
Please have a look at the search suggested in my answer below and let me know if that works better.

[Sukisen1981]

Hi,

I had a quick look at your code. You are using a left join with the field Source_Asset as your join field, but in your second tstats you actually do not have the Source_Asset field. See the join documentation here - http://docs.splunk.com/Documentation/Splunk/7.0.3/SearchReference/Join
Excerpt from example 2 , in the above link -
Example 2
If the field names in the sources do not match, you can rename the field in the subsearch result set. The field in the main search is product_id. The field in the subsearch is pid.
Note: The field names must match in name and in case. You cannot join product_id with product_ID.
... | join product_id [search vendors | rename pid AS product_id]

Now, there is no Source_Asset field in your second tstats, when you put in this statement | where Source_Asset!=Prev_Asset in your second tstats it does not find any Source_Asset field in your second tstats.

Without being aware of what your actual need is, I would wager that the join in your cide is returning no matches because there is no common join field.
Some additional tips:
I suspect if you really need to use join,there are limitations on join , also stated in the above splunk doc link + it does impact your query performance.
I can see that you have a common datamodel in both joins, can we not achieve your results without a join and using stats/ append / search or something like that? If you can give a mock of of all your data in the form the authentication data model with 1-2 examples of expected output it could help us.
However, if you are comfortable using join your query should work after you specify a common join field, consider replacing | where Source_Asset!=Prev_Asset with | rename Prev_Asset as Source_Asset for example

[MikeElliott]

Hi Sukisen,

Thanks for getting back to me. I think I was really tired this morning when writing this search. Now that I've had some sleep, I can see some of the mistakes I've made.

I am trying to determine when a Privileged User has been seen authenticating from an IP Address/Asset that they haven't authenticated from within the past 30 days.

Perhaps an option would be to use append and assign a "key" value of 2 to every Authentication.src a Privileged User has authenticated from in the past 30 days. Assign a "key" value of 1 to every Authentication.src a Privileged User has authenticated from in the past 24 hours and alert where "key" is not equal to 3?

What do you think?

[Sukisen1981]

I am not at all clear on the functional needs you have, and that is why i had asked for a mock up of your data and your expected output.
For example, you probably already have earliest, latest grouped by user by source assets from the first search itself. Why can't you just subtract earliest from latest and alert if the difference is >=30? Why use append/join at all?
I am sure I am missing something, but I am really unable to get your functional requirements here.
In fact I don't understand this stuff about the last 24 hours, if you have the earliest time per user per source asset I would just use something like - if now()-earliest >=30 then trigger alert

[MikeElliott]

Apologies - I thought that I had made the functional requirement clear.

I am interested in detecting instances of a privileged user account authenticating from an unusual source, as this may indicate something unusual going on with the privileged account. For example, if a privileged account is known to authenticate from asset A, B, C & D regularly, but suddenly authenticates from asset Z.

I would be interested in seeing:

• Earliest time authentication was seen for the unusual asset;
• Latest time authentication was seen for the unusual asset;
• Privileged account name that logged on from the unusual asset;
• The name of the unusual asset;

In some cases, I expect the Earliest/Latest times to be the same if the account only authenticated once, but I would like to have separate fields in case there have been multiple authentication events.