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.
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.
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?
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?
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()
?
DOW, DaysOfWeek
---,---------
15,21
16
21
DaysOfWeek
is single value in a row.
I see now, it's unnecessary. my habits.
see:
https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/SQLtoSplunk
and why don't you provide your SQL query?
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
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.
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).
@to4kawa , Thank you very much for your help!
you're welcome. try to present your final query.