I have 5 fields of data I want in a stats table, some of these fields have more than 1 value inside and they all correlate with each other.
Mac_Address (1 mac per device)
Apps (4 apps per device)
Compliance (1 compliance statuses for each of the apps (so 4))
LastCheckIn (I want the latest check-in, so 1)
Firmware (1 firmware level per device)
I wrote this query:
|stats list(Apps) as AllApps list(Compliance) as AllComp latest(LastCheckIn) as LatestCheckIn latest(Firmware) as latestFW by Mac_Address
The problem is there are duplicates in AllApps (Due to the nature of logs coming in etc.) I tried using values on apps, but then compliance is wrong (No longer paired correctly). Then tried making both app and comp values instead of list but then AllComp only provides 3 options (Compliant, non-compliant, error) and is not correlated to the apps
I could filter by moving compliance to the Mac_Address area but I do not want to have my table split by compliance level, I want it exclusively split by Mac_Address.
Ideal:
Any ideas? Thank you!
Run a |dedup Mac_Address Apps Compliance
before your stats with list function.
try this:
...|makemv Apps|mvexpand Apps|stats values(Apps) as AllApps values(Compliance) as AllComp latest(LastCheckIn) as LatestCheckIn latest(Firmware) as latestFW by Mac_Address