How to create a table based on a common field name from two sources?

[svemurilv]

HI Splunks,
I have two Splunk sources: source=source1 and source=source2. i just want to compare two source's data with one of the common Field names "ReportRequestInstanceID" and create table with the time stamp.

source1

"2017-01-09 12:01:00" ReportRequestInstanceID="3211552", CreatedOn="2017-01-09 12:01:00.387", ReportRequestID="172837", NumberOfReportTypes="1", Title="Accordant_Amtrak", QueueNumber="2", TimeInterval="Last Effective Hour", NumberOfFilteredNetworks="0", NumberOfFilteredAdvertisers="1", NumberOfFilteredAngencies="1", NumberOfFilteredCampaigns="1", NumberOfFilteredReportingProjects="0", HasEmail="1", HasFtps="1", NumberOfDaysInWeekScheduled="7",

source -2

2017-01-09 12:02:46" ID="6157727", ReportRequestInstanceID="3114793", DownloadTime="2017-01-09 12:02:46.567", ClientIP="64.156.167.132", DownloadMode="40", StartTime="2017-01-09 12:02:46.567"

i just want to create table for the source1.ReportRequestInstanceID | source2.ReportRequestInstanceID | source1.CreatedOn source1.ReportRequestID |source2.DownloadTime | source2.DownloadMode

Please Help me.

Thanks

[DalJeanis · Accepted solution]

Try something like this -

source=source1 | fields ReportRequestInstanceID CreatedOn | join ReportRequestInstanceID type=left maxrows=0 [search source=source2 | eval Source2ReportRequestInstanceID = ReportRequestInstanceID | fields ReportRequestInstanceID Source2ReportRequestInstanceID DownloadTime DownloadMode] | table ReportRequestInstanceID Source2ReportRequestInstanceID CreatedOn DownloadTime DownloadMode

This left join should produce one line for each time a report has been downloaded in source 2, or a single line for each report in source 1 that has never yet been downloaded. It will not produce any results for items that show as downloaded in source2, but which do not exist in source1.

[svemurilv]

Thanks @ DalJeanis Its working.

[svemurilv]

Thanks all

[DalJeanis]

Awesome! happy to be of service.

[cmerriman]

what about something like this? It should give you every unique value for each field by the ReportRequestInstanceID

source=source1 OR source=source2
|stats values(DownloadTime) as DownloadTime values(DownloadMode) as DownloadMode values(CreatedOn) as CreatedOn values(ReportRequestID) as ReportRequestID by ReportRequestInstanceID 

[svemurilv]

Sorry to say its not displaying the source1 values in this case, its displaying only source2 values

[gokadroid]

I think since the field should be autoextracted and is same/common in both sources can you try this below which should work straight away without any issues:

(source=source1 OR source=source2)
| table ReportRequestInstanceID, CreatedOn, ReportRequestID, DownloadTime, DownloadMode

If the index and sourcetype is same, you might wanna add those filters too something like

index=yourIndex sourcetype=yourSourceType (source=source1 OR source=source2)
| table ReportRequestInstanceID, CreatedOn, ReportRequestID, DownloadTime, DownloadMode

[gokadroid]

Since the ReportRequestInstanceID is same, displaying it twice might be redundant and this query above will automatically return the data for the ids which match in both source1 and source2. You don't need a specific matching condition to match between source1.ReportRequestInstanceID and source2.ReportRequestInstanceID

[svemurilv]

I need to compare the "ReportRequestInstanceID" from both sources, if ID matches in both sources then tabular few filed in both the sources and generate the report, both ReportRequestInstanceID di should display in the table.