topic Re: How to use where clause with table containing fields within quotes in Getting Data In

How to use where clause with table containing fields within quotes

nits — Fri, 02 Oct 2020 20:58:07 GMT

I have a query which looks like:
index=test "TestRequest" | dedup _time | rex field=_raw "Price\":(?<price>.*?)," | rex field=_raw REQUEST-ID=(?<REQID>.*?)\s | rex field=_raw "Amount\":(?<amount>.*?)}," | rex field=_raw "ItemId\":\"(?<itemId>.*?)\"}" | eval discount=round(exact(price-amount),2) , percent=(discount/price)*100
, time=strftime(_time, "%m-%d-%y %H:%M:%S") | stats list(time) as Time list(itemId) as "Item" list(REQID) as X-REQUEST-ID list(price) as "Original Price" list(amount) as "Test Price" list(discount) as "Dollar Discount" list(percent) as "Percent Override" by _time
[search index=test "UserId=" | rex field=_raw UserId=(?<userId>.*?)# | dedup userId | rex field=_raw X-REQUEST-ID=(?<REQID>.*?)\s | stats list(userId) as "User ID" list(REQID) as X-REQUEST-ID by _time]
| where "Dollar Discount">=500.00 OR "Percent Override">=50.00
| table "User ID" Item "Original Price" "Dollar Discount" "Test Price" "Percent Override" Time

This query throws error as mismatch type for "Dollar Discount">=500.00 OR "Percent Override">=50.00
Since my fields in the table have "" e.g. "Dollar Discount" or "Percent Override", it doesn't work. If i replace these fields names without quotes as Dollar_Discount and Percent_Override, it works fine.
how to use a table field with name listed in quotes in where clause?

Re: How to use where clause with table containing fields within quotes

ITWhisperer — Fri, 02 Oct 2020 21:28:15 GMT

Try using single quotes for fields in where clauses

index=test "TestRequest" | dedup _time | rex field=_raw "Price\":(?<price>.*?)," | rex field=_raw REQUEST-ID=(?<REQID>.*?)\s | rex field=_raw "Amount\":(?<amount>.*?)}," | rex field=_raw "ItemId\":\"(?<itemId>.*?)\"}" | eval discount=round(exact(price-amount),2) , percent=(discount/price)*100 , time=strftime(_time, "%m-%d-%y %H:%M:%S") | stats list(time) as Time list(itemId) as "Item" list(REQID) as X-REQUEST-ID list(price) as "Original Price" list(amount) as "Test Price" list(discount) as "Dollar Discount" list(percent) as "Percent Override" by _time [search index=test "UserId=" | rex field=_raw UserId=(?<userId>.*?)# | dedup userId | rex field=_raw X-REQUEST-ID=(?<REQID>.*?)\s | stats list(userId) as "User ID" list(REQID) as X-REQUEST-ID by _time] | where 'Dollar Discount'>=500.00 OR 'Percent Override'>=50.00 | table "User ID" Item "Original Price" "Dollar Discount" "Test Price" "Percent Override" Time

Re: How to use where clause with table containing fields within quotes

nits — Wed, 07 Oct 2020 21:29:58 GMT

@ITWhisperer Thanks, this works