topic Re: How to create a table based on a common field name from two sources? in Splunk Search

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

svemurilv — Mon, 09 Jan 2017 17:16:16 GMT

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

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

gokadroid — Mon, 09 Jan 2017 18:34:30 GMT

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

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

svemurilv — Mon, 09 Jan 2017 20:06:35 GMT

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.

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

cmerriman — Mon, 09 Jan 2017 20:10:48 GMT

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

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

gokadroid — Mon, 09 Jan 2017 20:14:29 GMT

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

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

svemurilv — Mon, 09 Jan 2017 20:33:47 GMT

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

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

DalJeanis — Mon, 09 Jan 2017 20:34:45 GMT

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.

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

svemurilv — Mon, 09 Jan 2017 21:34:00 GMT

Thanks @ DalJeanis Its working.

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

svemurilv — Mon, 09 Jan 2017 21:40:30 GMT

Thanks all

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

DalJeanis — Tue, 10 Jan 2017 15:56:39 GMT

Awesome! happy to be of service.