So what we are targetting to get out of this is , Even though it should show per update how many PC's are installed with, Fnal status should be overall compliance for all updates as percentage.
Instead of join, I am using lookup file and not using inputlookup option as I assume this is faster than Join. Let me know if that is wrong assumption.
1 lookup file is with list of all PC's in our environment.. second lookup is Software Updates which I want to show compliance for.
| pivot uA_DM_Application_SoftwareUpdateInventory Application_SoftwareUpdateInventory
latest(DisplayName) as DisplayName
filter host in (*)
filter State in (install)
|rename host as Host
|lookup UKDesktopAsset.csv Host OUTPUT Host as host
|eventstats count(host) as TotalHosts
|lookup SoftwareUpdate.csv DisplayName Output DisplayName
|where Host=host AND DisplayName=DisplayName
|eval Percentage=round("Install Count per Update"/TotalHosts)*100,2)
| stats dc(host) as "Install Count per Update" by
| sort limit=0 "Install Count per Update" desc