topic Re: Creating a comparison report in Splunk Dev

Creating a comparison report

mahbs — Mon, 08 Jan 2018 09:15:49 GMT

Hi,

I'm trying to create report, where I am extracting data from two different sources. This data being extracted from both sources share the same item number value. So the structure is something like this:

ITEM | src1 Field 1 | src1 Field 2 | src2 Field 1 | src2 Field 2
11111 0 0 0 0
12121 8 8 8 8
13222 7 7 7 7

Essentially, what I want to do is extract data from both sources for the relevant fields for a specific ITEM.

Can someone suggest what I can do to achieve this?

EDIT:
Apologies, I haven't been able to seperate the values for each fields. Basically, Each src field has only on Integer value.

Re: Creating a comparison report

mayurr98 — Mon, 08 Jan 2018 09:45:55 GMT

Hey

If you have different field name for both sources then try this:

source=source1 OR source=source2 ITEM=<item_number> | stats values(src1_field1) as "src1_field1"  values(src1_field2) as "src1_field2"  values(src2_field1) as "src2_field1" values(src2_field2) as "src2_field2" by ITEM

I hope this helps you!

Re: Creating a comparison report

mahbs — Mon, 08 Jan 2018 09:53:01 GMT

Hi @mayurr98,

Quick question, what does ITEM= do? I understand with the others, you're displaying the values of the fields according to ITEM.

Re: Creating a comparison report

mahbs — Mon, 08 Jan 2018 09:57:51 GMT

@mayurr98,

Apologies, I should have mentioned, the fields from the two different sources will have the same name.

Re: Creating a comparison report

mayurr98 — Tue, 29 Sep 2020 17:34:44 GMT

Try this then:

 index=<your_index> source=source1 ITEM=<item_number> field1=* field2=* | stats values(field1) as "src1_field1"  values(field2) as "src1_field2"  by ITEM | join ITEM [search index=<your_index> source=source2 ITEM=<item_number> field1=* field2=* | stats values(field1) as "src2_field1"  values(field2) as "src2_field2"  by ITEM]

so your output will be
ITEM src1_field1 src1_field2 src2_field1 src2_field2
1111 0 0 0 0
2222 1 1 1 1

Let me know if this works!

Re: Creating a comparison report

mahbs — Mon, 08 Jan 2018 10:10:32 GMT

Will do! Thanks for your speedy response @mayurrr98! 🙂

Re: Creating a comparison report

mayurr98 — Mon, 08 Jan 2018 16:57:33 GMT

Also for fast result you should write

index=export14 ITEM=$item_number$". sourcetype=csv | stats values(src1_field1) as field1 values(src1_field2) as field2 BY ITEM | rename ITEM as item_number | map search = "search index=export8 sourcetype=csv src2_item=$item_number$" | stats values(src2_field1) as field1 values(src2_field2) as field2 BY src2_item| rename src_item as item_number