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!

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...

Introducing Ingest Actions: Filter, Mask, Route, Repeat

WATCH NOW Ingest Actions (IA) is the best new way to easily filter, mask and route your data in Splunk® ...