topic Re: How do I group one field's values by another field? in Splunk Search

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

HattrickNZ — Wed, 03 Feb 2016 23:12:43 GMT

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.

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

mtranchita — Wed, 03 Feb 2016 23:43:37 GMT

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*

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

HattrickNZ — Tue, 29 Sep 2020 08:39:32 GMT

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

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

marina_rovira — Thu, 04 Feb 2016 09:06:30 GMT

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!

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

HattrickNZ — Thu, 04 Feb 2016 19:26:46 GMT

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

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

somesoni2 — Thu, 04 Feb 2016 19:44:46 GMT

Try something like this

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

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

HattrickNZ — Thu, 04 Feb 2016 20:42:51 GMT

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

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

somesoni2 — Thu, 04 Feb 2016 20:50:16 GMT

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

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

HattrickNZ — Thu, 04 Feb 2016 21:42:15 GMT

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

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

somesoni2 — Thu, 04 Feb 2016 22:05:45 GMT

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

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

HattrickNZ — Thu, 04 Feb 2016 22:41:21 GMT

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.

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

somesoni2 — Thu, 04 Feb 2016 22:53:28 GMT

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

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

HattrickNZ — Thu, 04 Feb 2016 23:34:26 GMT

yes group all c* values by measInfoId

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

rakeshh123 — Mon, 08 Feb 2016 05:42:27 GMT

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

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

HattrickNZ — Wed, 10 Feb 2016 20:26:37 GMT

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)

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

rakeshh123 — Tue, 29 Sep 2020 08:47:59 GMT

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

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

HattrickNZ — Sun, 14 Feb 2016 21:13:38 GMT

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

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

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

the same as 1 above.

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

No results found.

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

rakeshh123 — Mon, 15 Feb 2016 09:31:04 GMT

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

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

HattrickNZ — Mon, 15 Feb 2016 19:59:20 GMT

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

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

rakeshh123 — Tue, 16 Feb 2016 04:24:28 GMT

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