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
If ID is the only common field, then grouping by other field would not give you any results. Whats your requirement here?
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
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.
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
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
I see what you are saying,it does not work without the common one if you do a by.
Thanks somesoni2