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

[sh254087]

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?

[sh254087 · Accepted solution]

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

[gcusello]

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]

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

[HeinzWaescher]

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]

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

[kmaron]

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 

[sh254087]

Tried this. This is removing the field2 completely.

[sh254087]

@kmaron Thank you for the response. 🙂

[kmaron]

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

[sh254087]

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