topic Re: Lookup file as a base how do I get count of installs based on the names in my lookup in Splunk ITSI

Lookup file as a base how do I get count of installs based on the names in my lookup

sandeepshah81 — Thu, 06 Jun 2019 09:46:08 GMT

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.

Re: Lookup file as a base how do I get count of installs based on the names in my lookup

VatsalJagani — Thu, 06 Jun 2019 11:38:46 GMT

@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.

Re: Lookup file as a base how do I get count of installs based on the names in my lookup

sandeepshah81 — Thu, 06 Jun 2019 11:44:04 GMT

thanks.. I was having same query.. but the count was not giving good reslts.. so trying ot see what is wrong.

Re: Lookup file as a base how do I get count of installs based on the names in my lookup

VatsalJagani — Thu, 06 Jun 2019 11:46:13 GMT

Post your query, I'll check if I can help.

Re: Lookup file as a base how do I get count of installs based on the names in my lookup

sandeepshah81 — Thu, 06 Jun 2019 12:50:26 GMT

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