topic Re: data models deliver wrong values in Knowledge Management

data models deliver wrong values

denis_roehr — Tue, 29 Sep 2020 09:04:49 GMT

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

Re: data models deliver wrong values

martin_mueller — Thu, 10 Mar 2016 22:51:01 GMT

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

Re: data models deliver wrong values

denis_roehr — Fri, 11 Mar 2016 06:24:37 GMT

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

Re: data models deliver wrong values

denis_roehr — Fri, 11 Mar 2016 09:11:15 GMT

Thanks but...

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

Re: data models deliver wrong values

martin_mueller — Fri, 11 Mar 2016 23:34:37 GMT

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

Re: data models deliver wrong values

denis_roehr — Mon, 14 Mar 2016 07:21:05 GMT

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

Re: data models deliver wrong values

denis_roehr — Tue, 15 Mar 2016 07:22:07 GMT

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