Having a set of logs,we generate a report in table format containing 18 fields .There is a requirement to eliminate the records which has only one field containing value and the remaining 17 fields empty.For example
sl.no Filename user ..................... service error
2 reyg ........................
3 frfe fwef ........................frefg reytr
The record no 1 & 2 contains only one field containg value and the remaining fields empty.
slno 1.Only the field filename has value and other fields empty
slno 2.Only the field user has value and other fields empty
I want to eliminate these two records and similar records containing one non-empty field from displaying in the report.
eval a new field that concatenates your 18 fields into one delimited string, use
makemv on the new field, and then
mvcount to create a flag for rows/records that only have a single value:
your_current_search_here ... | eval concatField=Filename."|".user."|".service."|".error | makemv delim="|" concatField | eval count=mvcount(concatField) | where count>1 | fields - concatField,count
I haven't tested this exact example but I've done similar things before with no problem.
The understanding that has been got from the above query is
First calculate the count of events using "streamstats" for each event and store it in sno.
Secondly count the occurence of all fields for each sno(per event basis) and store it in c*.Then find the total number of numeric fields matching the field c* and store it in the NOnNullFields.Exclude the records that contain NonNullFields=1.Finally remove the fields c_* and NonNullFields
Can you confirm whether our understanding is correct .Also please correct if its wrong
Try this workaround
your base search giving your report with 18 fields | streamstats count as sno| eventstats count(*) as c_* by sno |fields - sno | addtotals fieldname=NonNULLFields c_* | where NOT NonNULLFields=1 | fields - c_*,NonNULLFields