I have a simple stats chart that shows a daily total with 6 fields. I would like to add a column that adds 3 of those fields together. I have tried and failed with addtotals and addcoltotals. It seems a simple task but I am somehow missing a concept. Here is one of the failed attempts:
mysearch* | stats sum(RuleCount) sum(NewInViolationCount) sum(NewHistoryCount) sum(NoChangeCount) sum(CodeMissingForRuleCount) sum(MeterDataMissingCount) by _time | sort -_time | rename _time as "Date Logged" | convert timeformat="%Y-%m-%d" ctime("Date Logged") | eval total=NewInViolationCount + NewHistoryCount + NoChangeCount | addtotals col=t fieldname=expected_total total
What am I missing? I just want a quick reference added to the chart. Three lines should always equaal the sum of the RuleCount line.
You need to rename them in the stats, since stats is a field modifier command. The new field is actually "sum(RuleCount)" unless you rename it.
mysearch* | stats sum(RuleCount) AS RuleCount sum(NewInViolationCount) AS NewInViolationCount sum(NewHistoryCount) AS NewHistoryCount sum(NoChangeCount) AS NoChangeCount sum(CodeMissingForRuleCount) AS CodeMissingForRuleCount sum(MeterDataMissingCount) AS MeterDataMissingCount by _time | sort -_time | rename _time as "Date Logged" | convert timeformat="%Y-%m-%d" ctime("Date Logged") | eval total=NewInViolationCount + NewHistoryCount + NoChangeCount | addtotals col=t fieldname=expected_total total
You need to rename them in the stats, since stats is a field modifier command. The new field is actually "sum(RuleCount)" unless you rename it.
mysearch* | stats sum(RuleCount) AS RuleCount sum(NewInViolationCount) AS NewInViolationCount sum(NewHistoryCount) AS NewHistoryCount sum(NoChangeCount) AS NoChangeCount sum(CodeMissingForRuleCount) AS CodeMissingForRuleCount sum(MeterDataMissingCount) AS MeterDataMissingCount by _time | sort -_time | rename _time as "Date Logged" | convert timeformat="%Y-%m-%d" ctime("Date Logged") | eval total=NewInViolationCount + NewHistoryCount + NoChangeCount | addtotals col=t fieldname=expected_total total
Thank you very much!
Change: | addtotals col=f fieldname=expected_total total
Also, please accept as answered if I have done so. Thanks!
That worked great, thank you. It creates a new question however. In the column that is now generated with the sum of those 3 fields it has a total number listed at the bottom of the column. Is there way to remove that listing from the bottom of that line in the table?