Splunk Search

How to obtain the user account from a user SID ?

corti77
Contributor

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
Labels (1)
Tags (2)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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.

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

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?

 

0 Karma

corti77
Contributor

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

 

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

corti77
Contributor

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!

 

 

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

0 Karma

corti77
Contributor

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 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.

Get Updates on the Splunk Community!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...