topic Re: How to organize a search with two lookup tables: common and event-specific? in Splunk Search

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

pm771 — Mon, 27 Apr 2020 19:54:31 GMT

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.

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

to4kawa — Mon, 27 Apr 2020 23:49:38 GMT

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

and why don't you provide your SQL query?

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

pm771 — Tue, 28 Apr 2020 23:18:53 GMT

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

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

to4kawa — Tue, 28 Apr 2020 23:36:25 GMT

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.

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

pm771 — Wed, 29 Apr 2020 23:03:10 GMT

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).

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

to4kawa — Wed, 29 Apr 2020 23:53:22 GMT

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?

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

pm771 — Thu, 30 Apr 2020 14:20:29 GMT

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()?

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

to4kawa — Thu, 30 Apr 2020 20:54:30 GMT

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

DaysOfWeek is single value in a row.

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

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

pm771 — Fri, 01 May 2020 17:56:38 GMT

@to4kawa , Thank you very much for your help!

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

to4kawa — Fri, 01 May 2020 20:30:53 GMT

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