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