How to organize a search with two lookup tables: common and event-specific?

[pm771]

In my question I will use a manufacturing monitoring analogy.

Employees (uniquely identified by name) work a certain shift.

Their names and each completed unit of work are recorded.

There are two shifts.

While Splunk ingests every manufactured unit we are interested only in totals for a few particular employees on probation.

Each employee has a predefined work schedule, but it's not the same for everyone. Each employee has their own quota that does not change often.

We have two lookup tables: Employees and Holidays.

"Holidays" lists all common days off for the majority of employees - 1 column with data like "2021-01-01".

"Employees" provides details about work schedule and quotas.

Name    Shift Holidays DaysOfWeek  DaysOfMonth  Quota
John      1      Y        6,7                    100
Jim       2      N        6,7       15,16,17     3000
Nick      2      Y                               1000000

Our search will be scheduled to run twice a day at the end of each shift and needs to output probation employees that were scheduled to work and did meet their quota.

• We need to disregard all employees who are not in the table
• We need to filter just the current shift
• For employees that are not required to work on holidays, we need to exclude holidays
• For employees that have weekly days off, we need to exclude such days
• For employees that have monthly days off, we need to exclude them as well
• For the remaining, we need to compare their total to their quota

While I would not have a problem writing an SQL query for the above, I'm not sure about Splunk.

I'm also not certain if it's possible to match to comma-separated fields without "unpacking" them.

[to4kawa · Accepted solution]

index=yourIndex sourcetype=yourEvents [|inputlookup Employees |where Holiday="N" AND Shift="1"| fields name | format]  
| eval date=strftime(_time,"%F")
| lookup Holiday holiday as date OUTPUT holiday
| where isnotnull(holiday)
| lookup Employees name OUTPUT DaysOfWeek as DOW DaysOfMonth as DOM Quota
| eval DaysOfWeek=tonumber(strftime(_time,"%w")) % 7, DaysOfMonth=tonumber(strftime(_time,"%d"))
| where NOT (match(split(DOW,","),DaysOfWeek) OR match(split(DOM,","),DaysOfMonth))
| eventstats sum(Quota) by name

I don't know the logs , e.units also.
please add where with comparison to sum(Quota)

Splunk SPL(query) is made by looking the logs, not SPL/query.

I don't know DB. the DB technician can make the SQL without looking data?

[pm771]

Thank you very much.

My Events table is the data source (log). It has "Name" and "Units" (quantity of manufactured items) fields. Total of "Units" is to be compared to "Quota" (so it's a post-aggregation filtering).

I still have a question: Why do we need split()?

[to4kawa]

DOW, DaysOfWeek
---,---------
15,21
16
21

DaysOfWeek is single value in a row.

I see now, it's unnecessary. my habits.

[to4kawa]

see:
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/SQLtoSplunk

and why don't you provide your SQL query?

[pm771]

Something like below. Assuming there are DOW (single-digit) and DOM (double-digit) functions.

SET @shift=1

SELECT emp.name, SUM(e.units) as Total, emp.quota
FROM Employees emp
JOIN Events evt ON emp.name=evt_name
LEFT JOIN Holidays h ON evt.date=h.date
WHERE emp.shift=@shift
  AND emp.dow IS NOT NULL AND emp.dow NOT LIKE '%'+DOW(e.date)+'%'
  AND emp.dom IS NOT NULL AND emp.dom NOT LIKE '%'+DOM(e.date)+'%'
GROUP BY emp.name  
HAVING SUM(e.units) < emp.quota

[to4kawa]

e ?
Which is "Holidays" or "Employees" ?

if it's possible to match to comma-separated fields without "unpacking" them.
In splunk, It should be divided rows.

[pm771]

Table included in my question is "Employees".

"Holidays" is a single-column table with dates of holidays.

"Events" is the main feed table with many-to-one relationship with "Employees". It has two significant columns: Name and Units

I also forgot to exclude holidays for employees that work through them. So I need one more WHERE condition:

e.Holidays='N' OR h.date IS NOT NULL

My "comma-separated" fields are in Employees table: DaysOfWeek (DOW) and DaysOfMonth(DOM).

[pm771]

@to4kawa , Thank you very much for your help!

[to4kawa]

you're welcome. try to present your final query.