How can I pull two lists of hosts from a datamodel and from metadata search in one search?

[mattbellezza]

I am trying to concatinate two searches that I already have working. One pulls host list from an Asset List in the PCI App, another pulls a host list from metadata. I am trying to come up with a diff between what hosts I have in the asset list verses everything that is logging. Here is my attempt so far:

[| metadata type=hosts 
    | eval "Last Logged Date"=strftime(recentTime, "%+") 
    | eval "Days Since Last Logged"=round((now() - lastTime)/86400) 
    | search "Days Since Last Logged"<=30 ] 
    [| `asset_eventcount` 
    | search (`get_category(*)`) () 
    | sort 0 - lastTime 
    | `uitime(firstTime)` 
    | `uitime(lastTime)` 
    | eval last_logged = round((now() - lastTime)/86400) 
    | replace -1 with 0 in last_logged 
    | eval last_logged = if(last_logged<30, "Logging", last_logged) 
    | eval last_logged = if(last_logged>30, "Stopped Logging", last_logged) 
    | eval last_logged = if(isnull(last_logged) OR last_logged="", "Never Logged", last_logged) ] | table host nt_host

[DalJeanis · Accepted solution]

You need a connection verb between the two, probably append. Assuming that your macro asset_evencount resolves to a generating command, then this should work...

 | `asset_eventcount` 
 | search (`get_category(*)`) () 
 | sort 0 - lastTime 
 | `uitime(firstTime)` 
 | `uitime(lastTime)` 
 | eval last_logged = round((now() - lastTime)/86400) 
 | replace -1 with 0 in last_logged 
 | eval last_logged = if(last_logged<30, "Logging", last_logged) 
 | eval last_logged = if(last_logged>30, "Stopped Logging", last_logged) 
 | eval last_logged = if(isnull(last_logged) OR last_logged="", "Never Logged", last_logged) 

 | append [| metadata type=hosts 
     | eval "Last Logged Date"=strftime(recentTime, "%+") 
     | eval "Days Since Last Logged"=round((now() - lastTime)/86400) 
     | search "Days Since Last Logged"<=30 
     ]

| table host nt_host

[mattbellezza]

That almost worked. The "host" column is empty... I think its an issue with the metadata search. It seems to only want to output results from my Asset_Eventcount macro...

[DalJeanis]

1) There is no field nt_host on the metadata type=hosts, so that should result in ONLY the host field values.

2) There is no sense in calculating "Last Logged Date" if you are not going to use it.

While debugging, change the table command to this...

| table host nt_host last_logged "Last Logged Date" "Days Since Last Logged"

[mattbellezza]

After much back and forth I figured it out. My subsearches were not properly ordered...
| metadata type=hosts
| dedup host
| eval "Last Logged Date"=strftime(recentTime, "%+")
| eval "Days Since Last Logged"=round((now() - lastTime)/86400)
| search "Days Since Last Logged"<=30
| eval host=lower(host)
| search NOT
[| asset_eventcount
| search (get_category(*)) ()
| sort 0 - lastTime
| uitime(firstTime)
| uitime(lastTime)
| eval last_logged = round((now() - lastTime)/86400)
| replace -1 with 0 in last_logged
| eval last_logged = if(last_logged<30, "Logging", last_logged)
| eval last_logged = if(last_logged>30, "Stopped Logging", last_logged)
| eval last_logged = if(isnull(last_logged) OR last_logged="", "Never Logged", last_logged)
| table ip,mac,nt_host,dns,owner,bunit,category,pci_domain,is_expected,firstTime,lastTime,last_logged
| search last_logged="Logging"
| fields nt_host
| rename nt_host as host] | stats dc(host)

[mattbellezza]

I should note: All of the evals are there so that I can do an inline search after that looks for hosts only seen in the last 30 days, anything else I assume has stopped sending me logs. The results of the search should be asset_list_hosts - non_matching_metadata_hosts = total hosts logging that are not part of the asset list