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

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!

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