I have a lookup file which only contains one single column with names of all Software updates, I need compliance for.
I have a pivot query which provides details of each Software Update name along with its State if Install or applied.
How do i write a query using combination of pivot query and my lookup file. so that my result is only for the list of names in my lookup instead of all software updates list available in index.
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
splitrow
Guid
splitrow
State
splitrow
host
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
Guid
DisplayName
State
| sort limit=0 "Install Count per Update" desc
@sandeepshah81,
| inputlookup <your-lookup> | join type=left software_name [<query to get data from index>]
software_name
is field name in index data as well as in lookup. If you have different field name in index and lookup you can
rename
on one of the side to make them same.
Hope this fulfill your requirement.
thanks.. I was having same query.. but the count was not giving good reslts.. so trying ot see what is wrong.
Post your query, I'll check if I can help.