How to obtain the user account from a user SID ?

[corti77]

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

[yuanliu]

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?

[corti77]

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 ".

[yuanliu]

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

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 ]
| where isnotnull(cn)

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

or

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

or

| 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.

[corti77]

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

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 ] 
| eval new_column=if(match(UserSID, "^LAB\\[a-zA-Z0-9_]+$"), UserSID, cn)

but the column "new_column" always appears empty.

Any idea on how to achieve this? Many thanks!

[yuanliu]

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

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
| eval UserSID = coalesce(cn, UserSID)
| fields - cn

Hope this helps.

[corti77]

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 

[yuanliu · Accepted solution]

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.