The problem here is unclear requirement: What is the logic to collapse the three rows after dedup into that single row? As @gcusello speculates, the three rows have common values of identity. Is t...
See more...
The problem here is unclear requirement: What is the logic to collapse the three rows after dedup into that single row? As @gcusello speculates, the three rows have common values of identity. Is this correct? Such should be be stated explicitly. The mock data also shows identical first and last for the three rows. Is this always true? Such should be stated explicitly, too. More intricately, the mock data contains different values of extensionAttribute11 and extensionAttribute10. What are the criteria of choosing one or another from these differing values in the collapsed table? Volunteers here cannot read minds. extensionAttribute10 in one of the three rows is blank; that in the rest rows is the same value. One can reasonably speculate that you want the non-blank value to be used in the collapsed table. But is this speculation correct? Are all non-blank values identical? Again, do not make volunteers read your mind. Additionally, what is the logic to determine which value remains with field name email, which goes to email2, email3, etc.? In the following example, I'll take arbitrary selection among emails (5), take every value of extensionAttribute11 (3), and take affirmative in (4). You get email extensionAtttribute10 extensionAttribute11 first last identity email2 email3 user@domain.com user@domain.com user@consultant.com user@domain.com User Surname USurname userT0@domain.com userT1@domain.com This the search index=collect_identities sourcetype=ldap:query user
| stats values(*) as * by first last identity
| eval idx = mvrange(1, mvcount(email))
| eval json = json_object()
| foreach idx mode=multivalue
[eval ordinal = <<ITEM>> + 1, json = json_set(json, "email" . ordinal, mvindex(email, <<ITEM>>))]
| spath input=json
| eval email = mvindex(email, 0)
| table email extension* first last identity email* (Of course, you can reduce extensionAttribute11 to one value if you know the logic.) Here is an emulation. Play with it and compare with real data. | makeresults format=csv data="email, extensionAttribute10, extensionAttribute11, first, last, identity
user@domain.com, , user@consultant.com, User, Surname, USurname
userT1@domain.com, user@domain.com, user@domain.com, User, Surname, USurname
userT0@domain.com, user@domain.com, user@domain.com, User, Surname, USurname"
``` the above emulates
index=collect_identities sourcetype=ldap:query user
```