index=main (sourcetype=bb OR sourcetype=cc) type=DELETE | transaction info.agentId startswith=COMPLETED endswith=DELETE keepevicted=true | search closed_txn=0 type=DELETE | stats count(info.agentId) AS "Deleted Device"
| appendcols [search index=main(sourcetype=bb OR sourcetype=cc ) type=COMPLETED OR type=DELETE| transaction info.agentId startswith=COMPLETED endswith=DELETE keepevicted=true | search closed_txn=0 type!=DELETE | stats count(info.agentId) AS "Onboarded Devices" ]
My outcome is:
Deleted Device Deleted Device
151 155
But I need the difference as below.
Total Devices
4
Here's a completed untested query that avoids appendcols
.
index=main(sourcetype=bb OR sourcetype=cc ) type=COMPLETED OR type=DELETE| transaction info.agentId startswith=COMPLETED endswith=DELETE keepevicted=true | search closed_txn=0 | stats count(eval(type=DELETE)) AS "Deleted Device" count(eval(type!=DELETE)) AS "Onboarded Devices" | eval "Total Devices"='Onboarded Devices' - 'Deleted Device'