topic Re: Join 4 source types with common field after eval in Splunk Search

Join 4 source types with common field after eval

msrama5 — Mon, 11 May 2020 20:02:26 GMT

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 )

Re: Join 4 source types with common field after eval

606866581 — Mon, 11 May 2020 21:02:25 GMT

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

Re: Join 4 source types with common field after eval

to4kawa — Mon, 11 May 2020 21:10:47 GMT

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

Re: Join 4 source types with common field after eval

606866581 — Mon, 11 May 2020 21:29:05 GMT

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

Re: Join 4 source types with common field after eval

to4kawa — Mon, 11 May 2020 21:34:35 GMT

append is need when some sourcetype is missing.

Re: Join 4 source types with common field after eval

msrama5 — Mon, 11 May 2020 23:09:02 GMT

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 ?

Re: Join 4 source types with common field after eval

to4kawa — Mon, 11 May 2020 23:19:53 GMT

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

Re: Join 4 source types with common field after eval

msrama5 — Mon, 11 May 2020 23:31:10 GMT

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 ?

Re: Join 4 source types with common field after eval

to4kawa — Mon, 11 May 2020 23:36:02 GMT

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?

Re: Join 4 source types with common field after eval

msrama5 — Mon, 11 May 2020 23:45:05 GMT

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

Re: Join 4 source types with common field after eval

msrama5 — Tue, 12 May 2020 00:00:40 GMT

correct, want to exclude source1 count values <=0

Re: Join 4 source types with common field after eval

to4kawa — Tue, 12 May 2020 00:06:59 GMT

add

| where source1 > 0 
| table Id source1