topic Re: Result of subsearch field repeated instead of displaying unique values in Splunk Search

Result of subsearch field repeated instead of displaying unique values

mahbs — Thu, 18 Jan 2018 09:26:18 GMT

Hi,

I have a could of fields that contain multiple values, and I am trying to seperate them into sepereate records. The following query works 90%. The only issue is that the last field in the subsearch is not displaying the unique valeus, for example it may contain the value: 2,3 but it will only display 2. Every other field works fine in terms of displaying all the unique values per record. This is the current query I have:

index=index sourcetype=csv source=src1 host=host1 
 | stats count by ITEM field2 field3 field4 
 | rename field2 as F_2 field3 as F_3 field4 as F_4 
 | join ITEM 
     [ search index=index sourcetype=csv source=src2 host=host2 
     | stats count by SKU c_2 c_3 c_4 
     | rename SKU as ITEM | rename c_2 as C_2 c_3as C_3 c_4as C_4 ] 
 | eval DIFF1=F2-C_2 
 | eval DIFF2=F_3-C_3 
 | sort limit=0 ITEM 
 | table ITEM, F_2, F_3, F_4, c_2, c_3, c_4, DIFF1, DIFF2

Can someone suggest what I can do to fix the problem?

Thanks

Re: Result of subsearch field repeated instead of displaying unique values

493669 — Thu, 18 Jan 2018 09:34:13 GMT

could you specify in detail which subsearch is not displaying unique values and what is the output you are getting and what is expected?

Re: Result of subsearch field repeated instead of displaying unique values

mahbs — Tue, 29 Sep 2020 17:40:48 GMT

Hi,
yep, the sub-search is where source=src2. Essentially C_4has multiple values, and im trying to seperate these values into seperate records which is working for the most part, but c_4 for some reason isn't displaying all the multiple values, it's just repeating, where as all the other fields are displaying the multiple data.

This is the output I want:
ITEM: 1234 F_2=22 F_3=21 F_4=23
ITEM: 1234 C_2=1 C_3=2 C_4=2

I hope that makes sense

Re: Result of subsearch field repeated instead of displaying unique values

mahbs — Thu, 18 Jan 2018 09:42:09 GMT

I'm not sure if the join is working properly

Re: Result of subsearch field repeated instead of displaying unique values

493669 — Tue, 29 Sep 2020 17:44:43 GMT

could you please run only subsearch i.e.

search index=index sourcetype=csv source=src2 host=host2
| stats count by SKU c_2 c_3 c_4
| rename SKU as ITEM | rename c_2 as C_2 c_3 as C_3 c_4 as C_4

what output you are getting and what is expeced...

Re: Result of subsearch field repeated instead of displaying unique values

mahbs — Thu, 18 Jan 2018 09:56:04 GMT

I'm getting a list of all the data for the fields I have specified in the query

Re: Result of subsearch field repeated instead of displaying unique values

mahbs — Thu, 18 Jan 2018 09:56:59 GMT

There's also count column at the end

Re: Result of subsearch field repeated instead of displaying unique values

493669 — Thu, 18 Jan 2018 10:07:17 GMT

do you require count field else you should remove it using |fields - count

Re: Result of subsearch field repeated instead of displaying unique values

mahbs — Thu, 18 Jan 2018 10:16:09 GMT

yeah but that's not the problem at the moment

Re: Result of subsearch field repeated instead of displaying unique values

493669 — Thu, 18 Jan 2018 10:21:36 GMT

also why you are renaming same field in subsearch....else query looks fine ..if you could share dummy data for source=src2 and src1 then I can try

Re: Result of subsearch field repeated instead of displaying unique values

cmerriman — Thu, 18 Jan 2018 14:07:52 GMT

if your fields are a multivalued list, splunk only brings back the first value. try adding |nomv C_4 at the end of your subsearch to convert it to a single value. you can also try having |mvcombine delim="," C_4 before the nomv to add a comma between the values.

http://docs.splunk.com/Documentation/Splunk/7.0.1/SearchReference/Mvcombine
http://docs.splunk.com/Documentation/Splunk/7.0.1/SearchReference/Nomv