Selective display of values after transpose

[kenbaugher]

This is similar to a question I asked earlier today that was quickly answered, however I'm not sure if I can apply that solution to this due to the transpose.  Not sure how to reference the data correctly for that.

We have data with 10-15 fields in it and we are doing a transpose like the below.  What we are looking to accomplish is to display only the rows where the values are the same, or alternatively where they are different.

index=idx1 source="src1"
| table field1 field2 field3 field4 field5 field6 field7 field8 field9 field10
| transpose header_field=field1

column    sys1    sys2
field2       a            b
field3       10         10
field4       a           a
field5       10         20
field6       c           c
field7       20         20
field8       a           d
field9      10         10
field10    20        10

[PickleRick]

And I will give you another approach.

| makeresults format=csv data="field1,field2,field3,field4,fiel5,field6,field7,field8,field9,field10,field11
sys1,2,a,v,4,65,2,dd,2,f,44
sys2,2,b,v,4,55,2,dd,2,f,44"
| appendpipe 
   [ | stats dc(*) as * 
     | eval field1="count"]
| transpose 0 header_field=field1

Now you can decide whether to include only those rows where you have one or two different values.

You can play with this to account for more rows and such.

And I'm assuming your sys1,sys2 names can be dynamic. Otherwise your solution would be as simple as

| where sys1=sys2

(or NOT)

[ITWhisperer]

The transpose doesn't make a huge difference (as @yuanliu suggested), the solution is similar to your previous question, just with a change of field name.

index=idx1 source="src1"
| table field1 field2 field3 field4 field5 field6 field7 field8 field9 field10
| transpose 0 header_field=field1
| untable column sysname value_info
| eventstats dc(value_info) as distinct_values by column
| where distinct_values > 1
| xyseries column sysname value_info

[yuanliu · Accepted solution]

I am confused as to how using transpose can changes anything.  The solution should be the same no matter how you obtain the table.  The following will pick up rows with equal values:

index=idx1 source="src1"
| table field1 field2 field3 field4 field5 field6 field7 field8 field9 field10
| transpose header_field=field1
| foreach sys*
    [eval _row_values = mvappend(_row_values, <<FIELD>>)]
| where mvcount(mvdedup(_row_values)) == 1

Using your sample data, the result is

column	sys1	sys2
field3	10	10
field4	a	a
field6	c	c
field7	20	20
field9	10	10

Here is an emulation for you to play with and compare with real data

| makeresults format=csv data="column,    sys1,    sys2
field2,       a,            b
field3,      10,         10
field4,       a,           a
field5,       10,         20
field6,       c,           c
field7,       20,         20
field8,       a,           d
field9,      10,         10
field10,    20,        10"
``` the above emulates
index=idx1 source="src1"
| table field1 field2 field3 field4 field5 field6 field7 field8 field9 field10
| transpose header_field=field1
```

[kenbaugher]

Thank you the below seems to work, however, for some reason the search only shows sys1 thru sys5.   It does not matter which how many rows I put in the data, it won't show more than 5.

| makeresults format=csv data="field1, field2, field3, field4, field4, field5, field6, field7, field8, field9
sys1,10,a,10,c,20,a,10,20
sys2,20,b,10,c,20,a,10,20
sys3,10,c,10,c,20,a,10,20
sys4,30,d,10,c,20,a,10,20
sys5,40,a,10,c,20,a,10,20
sys6,20,c,10,c,20,a,10,20
sys7,50,d,10,c,20,a,10,20
sys8,10,e,10,c,20,a,10,20"
| table field1 field2 field3 field4 field5 field6 field7 field8 field9
| transpose header_field=field1| foreach *
[eval _row_values = mvappend(_row_values, <<FIELD>>)]
| where mvcount(mvdedup(_row_values)) != 1

[ITWhisperer]

The transpose command has an int argument which defaults to 5 - this is why I have used zero (0) in my suggested solution.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transpose