How do I group one field's values by another field?

[HattrickNZ]

I have a search ...|table measInfoId that gives output in 1 column with the values
e.g.

measInfoId
1x
2x
3x
...

I have the same search, but slightly different different ...| table c* gives output with the values in many columns
e.g.

c1x c2x c3x ...

What I am trying to to is get something like this (group the c1x's by the 1x's):

measInfoId  c*
 1x         c1x
 2x         c2x
            c3x
 3x         c4x
            c5x
...

I think this has something to do with the way the data is imported (the c is prefixed in front of the values c1x), which is why I am getting the difference. But given these conditions, can I achieve what I want? I may have to add more details to get this question answered as I try and solve this answer.

[HattrickNZ]

I found a diffeernt way of doing this here
https://answers.splunk.com/answers/369391/how-do-i-use-my-regular-expression-to-extract-valu.html

[rakeshh123]

Hi ,HattrickNZ

                   May be you can solve it by using transaction.....what transaction will do is group  events based on fields specified .....try this query.....from u r information u meansInfold has to be grouped.     meansInfold :1907443286
                                                                                                                                    1907443286
                                                                                                                                     1907443286.........

index = |transaction meansInfold maxspan=30s maxpause=5s| table meansInfold ,c|eventstats list(meansInfold ) by c*|fields - meansInfold........

[HattrickNZ]

tks but |transaction meansInfold maxspan=30s maxpause=5s this returned nothing.

and table meansInfold ,c does not work as the field name is c followed by some numbers (c* i.e. c1x c1y c 1z ...etc)

[rakeshh123]

Hi , HattrickNZ

                   maxspan=30 means .....The first and last events in the transaction should be no more than thirty seconds apart....  see u r events and change it accordingly, maxpause=5s .... each event should not be longer than five seconds apart....Leave them  if it is irrevalant to u r data ......

table meansInfold ,c*---sorry i forgot *

what i see from ur answers is u want meansfold unique .......and you want to list c* by meansfold........try this query

index = |transaction meansInfold | table meansInfold ,c*|eventstats list(c* ) by meansfold|table meansfold,list(c*)....
remove any irrevvalent fields using fields - field uwant to remove
see if its working...........reply me if u have any problem .....

[HattrickNZ]

tks, don't think transaction is required for my data in this case, but stiil have no joy with what you mention.

1

...| table meansInfold ,c* | gives me the following output in the stats tab. There is some values in some of the cells but they are generally blank:

meansInfold c1907466416 c1907466417 c1907466418 c1907466419 .... c1907480795    c1907480796

2

| table meansInfold ,c* | eventstats list(c* ) by meansfold | gives me:

the same as 1 above.

3

| table meansInfold ,c* | eventstats list(c* ) by meansfold | table meansfold,list(c*) gives me:

No results found.

[rakeshh123]

hi HattrickNZ,
try this query.........
index=a sourcetype=b | eval output = toString(c1x) + ";" + toString(c2x) + ";" + toString(c3x) + ";" + toString(c4x).... |makemv delim=";"output |mvexpand output|table output ,meansfold......
reply me if its working ........

[HattrickNZ]

... | eval output = toString(c1907466966) + ";" + toString(c1907466967) + ";" + toString(c1907466968) + ";" + toString(c1907466969) | makemv delim";" | mvexpand output| table output ,meansfold

this gives me the following (meansfold column is empty, output column is like the values of c*)

output  meansfold
Null;Null;Null;Null  
...
0;0;0;0  
0;0;0;0  
0;0;0;0  
57;8.032;66.279;13   
...

[rakeshh123]

hi HattrickNZ,
please see my query correctly ,,,it is | eval output = makemv delim=";"output |mvexpand output|table output ,meansfold...... ......
also if you are not getting meansfold you can write a separate query for meansfold and join both the queries.......by using join .....

[HattrickNZ]

this gives an error:
...| eval output = makemv delim=";"output |mvexpand output|table output ,measInfoId

I think I have got this right now?
...| eval output = toString(c1907466966) + ";" + toString(c1907466967) + ";" + toString(c1907466968) + ";" + toString(c1907466969) | makemv delim=";" output | mvexpand output | table output ,measInfoId

but I am still getting null though.

output  measInfoId
1   Null    1907443286
2   Null    1907443286
3   Null    1907443286
4   Null    1907443286
5   Null    1907425342
6   Null    1907425342
7   Null    1907425342
8   Null    1907425342
9   Null    1907425342
10  Null    1907425342

Because there is so many c* fields is there a better way to write this to get them all? I presume they are meant to be showing up instead of null?

[somesoni2]

Try something like this

Your base search  | table measInfoId c*  | untable meansInfold c_meansInfold temp | fields - temp

[HattrickNZ]

that gives the following: meansInfold is an empty column, and measInfoId appears randomly in this column c_meansInfold

    meansInfold c_meansInfold
1       c1907473350
2       c1907473351
3       c1907473352
4       measInfoId
5       c1907466983
6       c1907466984
...
93      c1907466996
94      measInfoId
95      c1907466983
96      c1907466984
97      c1907466985
98      c1907466986
99      c1907466987
100     c1907466988

[somesoni2]

Ok.. can you provide the full search for getting both the result sets , means one with "...|table measInfoId" and other with "...| table c*".

[HattrickNZ]

not sure it will make a difference

1 index=core host="A" measObjLdn=* measInfoId=* duration=* userLabel=* sourcetype=M2 | table measInfoId

2 index=core host="A" measObjLdn=* measInfoId=* duration=* userLabel=* sourcetype=M2 | table c*
and as I said above the format of the outputs is different, 1 has 1 column and 2 has many columns

[somesoni2]

So, now does your data looks like this Or output of below search looks like this??

index=core host="A" measObjLdn=* measInfoId=* duration=* userLabel=* sourcetype=M2 | table measInfoId c*


  measInfoId    c1x c2x c3x     c4x c5x c6x     c7x c8x
    1x           Y    Y                     
    2x                          Y     Y     Y           
    3x                                               Y       Y      Y

[HattrickNZ]

ye ...| table measInfoId c* looks like you say. but there is not a Y value for every c* just a few and they concentrated close together, but that could just be the data.

[somesoni2]

And you want o list all the c* fields for which a measInfoId has values??

[HattrickNZ]

yes group all c* values by measInfoId

[mtranchita]

if I follow what your doing, why not use list with the stats or chart command instead of table?
you can then do something like
your search | stats list(measInfoId) by c*
or
your search | chart list(measInfoId) by c*

[HattrickNZ]

tks but that presents the same problem
... | stats list(measInfoId) gives output in 1 column with the values
... | stats list(c*) gives output with the values in many columns headers (e.g. list(c1x) list(c2x)...)

the problem I think is to do with the c* fields, they should show up as the measInfoId does but they don't.

I have tried transpose which which puts the kpi values(c*) in the left most column but I get other columns that I don't really want.
...| table c* | transpose | rename column as kpis

then I try to see the values in measInfoId after doing the transpose but this says no results found
| table c* | transpose | rename column as kpis | table measInfoId

[marina_rovira]

Can you explain or give some more information about your search?

I've done something like this but I resolved it using this search

your search | chart list(measInfoId) by c*

But if doesn't work for you, I don't understand the relation between these two fields or the real search you want to do.

Thanks!