Join 4 source types with common field after eval

[msrama5]

Hello, I have 4 sources (source 1-4) , common field for source 1 to 3 is Properties.Id, source4 common field is Id, does not have properties., I need to join these 4 sources on field Properties.Id, since source 4 just has Id, I renamed this field to Properties.Id using eval, with eval change, the query below is not working, it is not doing the join correctly using field Properties.Id, any ideas what is the issue here ?
Splunk query-
sourcetype=source1 OR sourcetype=source2 OR sourcetype=source3 OR sourcetype=source4

| eval Properties.Id=if(sourcetype="source4",Id,null())
| stats values(Properties.Id) as Id by sourcetype
|append [|makeresults
|eval sourcetype=split("source1 ,source2 ,source3 ,source4" ,",")
| mvexpand sourcetype
| fields sourcetype]
| fillnull value="Not exists" Id| chart count over Id by sourcetype
|sort (Id )

[606866581 · Accepted solution]

It looks like you're overwriting the value of Properties.Id with this command:

| eval Properties.Id=if(sourcetype="source4",Id,null())

Your 3 original sources will now lose their Properties.Id field
Take a look at the coalesce eval command https://docs.splunk.com/Documentation/Splunk/8.0.3/SearchReference/ConditionalFunctions

[to4kawa]

sourcetype=source1 OR sourcetype=source2 OR sourcetype=source3 OR sourcetype=source4
| eval Id=coalesce('Properties.Id',Id)
| stats count by Id sourcetype
|append [|makeresults 
|eval sourcetype=split("source1 ,source2 ,source3 ,source4" ,",")
| mvexpand sourcetype
| fields sourcetype]
| xyseries Id sourcetype count
| fillnull source1 ,source2 ,source3 ,source4  value="Not exists" 
| sort Id

[606866581]

I'm not sure what your particular use case is, but to me it seems like this would be a simpler way to create the same table without needing to |append

sourcetype=source1 OR sourcetype=source2 OR sourcetype=source3 OR sourcetype=source4
| eval Id=coalesce('Properties.Id',Id)
| stats count by Id sourcetype
| xyseries Id sourcetype count
| fillnull source1 source2 source3 source4 value="Not exists"
| sort Id

[to4kawa]

append is need when some sourcetype is missing.

[msrama5]

hi @606866581 thanks, it works, I need to add one more filter condition for values count in source1 sourcetype > 0 and only show those values , where to apply filter for source1 values count > 0 ?

[to4kawa]

the query result:

Id source1 source2 source3 source4
-- ------- ------- ------- ------- 
X 1 1 1 1 
Y 0 1 1 1
Z 1 0 0 1

what you want:

Id source1
-- ------- 
X 1
Z 1    

Is this?

[msrama5]

correct, want to exclude source1 count values <=0

[to4kawa]

add

| where source1 > 0 
| table Id source1

[msrama5]

for example in below case source1 =0 values also shown
Actual
Id source1 source2 source3 source4

---

X 1 1 1 1
Y 0 1 1 1
Z 1 0 0 1

Expected- exclude <=0 values for source1 count, so output will be
Id source1 source2 source3 source4

---

X 1 1 1 1
Z 1 0 0 1

[msrama5]

thanks, it works, I need to add one more filter condition for values count in source1 sourcetype > 0 and only show those values , where to apply filter for source1 values count > 0 ?

[to4kawa]

hi @msrama5
I make the query from @606866581 advice.
you say it work , so you should accept the answer first.