I have a chart that gives me serial numbers, some of the spots for serial numbers are empty. I want to compare how many are empty vs the total number of serials I have (That are not empty).
So basically a bar graph with 1 bar being total number of serials
The other bar being number of empty serials
|inputlookup Serial_Report.csv |stats count by serial_number|stats sum(serial_number) AS totalSerial | fillnull value="XXX" serial_number | search serial_number="XXX" | stats count by serial_number|stats sum(serial_number) AS totalEmptySerial |fields totalEmptySerial, totalSerial
Any Ideas?
Give this a try
|inputlookup Serial_Report.csv |stats count by serial_number | eval is_empty=if(serial_number="" OR len(trim(serial_number))=0,1,0)
| stats sum(is_empty) as totalEmptySerial count as totalSerial
Give this a try
|inputlookup Serial_Report.csv |stats count by serial_number | eval is_empty=if(serial_number="" OR len(trim(serial_number))=0,1,0)
| stats sum(is_empty) as totalEmptySerial count as totalSerial
https://pasteboard.co/8fKoBncto.png
Not exactly, it doesn't appear to recognize the empty fields
How about this
|inputlookup Serial_Report.csv | eval serial_number=if(isnull(serial_number) OR serial_number="" OR len(trim(serial_number))=0,"XXX",serial_number) |stats count by serial_number | eval is_empty=if(serial_number="XXX",1,0)
| stats sum(is_empty) as totalEmptySerial count as totalSerial
Now I am getting 1 count result for empty, should be around 300.
Pheww. Try this
|inputlookup Serial_Report.csv | eval serial_number=if(isnull(serial_number) OR serial_number="" OR len(trim(serial_number))=0,"XXX",serial_number) |stats count by serial_number | eval is_empty=if(serial_number="XXX",count,0)
| stats sum(is_empty) as totalEmptySerial sum(count) as totalSerial
Ah so close, I still need two bars though. I am not sure if it is possible
Basically one would say 300, the other would be 20,000
This is what your search has created so far
https://pasteboard.co/8hgrpV061.png
Let this be the final attempt. Try this
|inputlookup Serial_Report.csv | eval serial_number=if(isnull(serial_number) OR serial_number="" OR len(trim(serial_number))=0,"XXX",serial_number) |stats count by serial_number | eval Metrics=if(serial_number="XXX",split("totalEmptySerial,totalSerial",","),"totalSerial")
| stats sum(count) as count by Metrics
Thank you!! Perfect.