I have 2 sourcetype sourcetype="source1" and sourcetype="source2"
This is how sample data looks:
source1:
CID,Cname,CData
Source 2:
CID,key,FName,LName
Here values of CID of source 1 and key of source 2 will be same.Even though CID will be present in source 2 but it will be having different value.
I need to write query to when CID(source 1) = key (source 2) then fetch all other fields from source 1 and source 2 display in table .
Any suggestions would be appreciated.
sourcetype="source1" OR sourcetype="source2"
| eval key=coalesce(key,CID)
| stats values(*) as * by key
Thank you for the response.
I have tried the above query.But it doesn't seems to be working as coalesce is not matching if the values of CID(source1) and Key(source 2 ) same .Also its displaying only the fields of source 1.Fields of source 2 are not being fetched.
What do you think coalesce is doing?
coalesce is simply creating a field called key using the value in field key (if it exists as in source 2) or CID (if it doesn't exist as in source 1). This is similar to
| eval key=if(isnotnull(key), key, CID)
Having got a value in key, the stats is matching the events from both source 1 and source 2 by the value in key (where key = CID for source 1). Is this not what you wanted?
Thank you for the response.
Yes, that is what I am looking for .
But the catch here is CID is present in both source1 and source 2.
source1:
CID, Cname, CData
Source 2:
CID, key, FName, LName
But i need to match CID of source 1 and key of source 2.Since CID field name is common in both the sources its not fetching properly as combined results .Its giving 2 rows of data for each sources having fields as null for other source.
Hope I am making it clear.
Please suggest .
sourcetype="source1" OR sourcetype="source2"
| eval common=if(sourcetype="source2",key,CID)
| stats values(*) as * by common
Thank you for the response.
it is fetching only the fields of source 1 and not combining the fields of both source 1 and source 2:(
In below CID will be taken from source 1? my use case is to match key of source 2 and CID of source 1But CID present in both the sources.
| eval common=if(sourcetype="source2",key,CID)
The statement is putting the value from the key field in the common field if the sourcetype is "source2" and the value of the CID field in the common field if the sourcetype is not "source2". How is this not what you want?
Thank you for your patience and response.
Just briefing here with sample data.Please suggest.
sourcetype= source1
"CID": 123
"Cname": John
"CData": XYZ
sourcetype= source2
"CID": 285
"Key": 123,
"FName": Ken
"LName": Dan
Here CID(source1) = Key(source 2) so I need result in below format:
"Cname": John
"CData": XYZ
"CID": 285
"FName": Ken
"LName": Dan
But with the below query I am seeing the result like below:
sourcetype="source1" OR sourcetype="source2"
| eval common=if(sourcetype="source2",key,CID)
| stats values(*) as * by common
"common": 123
"CID": 123
"Cname": John
"CData": XYZ
There are no fields of source 2(FName,LName) is fetched here
| makeresults
| fields - _time
| eval sourcetype= "source1", "CID"= 123,"Cname"="John","CData"= "XYZ"
| append
[makeresults
| fields - _time
| eval sourcetype= "source2","CID"= 285,"Key"= 123,"FName"= "Ken","LName"= "Dan"]
``` the lines above just set up your sample data ```
| eval common=if(sourcetype="source2",Key,CID)
| eval CID=if(sourcetype="source2",CID,null())
| stats values(*) as * by common
| fields - common sourcetype Key
Thank you for the response.
Apologies for the multiple threads.
I did try below both the queries you have suggested and its fetching only the fields of source1 .(i.e CID, Cname ,CData). Source2 fields are not being combined and fetched.
I am not sure what am missing here .Please suggest.
sourcetype="source1" OR sourcetype="source2"
| eval common=if(sourcetype="source2",key,CID)
| stats values(*) as * by common
sourcetype="source1" OR sourcetype="source2"
| eval common=if(sourcetype="source2",Key,CID)
| eval CID=if(sourcetype="source2",CID,null())
| stats values(*) as * by common
| fields - common sourcetype Key
You should only need the lower part
sourcetype="source1" OR sourcetype="source2"
``` Set common to Key if sourcetype is source2 otherwise use CID (or source1 events) ```
| eval common=if(sourcetype="source2",Key,CID)
``` Preserve CID for sourcetype source2 otherwise clear the field (as you don't appear to be interested in this value) ```
| eval CID=if(sourcetype="source2",CID,null())
``` Gather all the fields from both sourcetypes where Key from source2 = CID from source1 ```
| stats values(*) as * by common
| fields - common sourcetype Key
If you are still not getting a match, this seems to suggest that CID in source1 has no matches for Key in source2
I will try this .Thank you
Something like
source IN (source1, source2)
| stats values(Cname) as Cname, values(CData) as CData, values(key) as key, values(FName) as FName, values(LName) as LName values(source) by CID
| where mvcount('values(source)') > 1
Hope this helps.
Thank you for the response.
I have tried this and not working.
As i need to match CID of source 1 and Key of source 2 are same then fetch all the fields of source1 and source 2.One thing to note here though CID is present in source 2 it will be having different value.so need to match if(CID(source1) equals to key(source2) then fetch all fields of source1 and source2). please suggest.