Splunk Search
Highlighted

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

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
Highlighted

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

Champion

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

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

View solution in original post

Highlighted

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

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
Highlighted

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

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.

Highlighted

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

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
Highlighted

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

SplunkTrust
SplunkTrust

Based on your data from the screenshot, try this

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