Join multiple lookup table to get result in single table

[basics]

Hi,

This is the case scenario:

when I run this search query:

index = "global" productID

I get the following result:

{ "productID" : "12",
   "UserID" : "123_username",
   "type" : "web_based"
}, ....

I get 100s of these result with various productID's. Either same productID or different productID but I get the result that has productID in the entire index = "global"

now the above query result have 2 key parameters that is of my interests. They are:

• productID
• UserID

Now, I have 2 additional lookup table. They are:

• Employee_lookup
  • {username, DepartmentName, .... }
  • in the above query result: username == UserID
    
    
• Product_lookup
  • {ProductID, ProductName, ... }
  • in the above query result: ProductID == ProductID

now my goal is to have one table that contains all the data, and my end result table will result with these columns:

DepartmentName,     ProductID,     ProductName,     UserID
----------------------------------------------------------------------------
Sales,                                 12,                   marketing,             123_username
Business,                          12,                   marketing,              323_username
Business,                          15,                   Online,                      523_username

Note that the ProductID and Product name are always same. All we are doing is fetching the ProductID, corresponding ProductName matching the ProductID, and then matching the UserID == Username, and related DepartmentName for the UserID...

basically, I want to search all the ProductID along with its department name, product name, and userID

How do I create a query for such end result? Could someone please help me?

[richgalloway]

Start with your existing search and add a lookup command for each lookup file needed.

index = "global" productID
| lookup Employee_lookup UserID as username OUTPUT DepartmentName
| lookup Product_lookup ProductID OUTPUT ProductName
| table DepartmentName, ProductID, ProductName, UserID

[basics]

Hi @richgalloway 

thanks so much. It works! 🙂

one last thing... how would I now group by DepartmentName, ProductID and get the count of each of them...

basically I want to get the count of ProductID by DepartmentName

one department has multiple products, so group by DepartmentName, ProductID, count

The end result would be like this:

{ DepartmentName, ProductName, ProductID, count }

[richgalloway]

Use the stats command to get counts and/or group results.

index = "global" productID
| lookup Employee_lookup UserID as username OUTPUT DepartmentName
| lookup Product_lookup ProductID OUTPUT ProductName
| stats count by DepartmentName, ProductName
| table DepartmentName, ProductName, ProductID, count