Archive

cross checking information between two sourcetypes

New Member

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.

Tags (1)
0 Karma
1 Solution

Path Finder

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)

View solution in original post

0 Karma

Path Finder

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)

View solution in original post

0 Karma

Esteemed Legend

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

New Member

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.

0 Karma

New Member

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

0 Karma

New Member

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.

0 Karma

New Member

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!