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