topic cross checking information between two sourcetypes in Splunk Search

cross checking information between two sourcetypes

asuratos — Sat, 28 Apr 2018 05:42:19 GMT

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.

Re: cross checking information between two sourcetypes

asuratos — Sat, 28 Apr 2018 05:47:37 GMT

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

Re: cross checking information between two sourcetypes

asuratos — Sat, 28 Apr 2018 22:34:20 GMT

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.

Re: cross checking information between two sourcetypes

woodcock — Sat, 28 Apr 2018 23:22:45 GMT

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

Re: cross checking information between two sourcetypes

asuratos — Sun, 29 Apr 2018 05:49:50 GMT

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.

Re: cross checking information between two sourcetypes

asuratos — Sun, 29 Apr 2018 06:04:03 GMT

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?

| 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

Re: cross checking information between two sourcetypes

rslama — Thu, 03 May 2018 20:05:09 GMT

have you tried this?