cross checking information between two sourcetypes

[asuratos]

I have two sourcetypes. first is a table of different pet types and respective animal. second is showing which pet is owned. Need to create a search that will show which pets-name are not owned:

SourcetypeA:
animal name

dog howser
dog dodge
cat jake
mouse lola

SourcetypeB:
owner pet-name

Alvin dog-dodge
Ellie cat-jake

Tried using join and/or subsearch but cannot seem to get a table that shows mouse-lola and dog-howser do not have owners.

[rslama · Accepted solution]

have you tried this?

SourcetypeA
| eval pet-name=animal+"-"+name
| table pet-name
| join pet-name type=outer
[ search SourcetypeB
| table pet-name owner]
| where isnull(owner)

[woodcock]

Try this:

| makeresults
| eval raw="dog howser:dog dodge:cat jake:mouse lola"
| makemv delim=":" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<animal>\S+)\s+(?<name>\S+)$"
| eval sourcetype="SourcetypeA"
| append [
| makeresults
| eval raw="Alvin dog-dodge:Ellie cat-jake"
| makemv delim=":" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<owner>\S+)\s+(?<pet_name>\S+)$"
| eval sourcetype="SourcetypeB" ]
| rename pet_name AS "pet-name"
| fields - _time

| rename COMMENT AS "Everything above generates sample event data; everything below is the solution"

| rex field="pet-name" "(?<animal>[^-]+)-(?<name>[^-]+)$"
| stats values(*) AS * BY animal
| eval owned=if(isnotnull(owner), "YES", "NO")
| stats values(name) AS name BY animal owned

[asuratos]

Also, a solution that might be suggested is to use lookup. my concern with this is that the list from sourcetypeA might change more often. I read that using lookup is only good if the lookup table does not change as often.

[asuratos]

adding comma to clarify the fields for each sourcetype

SourcetypeA:
animal, name

dog, howser
dog, dodge
cat, jake
mouse, lola

SourcetypeB:
owner, pet-name

Alvin, dog-dodge
Ellie, cat-jake

[asuratos]

I copied and pasted the code below. it shows that for one of the dogs that is not owned, it does not say "No". I expect the same line to show which is same as mouse that is not owned.

My goal is to understand how I can apply this to a sourcetypeA and sourcetypeB that has much larger data set. I suppose that if I apply it to my actual use case that follows the same pattern, I can skip eval=raw statements.

this is close but may need a bit more tweak to the search command. thanks.

[asuratos]

if actually have a lot of data that is already ingested into different events for sourcetypeA and sourcetypeB, to type the command to cross check, do I just do the following?

sourcetype=sourcetypeA
| rex "^(?\S+)\s+(?\S+)$"
| append [
sourcetype=sourcetypeB
| rex "^(?\S+)\s+(?\S+)$" ]
| rename pet_name AS "pet-name"
| fields - _time ------ I am not sure what this line does

| rename COMMENT AS "Everything above generates sample event data; everything below is the solution"

| rex field="pet-name" "(?[^-]+)-(?[^-]+)$"
| stats values(*) AS * BY animal
| eval owned=if(isnotnull(owner), "YES", "NO")
| stats values(name) AS name BY animal owned