Combine two table searches into 1 to compare the results, but one has an inputlookup for inventory and the other is sear

[brglaze]

I currently have 2 different tables where the first one shows the number of firewalls each location has (WorkDay_Location) from an inventory lookup file, and a second table that shows how many firewalls are logging to splunk through searching the firewall indexes to validate they are logging. I would like to combine them, and have a 3rd column that shows the difference. 

I run into problems with multisearch since I am using a lookup (via inputlookup), and another lookup where I search for firewalls by hostname, and if the hostname contains a certain naming convention it matches the hostname to a lookup file with the hostname to WorkDay_Location.

FIREWALLS FROM INVENTORY - by Workday Location

| inputlookup fw_asset_lookup.csv
| search ComponentCategory="Firewall*"
| stats count by WorkDay_Location

FIREWALLS LOGGING TO SPLUNK - by Workday Location

index=firewalls OR index=alerts AND host="*dmz-f*"
| rex field=host "(?<hostname_code>[a-z]+\-[a-z0-9]+)\-(?<device>[a-z]+\-[a-z0-9]+)"
| lookup device_sites_master_hostname_mapping.csv hostname_code OUTPUT Workday_Location
| stats dc(host) by Workday_Location
| sort Workday_Location

Current output:
Table 1: Firewalls from Inventory Search
WorkDay_Location   count
Location_1                   5
Location_2                   5

Table 2: Firewalls Logging to Splunk search
WorkDay_Location  count
Location_1                  3
Location_2                  5

Desired output
WorkDay_Location      FW_Inventory      FW_Logging      Diff
Location_1                      5                                 3                            2
Location_2                      5                                 5                            0

Appreciate any help if this is possible.

[bowesmana]

This fits into the "proving a negative" where you're trying to find things that are NOT reporting

This is the general way to do that

index=firewalls OR index=alerts AND host="*dmz-f*"
| rex field=host "(?<hostname_code>[a-z]+\-[a-z0-9]+)\-(?<device>[a-z]+\-[a-z0-9]+)"
| lookup device_sites_master_hostname_mapping.csv hostname_code OUTPUT Workday_Location
| stats dc(host) as hosts_reporting by Workday_Location
| append [
  | inputlookup fw_asset_lookup.csv where ComponentCategory="Firewall*"
  | stats count as expected_hosts by WorkDay_Location
]
| stats values(*) as * by WorkDay_Location
| eval diff=expected_hosts - hosts_reporting

so you do your basic search to count the reporting hosts and then add on the list of hosts you expect to see and then join them together with the last stats and then calc the difference