topic Re: Search to identify missing data between 2 sets of data in Splunk Search

Search to identify missing data between 2 sets of data

collumc — Wed, 22 Nov 2017 21:30:19 GMT

Looking for an SPL way to identify missing data between 2 sets of data.
To simplify the problem, I will present it this way:

The first set of data is essentially the lookup and identifies the type of business unit as well as all required positions:
Business Unit Type Required position
Financial Director
Financial AsstDirector
Financial AdminAsst
Financial Lead
IT VicePresident
IT Director
Etc…
The second set of data is essentially the event data and identifies all people working in the company along with their business unit type and their position:
SSN Business Unit Type Position
111229999 IT Director
222114444 Financial Lead
444552222 Financial AsstDirector
999338888 Financial Director
334225544 IT VicePresident

How can I use SPL to determine which business units are missing required positions?

For example, the Financial business unit is missing an AdminAsst. In a standard programming language I could dedup the transactions coming in by business unit and then loop through the lookup for all required positions for that business unit, then search the list of employees for matches to each.

How can this done in SPL… are there a good, better, best ways to accomplish this?

Re: Search to identify missing data between 2 sets of data

collumc — Wed, 22 Nov 2017 21:35:37 GMT

the tabs threw the format off a little:
data set 1(lookup data):
Business Unit Type Required position
Financial Director
Financial AsstDirector
Financial AdminAsst
Financial Lead
IT VicePresident
IT Director
Etc…

Dataset 2:
SSN Business Unit Type Position
111229999 IT Director
222114444 Financial Lead
444552222 Financial AsstDirector
999338888 Financial Director
334225544 IT VicePresident

Re: Search to identify missing data between 2 sets of data

somesoni2 — Wed, 22 Nov 2017 22:01:29 GMT

Try like this (will give you all the "Business Unit Type" Position combination which are in lookup but not in event data)

your event data search to get all "SSN" "Business Unit Type" "Position" field values 
| stats count by "Business Unit Type" Position
| append [| inputlookup BU_Position_lookup.csv | table "Business Unit Type" "Required position" | rename "Required position" as Position | eval count=0]
| stats max(count) as count by  "Business Unit Type" Position | where count=0

Re: Search to identify missing data between 2 sets of data

collumc — Wed, 22 Nov 2017 22:22:12 GMT

I apologize for not being clear but the formatting was all crazy, so just want to clarify. it isn't setup as an official lookup table. the first table has 2 fields: Business Unit Type and Required position. So for each business unit there are multiple entries, one for each required position. The other data has 3 fields: SSN, business unit and their current position.

so the bottom line is that when a record from the second data source is read, we know the business unit being referred to and going to the first data source for that business unit, we know all of the required positions that need to be there. So we want to search through the 2nd data source to see if every position has an entry and report on what's missing.

Does your solution still solve this question?