Splunk Search

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

vrmandadi
Builder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

vrmandadi
Builder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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.

0 Karma

vrmandadi
Builder

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

vrmandadi
Builder

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

Thanks somesoni2

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...