Splunk Search

How to count filled and blank cells in excel spreadsheet by writing splunk query?

ngwodo
Path Finder

I need help on splunk query that will count both filled and empty cells in excel spreadsheet differently  and give the values in percentages. I am trying to track down the percentage of the cells left to be filled out in the excel spreadsheet using  |inputlookup David_metrics_temp.csv

Labels (2)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

Use eval to add filled and empty.

| eval total = filled + empty

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

Here is a way to count the number of filled and empty cells.  I'll leave it to you to compute the percentage.

| inputlookup David_metrics_temp.csv 
| eval filled=0, empty=0 
| foreach * [eval filled=if('<<FIELD>>'=filled OR '<<FIELD>>'=empty,filled,if(isnotnull('<<FIELD>>'),filled+1,filled)), empty=if('<<FIELD>>'=filled OR '<<FIELD>>'=empty,empty,if(isnull('<<FIELD>>'),empty+1, empty))] 
| fields empty filled
| stats sum(*) as * 
| table filled empty
---
If this reply helps you, Karma would be appreciated.
0 Karma

ngwodo
Path Finder

It worked very well. Thanks.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.

---
If this reply helps you, Karma would be appreciated.

ngwodo
Path Finder

Please what about if I add up the totals of filled and empty will this query below do it? 

 

| eventstats sum(filled, empty) AS total  | table filled empty

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Use eval to add filled and empty.

| eval total = filled + empty

 

---
If this reply helps you, Karma would be appreciated.

ngwodo
Path Finder

Thanks. I had already figure it out. Thanks again for your help.

0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...