topic Re: How to obtain the user account from a user SID ? in Splunk Search

How to obtain the user account from a user SID ?

corti77 — Mon, 15 May 2023 15:02:22 GMT

Hi,

I am creating a query to identify users connected to our Exchange on-prem servers using Microsoft Modern Authentication.
So far I managed to get the user SID and using ldapfilter command I obtain the user account related to the SID but I get two rows for some reason.

Could someone point what could be the issue? or maybe a better way to reach the same goal.

index=msexchange sourcetype=MSExchange:2016:WinHttp host=LABMX1 AuthMethod=Bearer | stats count(_time) as totalConnections,earliest(_time) as lastcon,values(UserAgent) as UserAgents by UserSID | eval last_connection = strftime(lastcon, "%Y%m%d") | table UserSID,last_connection, UserAgents, totalConnections | appendpipe [| ldapfilter domain=LAB search="(objectSid=$UserSID$)" attrs=cn ]

the result

UserSID last_connection UserAgents totalConnections cn S-1-5-21-2872280-2353677280-1887909603-18694 20230515 Microsoft Office/16.0 (Windows NT 10.0; Microsoft Outlook 16.0.4266; Pro) 61 S-1-5-21-2872280-2353677280-1887909603-18694 20230515 Microsoft Office/16.0 (Windows NT 10.0; Microsoft Outlook 16.0.4266; Pro) 61 Josh Wright

Re: How to obtain the user account from a user SID ?

yuanliu — Tue, 16 May 2023 07:38:18 GMT

appendpipe adds the subpipeline to the main search results. You will get one row only if

| ldapfilter domain=LAB search="(objectSid=$UserSID$)" attrs=cn

returns no result.

Maybe you can start by explaining what you are trying to achieve with the main search and the subpipe? Is the sole purpose of the subpipe to provide cn? If that is the purpose, you command is working exactly as you designed.

ldapfilter is not a standard SPL command. So, it is not clear how it works with a dashboard token when the main search is not limited by the token. Maybe your actual code is (objectSid=UserSID), i.e., with the UserSID returned from the main search, not with a token $UserSID$? Or do you mean that you actually uses a token $UserSID$ that has the value of S-1-5-21-2872280-2353677280-1887909603-18694, while the main search

index=msexchange sourcetype=MSExchange:2016:WinHttp host=LABMX1 AuthMethod=Bearer

happens to also only return a single user S-1-5-21-2872280-2353677280-1887909603-18694?

Either way, the result suggests that the magical command ldapfilter returns last_connection, UserAgents, and totalConnections in addition to cn. Is this possible? For example, if you run

| makeresults | eval userSID = "S-1-5-21-2872280-2353677280-1887909603-18694" | ldapfilter domain=LAB search="(objectSid=" . UserSID . ")" attrs=cn

Does this give you last_connection, UserAgents, and totalConnections as well?

Re: How to obtain the user account from a user SID ?

corti77 — Tue, 16 May 2023 08:50:58 GMT

Hi @yuanliu ,

thanks for your reply.

The query aims to identify the list of authentication methods (Negotiate or Bearer) and the email clients used to connect to our Exchange on-prem deployment. The goal is to also get the last connection for each combination.

For that, I slightly changed the query to make it more simple.

index=msexchange sourcetype=MSExchange:2016:WinHttp host=LABMX1 | stats latest(_time) as LastTime by UserSID,UserAgent,AuthMethod | eval TimeConnection = strftime(LastTime, "%Y%m%d-%H:%M")

the results are

UserSID TimeConnection UserAgent AuthMethod ATLAS-LAB\jcortijo 20230515-17:52 Microsoft Office/14.0 (Windows NT 6.2; Microsoft Outlook 14.0.7266; Pro) Negotiate S-1-5-21-287652280-2353677280-1887909603-18694 20230515-17:39 Microsoft Office/16.0 (Windows NT 10.0; Microsoft Outlook 16.0.4266; Pro) Bearer

In cases of Bearer authentication, the userSID needs to be converted to a human-readable version, and that is when I would like to use ldapfilter or ldapserach.

I tried your query

| makeresults | eval userSID = "S-1-5-21-2872280-2353677280-1887909603-18694" | ldapfilter domain=LAB search="(objectSid=" . UserSID . ")" attrs=cn

it seems like the "." is not a valid operator.

External search command 'ldapfilter' returned error code 1. Script output = "error_message=invalid filter ".

Re: How to obtain the user account from a user SID ?

yuanliu — Wed, 17 May 2023 06:52:37 GMT

