Splunk Search

How do I remove rows from a table based on empty column values?

mrg_linus
Engager

What I'm trying to do
Using the export API /servicesNS/admin/search/search/jobs/export?output_mode=json&search=search I want to fetch (into a new cool system) comments made from CustomerServiceAgents in another system that then have been imported in Splunk. There are a lot of comments and I see that I can reduce the amount of retrieved rows by eliminating the ones where all the Comment fields are empty.

My question
How do I go about removing the rows where all Comments are empty? I've fiddled about with where and setting the field =*, but Splunk just sees this as invalid. See attached image to view my query and the result.

0 Karma
1 Solution

maciep
Champion

Yeah, where doesn't like wildcards, but you can use it with match function.

.... | where match(comment_field,".")

View solution in original post

somesoni2
Revered Legend

Based on your data from the screenshot, try this

your base search  Value=* | eval {Property}=Value | stats lastest(*) as * by AccountID

maciep
Champion

Yeah, where doesn't like wildcards, but you can use it with match function.

.... | where match(comment_field,".")

mrg_linus
Engager

Writing | where match(Comment1,".") nets me 0 results. I would assume it would return me the row in the picture where Comment1 is populated.

My goal is of course having all the rows where any of the Comments are populated.

0 Karma

maciep
Champion

it depends on where you have the where. If it's after the stats, then you don't have a field called Comment1, you have a field called "latest(Comment1)".

So maybe try putting it before the stats. Or try what somesoni2 mentioned to keep the original field names.

mrg_linus
Engager

Right perfect :)!

You are correct, I needed | where match("latest(Comment1)",".") if I didn't rename the columns. Thanks to @somesoni2!

Ended up with

index="*_db_customer" sourcetype="*:db:account:property" | eval {Property} = Value | stats latest(Comment*) by AccountID | table AccountID, latest(Comment*) | rename latest(*) to * | where match(Comment1,".") OR match(Comment2,".") OR match(Comment3,".") OR match(Comment4,".") OR match(Comment5,".") OR match(Comment6,".") OR match(Comment7,".") OR match(Comment8,".") OR match(Comment9,".") OR match(Comment10,".")
0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to July Tech Talks, Office Hours, and Webinars!

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...

Updated Data Type Articles, Anniversary Celebrations, and More on Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

A Prelude to .conf25: Your Guide to Splunk University

Heading to Boston this September for .conf25? Get a jumpstart by arriving a few days early for Splunk ...