topic Re: Selective display of values after transpose in Splunk Search

Selective display of values after transpose

kenbaugher — Thu, 07 Nov 2024 22:14:56 GMT

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

Re: Selective display of values after transpose

yuanliu — Fri, 08 Nov 2024 05:36:23 GMT

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 ```

Re: Selective display of values after transpose

ITWhisperer — Fri, 08 Nov 2024 09:53:24 GMT

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.

Re: Selective display of values after transpose

kenbaugher — Fri, 08 Nov 2024 13:25:30 GMT

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

Re: Selective display of values after transpose

ITWhisperer — Fri, 08 Nov 2024 16:02:32 GMT

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

Re: Selective display of values after transpose

PickleRick — Fri, 08 Nov 2024 19:01:38 GMT

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)