topic Re: how to combine two sources from same index with a common field in Splunk Search

how to combine two sources from same index with a common field

vrmandadi — Tue, 29 Sep 2020 19:29:29 GMT

Hello,

I have and index=A with two sources A and B and I want to get two fields(Geo_Name,Geo_Type) from source B using the ID as common field and do what ever stats on that

Below are the sources with fields

source A=ID,Views,Co,Camp,Creative,Insertion_Order,Place,Site,Co
sourceB=ID,Views,Camp,Creative,Insertion_Order,Place,Geo_Name,_Geo_type

Below is the query I am using

index=main sourcetype=sftp (source=A OR source=B)
| stats dc(source) as count sum(Views) as views values("Co") as CO values(Geo_Name) as GEO by ID
|search count=2|fields- count

but when a I do the stats by Co instead of ID(common field) I dont get any results

index=main sourcetype=sftp (source=A OR source=B)
| stats dc(source) as count sum(Views) as views values("Co") as CO values(Geo_Name) as GEO by "Co"
|search count=2|fields- count

Does join work ? or any other command which gives that

Re: how to combine two sources from same index with a common field

somesoni2 — Wed, 16 May 2018 18:29:45 GMT

If ID is the only common field, then grouping by other field would not give you any results. Whats your requirement here?

Re: how to combine two sources from same index with a common field

vrmandadi — Tue, 29 Sep 2020 19:34:23 GMT

Hello @somesoni2

Sorry for the confusion .Let me be clear in my explanation

Both source A and B are csv files which are ingested into splunk everyday through a script, Source A has Site and CO which are not there in Source B and Source B has two fields (Geo_Name,_Geo_type) which are not there in Source A. I want to combine these based on a common field of many,I took ID as common field .

source A=ID,Views,Co,Camp,Creative,Insertion_Order,Place,Site,Co
sourceB=ID,Views,Camp,Creative,Insertion_Order,Place,Geo_Name,_Geo_type

but the second query when I ran by Co does nit give any results.What is the best way to join all these fields and do necessary stats command

index=main sourcetype=sftp (source=A OR source=B)
| stats dc(source) as count sum(Views) as views values("Co") as CO values(Geo_Name) as GEO by "Co"
|search count=2|fields- count

Re: how to combine two sources from same index with a common field

somesoni2 — Thu, 17 May 2018 15:16:32 GMT

I see two Co in sourceA. Is that a typo? If its a type and field Co is not common in both sources, your stats will only aggregate data from one source (sourceA) and dc(source) will be 1. Hence your filter after stats will not show any result.

When trying to join two sources, fields in by clause of stats should be the field or fields that are common in both sources and uniquely identify a record. So, if field Co alone can't do the above, you should use ID or combination of ID and Co.

Re: how to combine two sources from same index with a common field

vrmandadi — Thu, 17 May 2018 15:33:22 GMT

Ya Co is a field.How to use the combination of both ID and Co,as I want the results by Co ,instead of values which accumulates all of them

Re: how to combine two sources from same index with a common field

somesoni2 — Thu, 17 May 2018 15:45:29 GMT

Does field Co exists in both ? Your list of fields shows different but your comment says otherwise. You can only combine two sources if there are common fields (name and value wise). If field Co exists in both then your query should work fine. If it doesnt, you'd need to do stats twice, first to populate Co to all rows and then aggregate by field Co.

index=main sourcetype=sftp (source=A OR source=B) 
| stats dc(source) as count sum(Views) as Views values(Site) as Site values("Co") as Co values(Geo_Name) as Geo_Name by ID
|search count=2|fields- count
| stats sum(Views) as Views values(Site) as Site  values(Geo_Name) as GEO by Co

Re: how to combine two sources from same index with a common field

vrmandadi — Thu, 17 May 2018 16:00:04 GMT

I see what you are saying,it does not work without the common one if you do a by.

Thanks somesoni2