Splunk Search

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

HattrickNZ
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

HattrickNZ
Motivator
0 Karma

rakeshh123
Path Finder

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

0 Karma

HattrickNZ
Motivator

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)

0 Karma

rakeshh123
Path Finder

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

0 Karma

HattrickNZ
Motivator

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.

0 Karma

rakeshh123
Path Finder

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

0 Karma

HattrickNZ
Motivator

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

rakeshh123
Path Finder

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

0 Karma

HattrickNZ
Motivator

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?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try something like this

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

HattrickNZ
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

somesoni2
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

HattrickNZ
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

somesoni2
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

HattrickNZ
Motivator

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

HattrickNZ
Motivator

yes group all c* values by measInfoId

0 Karma

mtranchita
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

HattrickNZ
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

marina_rovira
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...