Splunk Search

Combine 2 cols in one

MaximeM
Explorer

Hi there. I'm trying to get the number of some operations (each operation corresponding to a number (field "tag")) that take a certain time to be executed.
Here is my command :

host="yvas7300" sourcetype="accesslog" type_op="result"  etime<"0.010" | stats count(tag) BY tag | fillnull count(tag) | rename count(tag) AS nb_tr0 
| appendcols [search host="yvas7300" sourcetype="accesslog" type_op="result" etime>"0.010" AND etime<"0.100" | stats count(tag) by  tag | rename count(tag) AS nb_tr1, tag AS tag2 | fillnull nb_tr1 | fields tag2 nb_tr1]

And I get as a result something like :

tag     nb_tr0      tag2      nb_tr1

1        10         1         11
2        20         3         22
3        30         5         55
4        40

And I would like to get that :

tag     nb_tr0      nb_tr1

1       10          11
2       20
3       30          22
4       40
5                   55

So it is like combining the two fields tag and tag2. Can someone help me to find a solution ?

P.S. : Sorry for bad grammar, English is not my first language.

0 Karma
1 Solution

MaximeM
Explorer

I have found a solution. I need to use "append" instead of "appendcols". So with some "stats" operation, I can get the result I need.
Here is my solution :

host="yvas7300" sourcetype="accesslog" type_op="result"  etime<"0.010" | stats count(tag) BY tag | rename count(tag) AS nb_tr0 | fillnull nb_tr0
| append [search host="yvas7300" sourcetype="accesslog" type_op="result" etime>"0.010" AND etime<"0.100" 
    | stats count(tag) by tag | rename count(tag) AS nb_tr1 | fillnull nb_tr1 | fields tag nb_tr1]
| stats sum(nb_tr0) sum(nb_tr1) by tag

(The operation sum is certainly not the most intuitive operation to use, but it actually works.)

View solution in original post

0 Karma

MaximeM
Explorer

I have found a solution. I need to use "append" instead of "appendcols". So with some "stats" operation, I can get the result I need.
Here is my solution :

host="yvas7300" sourcetype="accesslog" type_op="result"  etime<"0.010" | stats count(tag) BY tag | rename count(tag) AS nb_tr0 | fillnull nb_tr0
| append [search host="yvas7300" sourcetype="accesslog" type_op="result" etime>"0.010" AND etime<"0.100" 
    | stats count(tag) by tag | rename count(tag) AS nb_tr1 | fillnull nb_tr1 | fields tag nb_tr1]
| stats sum(nb_tr0) sum(nb_tr1) by tag

(The operation sum is certainly not the most intuitive operation to use, but it actually works.)

0 Karma

bmacias84
Champion

This sounds like good case for join. I about your search but it might look something like this.


host="yvas7300" sourcetype="accesslog" type_op="result" etime<"0.010" | stats count(tag) as nb_tr0 by tag | fillnull nb_tr0 | fields tag,nb_tr0 | join tag[search host="yvas7300" sourcetype="accesslog" type_op="result" etime>"0.010" AND etime<"0.100" | stats count(tag) as nb_tr1 by tag | fillnull n_tr1 | fields tag,nb_tr1]|table tag,nb_tr0,nb_tr1

Additional reading:

Using join tag on your subsearch tell Splunk to join your two search on the common field of tag. You may need to experiment with this to get the exact results you want.

Hope this helps or gets you started. Don't forget to thumbs up or accept answers that help.

Cheers,

0 Karma

MaximeM
Explorer

Thanks for your answer. I tried to use "join tag" but it can't work everytime in this case. For example, If you get an operation X in the subsearch that is not present in the search (like the operation 5 in my post), it will not be displayed in the results.

0 Karma

dmaislin_splunk
Splunk Employee
Splunk Employee

Use the Splunk search command called strcat? Would that work for you?

0 Karma

dmaislin_splunk
Splunk Employee
Splunk Employee

Sorry, I misunderstood the question.

0 Karma

MaximeM
Explorer

Thanks for your answer, but I can't figure how the strcat command can help there. I need to add the "tag2" values in the field "tag1", and not to concatenate them together.

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