Splunk Enterprise Security

Need help excluding results which have field values that show up in another field

fdevera
Path Finder

Need help excluding results which have field values that show up in another field.

Search: 
| tstats `summariesonly` values(Authentication.user_priority) as user_priority earliest(_time) as earliest latest(_time) as latest count from datamodel=Authentication where Authentication.signature_id=4624 Authentication.Authentication_Package=NTLM Authentication.Logon_Type=3 Authentication.user!="ANONYMOUS LOGON" Authentication.Logon_Process="NtLmSsP" NOT Authentication.type=Information NOT Authentication.Key_Length=0 by Authentication.dest Authentication.user Authentication.dest_nt_domain
| `drop_dm_object_name("Authentication")`

Results:
dest field with fully qualified hostnames (computername.xx.mycompany.com) - can also be computername.yy.mycompany.com or computername.zz.mycompany.com.

des_nt_domain field should only have domains but also has computernames (computername) not fully qualified

How do I exclude results which have dest_nt_domain values that match dest values?

Example:
dest=lab001.xx.mycompany.com
dest_nt_domain=lab001

Any dest field value that starts with dest_nt_domain value, I would like it excluded from resulsts. The above example should be excluded.

Labels (2)
0 Karma
1 Solution

to4kawa
SplunkTrust
SplunkTrust
| makeresults 
| eval _raw="dest,dest_nt_domain
lab001.xx.mycompany.com,xx.mycompany.com
lab002.xx.mycompany.com,lab002
lab003.yy.mycompany.com,yy.mycompany.com
lab004.yy.mycompany.com,lab004
lab005.zz.mycompany.com,zz.mycompany.com
lab006.zz.mycompany.com,lab006"
| multikv forceheader=1
| where !match(dest,"^".dest_nt_domain)

@fdevera  @Gunnar  Let's make this easy.

View solution in original post

to4kawa
SplunkTrust
SplunkTrust
| makeresults 
| eval _raw="dest,dest_nt_domain
lab001.xx.mycompany.com,xx.mycompany.com
lab002.xx.mycompany.com,lab002
lab003.yy.mycompany.com,yy.mycompany.com
lab004.yy.mycompany.com,lab004
lab005.zz.mycompany.com,zz.mycompany.com
lab006.zz.mycompany.com,lab006"
| multikv forceheader=1
| where !match(dest,"^".dest_nt_domain)

@fdevera  @Gunnar  Let's make this easy.

View solution in original post

fdevera
Path Finder

Sorry I didn't explain clearly.

dest field has fully qualified computernames and while many follow naming convention, the majority do not and we're talking about 30,000+ systems.

dest_nt_domain field should only contain actual domains or bogus domains but in this case includes computernames which I suspect are local logins. e.g. COMPUTERNAME\username.

I need something that notices the unqualified part of the fully qualified computername in the dest field, matches it up with the same computername showing up in the dest_nt_domain field and based on that match, excludes the result.

0 Karma

Gunnar
Explorer

Maybe something like this:

| makeresults 
| eval _raw="dest,dest_nt_domain
lab001.xx.mycompany.com,xx.mycompany.com
lab002.xx.mycompany.com,lab002
lab003.yy.mycompany.com,yy.mycompany.com
lab004.yy.mycompany.com,lab004
lab005.zz.mycompany.com,zz.mycompany.com
lab006.zz.mycompany.com,lab006"
| multikv forceheader=1
| eval dest_tmp=replace(dest, "^(.+?)\..*$", "\1")
| where dest_nt_domain!=dest_tmp
| table dest, dest_nt_domain, dest_tmp

The eval puts the hostname part from dest in a temporary variable, the where command keeps only those results where the dest_nt_domain is not equal to the hostname stored in the temporary variable.

0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!