Thanks for the explanation. So, I see two parts in this question. One is why/how ldapsearch command returns last_connection, UserAgents, and totalConnections (in addition to cn), two is whether the full result meets your need.

Let me start with the second question. If the goal is to return a human readable user name, is cn the best choice? When autmethod is negotiate, UserSID is of the format <domain>\user ID. Do you want to return/compose something similar?

If cn is sufficient for your purpose, and all you want is to have the row containing cn, all you need is

The first question is really a curiosity. Since I really have no idea how the external command work, the test I proposed was purely a blind guess. Maybe $UserSID$ is not at all a dashboard token as I interpreted? Maybe the command substitutes $UserSID$ with the value of field UserSID?

You can always try

| ldapfilter domain=LAB search="(objectSid=$S-1-5-21-2872280-2353677280-1887909603-18694$)" attrs=cn

| makeresults | eval userSID = "S-1-5-21-2872280-2353677280-1887909603-18694" | ldapfilter domain=LAB search="(objectSid=$UserSID$)" attrs=cn

| makeresults | eval userSID = "S-1-5-21-2872280-2353677280-1887909603-18694" | ldapfilter domain=LAB search="(objectSid=$" . UserSID . "$)" attrs=cn

and see what comes out. But I don't think the answer will help you reach your end result much.

Re: How to obtain the user account from a user SID ?

corti77 — Wed, 17 May 2023 07:41:00 GMT

Thanks @yuanliu ,

I was using a similar approach to eliminate duplicates generated by ldapfilter command.

index=msexchange sourcetype=MSExchange:2016:WinHttp host=LABMX1 | stats latest(_time) as LastTime by UserSID,UserAgent,AuthMethod | appendpipe [| ldapfilter domain=LAB search="(objectSid=$UserSID$)" attrs=cn ] | search cn=*

The problem is that I have two types of events, one with a human-readable value domain\user and the SIDs.

With this command the tuples that are not SIDs are removed as the ldapfilter command returns nothing for them.

Do you know a way I could append a new column that simply replicates the UserSID in case follow the regex ^domain\\[a-zA-Z0-9_]+$ and appends the output of ldapfilter otherwise? I tried with

but the column "new_column" always appears empty.

Any idea on how to achieve this? Many thanks!

Re: How to obtain the user account from a user SID ?

yuanliu — Thu, 18 May 2023 03:17:33 GMT

If you want to combine results from the two AuthMethod, there's a simpler method:

index=msexchange sourcetype=MSExchange:2016:WinHttp host=LABMX1 | stats max(_time) as LastTime by UserSID,UserAgent,AuthMethod | appendpipe [| ldapfilter domain=LAB search="(objectSid=$UserSID$)" attrs=cn ] | stats values(cn) as cn by UserSID,UserAgent,AuthMethod LastTime

If you don't want to see that ugly UserSID when AuthMethod is bearer, you can even put cn into UserSID like this

Hope this helps.

Re: How to obtain the user account from a user SID ?

corti77 — Thu, 18 May 2023 07:33:06 GMT

Yes! Thanks a lot . Coalesce, I forgot about that one 😉

Finally, I would like to group all the rows that belong to the same UserSID, I tried using stats list().

Is it correct? As I need to use one list() per field, I was wondering if the columns do not get mixed up.

index=msexchange sourcetype=MSExchange:2016:WinHttp host=LAB | stats latest(_time) as LastTime by UserSID,UserAgent,AuthMethod | eval TimeConnection = strftime(LastTime, "%Y%m%d-%H:%M") | appendpipe [| ldapfilter domain=LAB search="(objectSid=$UserSID$)" attrs=cn ] | stats values(cn) as cn by UserSID,UserAgent,AuthMethod, TimeConnection | eval UserSID = coalesce(cn, UserSID) | fields - cn | stats list(UserAgent),list(AuthMethod), list(TimeConnection ) by UserSID

thanks a lot once again @yuanliu

Re: How to obtain the user account from a user SID ?

yuanliu — Thu, 18 May 2023 14:53:18 GMT

In your use case, list is the way to go. List preserves the order of rows, so multiple fields will be aligned properly across columns.

Given that UserSID with negotiate auth method and cn (with bearer) are drastically different, there is no chance the same user will have identical UserSID after coalesce. As such, you can use AuthMethod as groupby and not run a list. (AuthMethod will have the same value listed twice for each UserSID.) If you want to group by true user, a different LDAP attribute (or combination of attributes) will be needed instead of cn.