topic Use same query for all the panels with different search value for just one field in Splunk Search

Use same query for all the panels with different search value for just one field

psmp — Sat, 28 Jul 2018 06:31:57 GMT

Problem to solve:

we have say 500 servers. out of 500 servers some servers have older versions of software installed. Some  server are even missing the softwares. we are trying to list this in the report.

SoftwareName    Version   NoOfhostwithsoftwareinstalled      NoOfHostmissingSoftwares                Listofhosts_thataremissing_softwares
Software1               1.0             300                                                        10                                                          server1
software1               1.1              190                                                                                                                      server2
                                                                                                                                                                                    ......
                                                                                                                                                                                 server10


I am trying to achieve this with the base search / postprocess.
I am able to list first 4 columns. But Not able to list the servers missing package. 

<code>
My dashboard XML:

<dashboard>
  <label>Installed Softwares List</label>
  <search id="baseSearch">
    <query>index=powershell  source="powershell://CCT-InstalledSoftware" sourcetype=CCT_Software host ="UE1*A01" 
    | dedup host, DisplayName, DisplayVersion</query>
    <earliest>-24h</earliest>
    <latest>now</latest>
  </search>
  <search base="baseSearch">
          <query> | stats dc(host) as TotalNoOfHosts values(host) as allhostlist</query>
          <done>
            <condition>
              <set token="tokhost">$result.TotalNoOfHosts$</set>
              <set token="tokhostlist">$result.allhostlist$</set>
            </condition>
          </done>
        </search>
  <row>
    <panel>
      <search base="baseSearch">
    <query>search DisplayName="software*"  | stats dc(host) as HostsWithPackage values(host) as packlist </query>
    <done>
      <condition>
        <set token="tokpack">$result.HostsWithPackage$</set>
        <set token="tokpacklist">$result.packlist$</set>
      </condition>
    </done>
  </search>
      <table>
        <title>List of Hosts with Package</title>
        <search base="baseSearch">
          <query>search DisplayName="software1*" | rex "DisplayVersion=(?<versionD1>\d+)" | rex "DisplayVersion=\d+.(?<versionD2>\d+)" | rex "DisplayVersion=\d+.\d+.(?<versionD3>\d+)" | rex "DisplayVersion=\d+.\d+.\d+.(?<versionD4>\d+)" | sort host -versionD1 -versionD2 -versionD3 -versionD4 | streamstats count by host | where count=1 | eval Version = versionD1.".".versionD2.".".versionD3.".".versionD4 | dedup host, DisplayName, DisplayVersion | dedup host, DisplayName, DisplayVersion | stats estdc(host) as HostsWithPackage by DisplayName DisplayVersion</query>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
    <panel>
      <table>
        <title>Hosts_missing_packages</title>
        <search>
          <query>| makeresults
 | eval Hosts_missing_packages=$tokhost$-$tokpack$ |table Hosts_missing_packages</query>
          <earliest>@d</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
  </row>
</dashboard>

Can someone suggest how do I pass the list values from both the searches to my final panel and find the missing list of servers.

Re: Use same query for all the panels with different search value for just one field

DalJeanis — Mon, 30 Jul 2018 03:38:23 GMT

OKay, I saw your other question and gave it a different answer. Here's another way of looking at this.

1) You need the first (most recent) record for each host for each DisplayName, along with DisplayVersion. If there is none, then you want a record that says "Not Installed". How about if we put "Not Installed" or "None" in the DisplayVersion in that case?

 index=powershell  source="powershell://CCT-InstalledSoftware" sourcetype=CCT_Software host ="UE1*A01"
 | dedup host, DisplayName
 | appendpipe 
    [| stats values(DisplayName) as DisplayName
     | eval killme="killme"
     | append [
         | metadata type=hosts 
         | where  host ="UE1*A01" 
         | table host 
         | eval DisplayVersion = "Not Installed" ]
     |  eventstats values(DisplayName) as DisplayName
     | where isnull(killme)
     |  mvexpand DisplayName]
 | dedup host, DisplayName

That will get you one record for each host... you can update the metadata call with whatever limitations you want on the entire list. Don't worry about the fact that some of the host will have already been found to have software - those dup events will come after the real records and will be killed by the second dedup command.

Re: Use same query for all the panels with different search value for just one field

psmp — Tue, 31 Jul 2018 16:42:04 GMT

Hi DalJeanis,

Thanks for your reply. But this gives only the installed Versions and still not displaying the missing hosts.

I found another way of doing it.

i calculated the no of hosts missing the softwares. If its greater than zero, I run a search which just displays the hostnames that do not have the software.

Thanks!

Also one more QQ. How do I edit the my question in answers.splunk.com??