Hi,
I deal with data models for a couple of days and i have trouble with different values.
My query delivers the value "3252" for all active users.
index=msad admonEventType!=Schema admonEventType!=Start sourcetype=ActiveDirectory | dedup objectGUID | regex objectClass="(?i)\|user$" | search (userAccountControl="512" OR userAccountControl="66048" OR userAccountControl="66080") | search admonEventType!="Deleted" | stats count
The data model with pivot returns "3312" active Users?!
| pivot ROOT AD_Object_Management splitrow objectGUID splitrow userAccountControl splitrow admonEventType splitrow objectClass | dedup objectGUID | regex objectClass="(?i)\|user$" | search (userAccountControl="512" OR userAccountControl="66048" OR userAccountControl="66080") | stats count(objectGUID)
AD_Object_Management contains only this:
index=msad admonEventType!=Schema admonEventType!=Start sourcetype=ActiveDirectory
what do I make wrong?
Thanks
I have found a solution. The data models have a problem with the deduplication of the GUIDs. I have tried out different things and have reached the following knowledge. Root Events do not seem to be suitable for such queries.
| pivot model splitrow objectGUID | dedup objectGUID or | pivot model splitrow objectGUID | stats latest(*) AS * by objectGUID or | pivot model splitrow objectGUID | stats first(*) AS * by objectGUID
Different admonEventTypes in active directory (Sync, Update, Deleted, Start, Schema). The deduplication of the objectGUID does not process these dependences correctly, because deleted objects are nevertheless displayed in the result.
I have added a Root Search to data model and now we have the correctly results.
here my is my root search code:
index=msad sourcetype=ActiveDirectory | regex objectClass="(?i)\|user$" | stats latest(*) AS * by objectGUID | search admonEventType!=Deleted
Now only the last admonEventTypes per objectGUID is in the result.
Thanks
I have found a solution. The data models have a problem with the deduplication of the GUIDs. I have tried out different things and have reached the following knowledge. Root Events do not seem to be suitable for such queries.
| pivot model splitrow objectGUID | dedup objectGUID or | pivot model splitrow objectGUID | stats latest(*) AS * by objectGUID or | pivot model splitrow objectGUID | stats first(*) AS * by objectGUID
Different admonEventTypes in active directory (Sync, Update, Deleted, Start, Schema). The deduplication of the objectGUID does not process these dependences correctly, because deleted objects are nevertheless displayed in the result.
I have added a Root Search to data model and now we have the correctly results.
here my is my root search code:
index=msad sourcetype=ActiveDirectory | regex objectClass="(?i)\|user$" | stats latest(*) AS * by objectGUID | search admonEventType!=Deleted
Now only the last admonEventTypes per objectGUID is in the result.
Thanks
Your non-pivot query has an extra search admonEventType!="Deleted"
, that might drop those 60 users?
There's also a difference of stats count
vs stats count(objectGUID)
.
excuse the delayed answer. I will test this and the results item sometime.
Thanks but...
The search search admonEventType!="Deleted"
has no effect on the return value .
Thank you for your remark.
this attribute the difference remains like whether with or without. Gives unusual features still question it at pivot?