Compliance Dashboard

[Foolish_Rogue]

I would like to create a search or a series of searches to retrieve all of my Windows Servers from LDAP. After obtaining the list of servers, I plan to check each one against an index that contains all the installed applications. The purpose of this is to determine whether specific applications are installed on each server. Finally, I want to output a table with columns for the host and a 'Yes' or 'No' indicator for each application, reflecting its installation status.

I have tried using the | ldapsearch with filters to build a csv named AD_servers.csv.  I then try the search below:

| inputlookup AD_servers.csv

| join type= left host

     [ search index=installed-apps (DisplayName=App1 OR DisplayName=App2 OR DisplayName=App3                      OR DisplayName=App4)

     | stats values(DisplayName) as displayNames by host ]

| eval App1 = if(match(tostring(displayNames), "App1"), "YES", "NO")

| eval App2 = if(match(tostring(displayNames), "App2"), "YES", "NO")

| eval App3 = if(match(tostring(displayNames), "App3"), "YES", "NO")

| eval App4 = if(match(tostring(displayNames), "App4"), "YES", "NO")

| fields host, App1, App2, App3, App4

| table host, App1, App2, App3, App4

My issue seems to be with the | stats values() as some of my hosts have their data in the index but the search returns empty for DisplayName.  Can anyone help me with advice on how to accomplish my goal?

[PickleRick · Accepted solution]

First and foremost - don't use join. There are some rare cases when join can be useful but this isn't one of them. As a rule of thumb - this is not SQL, joins are a no-no.

Just do your 

index=installed-apps (DisplayName IN (App1, App2, App3, App4))
| fields DisplayName host

to get your initial "report" of the data from the index. Check if it's good.

Now we use a neat trick to save ourselves the need to write all those evals.

| eval have{DisplayName}=1

This way if you have an event where DisplayName was named "App1", you'll get a field called haveApp1 with a value of 1.

Now you can simply do

| stats values(have*) as * by host

And you have the table from your indexed data. If you want to filter it by your lookup, you just do

| lookup AD_servers.csv host output host as matched
| search matched=*

If you want to show rows even for hosts which are in the lookup but which aren't in your index, you'll have to go another way. Instead of immediately statsing your data you go

| inputlookup append=t AD_servers.csv

And now you can do your 

| stats values(have*) as * by host