data models deliver wrong values

[denis_roehr]

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

[denis_roehr · Accepted solution]

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

[martin_mueller]

Your non-pivot query has an extra search admonEventType!="Deleted", that might drop those 60 users?

[martin_mueller]

There's also a difference of stats count vs stats count(objectGUID).

[denis_roehr]

excuse the delayed answer. I will test this and the results item sometime.

[denis_roehr]

Thanks but...

The search search admonEventType!="Deleted" has no effect on the return value .

[denis_roehr]

Thank you for your remark.
this attribute the difference remains like whether with or without. Gives unusual features still question it at pivot?