Splunk Search
Highlighted

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

Motivator

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.

Tags (3)
0 Karma
Highlighted

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

Communicator

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*

0 Karma
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

Contributor

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!

0 Karma
Highlighted

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

Motivator

I will try to explain by example
If i do ...| chart list(measInfoId) by c* this says no results found

If I do ...| chart list(measInfoId) by c1907466990,just looking at 1 specific c* value, I get the below:

c1907466990 list(measInfoId)
1   0           1907425342
                1907425342
                1907425342
                1907425342
                1907425342
2   1           1907425342
                1907425342
                1907425342
                1907425342
    ...
98  104         1907425342
99  105         1907425342
                1907425342
                        1907425342
100 106         1907425342

If i do ...| chart list(measInfoId) by measObj* this says no results found, the wild card does not seem to work, it seems I have to specify the whole name. UI will see if this might be a rights issue.

If I do ...| chart list(measInfoId) by measObjLdn,specify the whole measObjLdn name, I get the below, which is what i am originally trying to achieve:

    measObjLdn  list(measInfoId)
1   object1     1907425301
                1907425280
                1907425335
                1907425301
                1907425280
                1907425335
                1907425301
2   object2     1907443286
                1907443286
                1907443286
                1907443286
                1907443286
...
100 object100   1907425341
                1907425341
                1907425341
                1907425341
0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

Try something like this

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

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

Motivator

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
0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Motivator

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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
0 Karma