I'm trying to set up a search for when a user disables their 2FA vs when IT disables it for them.
I have the User Account and the Actor account.
index=auth sourcetype=auth mfa* (actor.displayName="*") legacyEventType="core.user.factor.deactivate"
|stats count by "actor.displayName", "target{}.displayName", "displayMessage", _time
|rename "actor.displayName" AS "Changed by","target{}.displayName" AS "Account Changed", displayMessage as "Action"
Results look like
Changed by Account Changed Action _time count
Bob Johnson Mike Smith Reset factor for user 2018-10-09 15:16:19.880 1
Kelly Short Kelly Short Reset factor for user 2018-10-09 02:45:08.536 1
I'm trying to compare if the "Changed by" and "Account Changed" matched, and return just those results. And then, eval if it doesn't like to compare values and match() asks to compare a field to a regex.
Does anyone have any idea how to compare 2 field values from the same search?
Hi ColinJacksonPS,
give this a try:
index=auth sourcetype=auth mfa* (actor.displayName="*") legacyEventType="core.user.factor.deactivate"
| stats count by "actor.displayName", "target{}.displayName", "displayMessage", _time
| rename "actor.displayName" AS "Changed by","target{}.displayName" AS "Account Changed", displayMessage as "Action"
| where 'Change by' = 'Account Changed'
This will compare the values of both fields and only show the ones that are same.
Hope this helps ...
cheers, MuS
@ColinJacksonPS,
Try renaming the fields with space , compare and change it back
index=auth sourcetype=auth mfa* (actor.displayName="*") legacyEventType="core.user.factor.deactivate"
|stats count by "actor.displayName", "target{}.displayName", "displayMessage", _time
|rename "actor.displayName" AS "Changed","target{}.displayName" AS "Account", displayMessage as "Action"
|where Changed==Account
|rename Changed as "Changed by",Account as "Account Changed"
OR evaluate to a variable
index=auth sourcetype=auth mfa* (actor.displayName="*") legacyEventType="core.user.factor.deactivate"
|stats count by "actor.displayName", "target{}.displayName", "displayMessage", _time
|rename "actor.displayName" AS "Changed","target{}.displayName" AS "Account", displayMessage as "Action"
|eval isEqual=if(Changed==Account,"yes","no")
|rename Changed as "Changed by",Account as "Account Changed"
Hi ColinJacksonPS,
give this a try:
index=auth sourcetype=auth mfa* (actor.displayName="*") legacyEventType="core.user.factor.deactivate"
| stats count by "actor.displayName", "target{}.displayName", "displayMessage", _time
| rename "actor.displayName" AS "Changed by","target{}.displayName" AS "Account Changed", displayMessage as "Action"
| where 'Change by' = 'Account Changed'
This will compare the values of both fields and only show the ones that are same.
Hope this helps ...
cheers, MuS
I think where needs single quotes for field names which have non-alphanumerics.
yep, of course this always gets me eval()
requires in this case a '
instead of "
because using the "
will tell eval()
to compare two literal strings, not the values of two fields.
So,
using this | where "Change by" = "Account Changed"
will compare two strings
using this | where 'Change by' = 'Account Changed'
will compare the values of two fields
This did it. Single quotes FTW
index=auth sourcetype=auth mfa* (actor.displayName="*") legacyEventType="core.user.factor.deactivate"
| stats count by "actor.displayName", "target{}.displayName", "displayMessage", _time
| rename "actor.displayName" AS "Changed by","target{}.displayName" AS "Account Changed", displayMessage as "Action"
| where 'Changed by' = 'Account Changed'
Thanks @MuS @chanfoli
No, doesn't work. I changed it to "Change*d* by" and the result is still blank. Finds the 3 events in Events tab, but not the Statistics tab.
Also tried |where.... ==...., but I don't think that works with the where command
try it without rename and the original field name in the where