Splunk Search

how to display only those rows with a particular value in a particular value using |table command

sh254087
Communicator

I am applying few conditions and logic to come up with values for different fields. I'm then displaying them using teh table command, like -
| table field1 field2 field3 etc

I now want to display this table with a condition like the table should display only those rows where a field has a particular value. Ex - Display only those rows where field2="testvaluexyz". something like - SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1 WHERE FIELD2="testvaluexyz"

I'm trying with the below command after table command and getting any result.
|fields - field2| where field2 != "testvaluexyz"

I can guess this may not be the right way. Can someone please help achieve this?

0 Karma
1 Solution

sh254087
Communicator

Just after posting this I got this resolved. Just came across an other question on the forum where someone had made a comparison(not similar to my problem but it helped) using ==, the opposite of how I was trying. Instead of removing fields which is having values not matching with my value, this would display only those rows with the values which would match my value. Somehow I did not think this way.

So the solution is (as simple as)-
| table field1 field2 field3
| where field2 == "testvaluexyz"

I probably did not know how all I could use the where condition! Lesson learned. 🙂

View solution in original post

0 Karma

sh254087
Communicator

Just after posting this I got this resolved. Just came across an other question on the forum where someone had made a comparison(not similar to my problem but it helped) using ==, the opposite of how I was trying. Instead of removing fields which is having values not matching with my value, this would display only those rows with the values which would match my value. Somehow I did not think this way.

So the solution is (as simple as)-
| table field1 field2 field3
| where field2 == "testvaluexyz"

I probably did not know how all I could use the where condition! Lesson learned. 🙂

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi sh254087,
If you use the command | fields - field2, this field isn't more available for searches, so the following where command is always wrong!
you have to insert the search condition before the | fields - field2 command.

Anyway it's a best practice to put all the conditions as left as you can and not after table command.
So try something like this:

index=your_index  field2 != "testvaluexyz"
| table field1 field2 field3

Bye.
Giuseppe

sh254087
Communicator

@cusello this would display the field with the non-matching values. the other way of what I needed. Just checked this as well with a small change -

|where field2 == "testvaluexyz"
|table field1 field2 field3

This worked fine, just the way it did when I tried | where after | table command.

Thank you too. Cheers. 🙂

0 Karma

HeinzWaescher
Motivator

In your example you are removing field2 before using the filter

Have you tried out:

yourbase search
| search field2="testvaluexyz"
| table field1 field2 field3

In general you should filter as soon as possible. So if possible, filter it directly in the base search.

sh254087
Communicator

@HeinzWaescher This as well worked fine. Thank you. 🙂

0 Karma

kmaron
Motivator

the fields command will remove field2 so your where clause has nothing to compare so just flip the order

| table field1 field2 field3 etc
| where field2 != "testvaluexyz"
| fields - field2 
0 Karma

sh254087
Communicator

Tried this. This is removing the field2 completely.

0 Karma

sh254087
Communicator

@kmaron Thank you for the response. 🙂

0 Karma

kmaron
Motivator

That's what the fields - field2 command does. I assumed you wanted to remove it

0 Karma

sh254087
Communicator

I wanted to remove the non matching entries alone and not the complete field.

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