Splunk Search

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

pm771
Path Finder

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.

0 Karma
1 Solution

to4kawa
Ultra Champion
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?

View solution in original post

to4kawa
Ultra Champion
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
Path Finder

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

0 Karma

to4kawa
Ultra Champion
DOW, DaysOfWeek
---,---------
15,21
16
21

DaysOfWeek is single value in a row.

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

0 Karma

to4kawa
Ultra Champion
0 Karma

pm771
Path Finder

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
0 Karma

to4kawa
Ultra Champion

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.

0 Karma

pm771
Path Finder

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

0 Karma

pm771
Path Finder

@to4kawa , Thank you very much for your help!

0 Karma

to4kawa
Ultra Champion

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

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...