Need to get help about column subtraction

[saipavan123]

Hi team,

message_id status time

2020-01-21T13:09:14.416164Z PROCESSED 2020-02-19T01:50:05.55630875Z
2020-01-21T13:09:14.416164Z PROCESSING 2020-02-19T01:50:04.621606854Z
2020-01-21T13:09:44.586501Z ERROR 2020-02-19T01:50:04.305742277Z
2020-01-21T13:09:44.586501Z PROCESSING 2020-02-19T01:50:04.233225192Z
2020-01-21T13:09:44.586416Z PROCESSED 2020-02-19T01:50:04.142651435Z
2020-01-21T13:09:44.586416Z PROCESSING 2020-02-19T01:50:03.826457927Z
2020-01-21T13:09:44.586321Z PROCESSED 2020-02-19T01:50:03.745964666Z
2020-01-21T13:09:44.586321Z PROCESSING 2020-02-19T01:50:03.449583679Z
2020-01-21T13:09:44.586190Z PROCESSED 2020-02-19T01:50:03.337887858Z
2020-01-21T13:09:44.586190Z PROCESSING 2020-02-19T01:50:03.086329734Z
2020-01-21T13:09:44.586063Z PROCESSED 2020-02-19T01:50:03.00531639Z
2020-01-21T13:09:44.586063Z PROCESSING 2020-02-19T01:50:02.735821778Z

I have a three columns: message_id, status, time
I need to get the count for status column like
PROCESSED = ?
PROCESSING = ?
ERROR = ?

And finally, once we will get the count for ERROR,Processed,Processing then i need to do the subtraction like below:
Total = ERROR+PROCESSED-PROCESSING
Total = ?

I'm using below query to get the total but it does not work::

|rex field=log ".* Updated the Message Id : (?[^ ]). status : (?.*)" | table message_id, status, time | stats count by status | eval total = ERROR + PROCESSED - PROCESSING

[to4kawa]

| makeresults
| eval _raw="message_id,status,time
2020-01-21T13:09:14.416164Z,PROCESSED,2020-02-19T01:50:05.55630875Z
2020-01-21T13:09:14.416164Z,PROCESSING,2020-02-19T01:50:04.621606854Z
2020-01-21T13:09:44.586501Z,ERROR,2020-02-19T01:50:04.305742277Z
2020-01-21T13:09:44.586501Z,PROCESSING,2020-02-19T01:50:04.233225192Z
2020-01-21T13:09:44.586416Z,PROCESSED,2020-02-19T01:50:04.142651435Z
2020-01-21T13:09:44.586416Z,PROCESSING,2020-02-19T01:50:03.826457927Z
2020-01-21T13:09:44.586321Z,PROCESSED,2020-02-19T01:50:03.745964666Z
2020-01-21T13:09:44.586321Z,PROCESSING,2020-02-19T01:50:03.449583679Z
2020-01-21T13:09:44.586190Z,PROCESSED,2020-02-19T01:50:03.337887858Z
2020-01-21T13:09:44.586190Z,PROCESSING,2020-02-19T01:50:03.086329734Z
2020-01-21T13:09:44.586063Z,PROCESSED,2020-02-19T01:50:03.00531639Z
2020-01-21T13:09:44.586063Z,PROCESSING,2020-02-19T01:50:02.735821778Z"
| multikv forceheader=1
| table message_id,status,time
| stats count by status
| eval count=if(status="PROCESSING",count * -1, count)
| stats sum(count) as total

Try this if you want only the result.

[manjunathmeti]

hi @saipavan123, as @richgalloway explained you need to convert status values to fields before calculating total.

Use transpose command.

| stats count by status | transpose header_field=status | eval total = ERROR + PROCESSED - PROCESSING

[saipavan123]

Hi manjunathmeti,

message_id status time

2020-02-12T12:22:23.415248Z ERROR 2020-02-14T00:01:14.038498814Z
2020-02-12T12:22:23.415248Z ERROR 2020-02-14T00:00:34.034346477Z
2020-02-12T12:22:23.415248Z ERROR 2020-02-13T23:59:53.851851061Z
2020-02-12T12:22:23.415248Z ERROR 2020-02-13T23:57:12.663621081Z
2020-02-12T12:22:23.415248Z ERROR 2020-02-13T23:53:51.293506747Z
2020-01-21T13:09:14.416164Z PROCESSED 2020-02-19T01:50:05.55630875Z
2020-01-21T13:09:14.416164Z PROCESSING 2020-02-19T01:50:04.621606854Z
2020-01-21T13:09:44.586501Z ERROR 2020-02-19T01:50:04.305742277Z
2020-01-21T13:09:44.586501Z PROCESSING 2020-02-19T01:50:04.233225192Z
2020-01-21T13:09:44.586416Z PROCESSED 2020-02-19T01:50:04.142651435Z
2020-01-21T13:09:44.586416Z PROCESSING 2020-02-19T01:50:03.826457927Z
2020-01-21T13:09:44.586321Z PROCESSED 2020-02-19T01:50:03.745964666Z
2020-01-21T13:09:44.586321Z PROCESSING 2020-02-19T01:50:03.449583679Z
2020-01-21T13:09:44.586190Z PROCESSED 2020-02-19T01:50:03.337887858Z
2020-01-21T13:09:44.586190Z PROCESSING 2020-02-19T01:50:03.086329734Z
2020-01-21T13:09:44.586063Z PROCESSED 2020-02-19T01:50:03.00531639Z
2020-01-21T13:09:44.586063Z PROCESSING 2020-02-19T01:50:02.735821778Z
2020-01-21T13:09:44.585532Z PROCESSED 2020-02-19T01:50:02.677935722Z
2020-01-21T13:09:44.585532Z PROCESSING 2020-02-19T01:50:02.379874913Z
2020-01-21T13:09:44.585456Z PROCESSED 2020-02-19T01:50:02.320574471Z
2020-01-21T13:09:44.585456Z PROCESSING 2020-02-19T01:50:02.056969718Z
2020-01-21T13:09:44.585379Z PROCESSED 2020-02-19T01:50:01.993389933Z
2020-01-21T13:09:44.585379Z PROCESSING 2020-02-19T01:50:01.645723986Z
2020-01-21T13:09:44.585301Z PROCESSED 2020-02-19T01:50:01.573655793Z
2020-01-21T13:09:44.585301Z PROCESSING 2020-02-19T01:50:01.319969304Z
2020-01-21T13:09:44.585220Z PROCESSED 2020-02-19T01:50:01.256761569Z
2020-01-21T13:09:44.585220Z PROCESSING 2020-02-19T01:50:00.980754532Z
2020-01-21T13:09:44.585132Z PROCESSED 2020-02-19T01:50:00.920435406Z
2020-01-21T13:09:44.583423Z PROCESSING 2020-02-19T01:49:54.709364124Z
2020-01-21T13:09:44.583342Z PROCESSED 2020-02-19T01:49:54.627564396Z
2020-01-21T13:09:44.583342Z PROCESSING 2020-02-19T01:49:54.379127471Z
2020-01-21T13:09:44.583255Z PROCESSED 2020-02-19T01:49:54.319034068Z
2020-01-21T13:09:44.583255Z PROCESSING 2020-02-19T01:49:54.028230252Z
2020-01-21T13:09:44.583171Z PROCESSED 2020-02-19T01:49:53.942640218Z
2020-01-21T13:09:44.583171Z PROCESSING 2020-02-19T01:49:53.689197493Z
2020-01-21T13:09:44.583085Z PROCESSED 2020-02-19T01:49:53.627728985Z
2020-01-21T13:09:44.583085Z PROCESSING 2020-02-19T01:49:53.389097603Z
2020-01-21T13:09:44.582989Z PROCESSED 2020-02-19T01:49:53.332868523Z
2020-01-21T13:09:44.582989Z PROCESSING 2020-02-19T01:49:53.085943873Z
2020-01-21T13:09:44.582905Z PROCESSED 2020-02-19T01:49:53.027980939Z
2020-01-21T13:09:44.582905Z PROCESSING 2020-02-19T01:49:52.757156504Z
2020-01-21T13:09:44.582821Z PROCESSED 2020-02-19T01:49:52.697941959Z
2020-01-21T13:09:44.582821Z PROCESSING 2020-02-19T01:49:52.463730556Z
2020-01-21T13:09:44.582727Z PROCESSED 2020-02-19T01:49:52.410138972Z
2020-01-21T13:09:44.582727Z PROCESSING 2020-02-19T01:49:52.169536808Z
2020-01-21T13:09:44.582639Z PROCESSED 2020-02-19T01:49:52.107720449Z
2020-01-21T13:09:44.582639Z PROCESSING 2020-02-19T01:49:51.84715461Z
2020-01-21T13:09:44.582555Z PROCESSED 2020-02-19T01:49:51.777011069Z
2020-01-21T13:09:44.582555Z PROCESSING 2020-02-19T01:49:51.488824085Z
2020-01-21T13:09:44.582467Z PROCESSED 2020-02-19T01:49:51.414304108Z
2020-01-21T13:09:44.582467Z PROCESSING 2020-02-19T01:49:51.146699571Z
2020-01-21T13:09:44.582370Z PROCESSED 2020-02-19T01:49:51.07314806Z
2020-01-21T13:09:44.582370Z PROCESSING 2020-02-19T01:49:50.803455506Z
2020-01-21T13:09:44.582288Z PROCESSED 2020-02-19T01:49:50.68563427Z
2020-01-21T13:09:44.582288Z PROCESSING 2020-02-19T01:49:50.418044177Z
2020-01-21T13:09:44.582211Z PROCESSED 2020-02-19T01:49:50.34967605Z

I had three columns message_id, status,time
I want to print the 'message_ids' which are YetToBeProcessed.

YetToBeProcessed = ERROR+PROCESSED-PROCESSING

Note: Earlier post was to get the Count which is total(YetToBeProcessed)

[manjunathmeti]

Try xyseries, then you can filter result based on the output.

| stats count by status, message_id | xyseries message_id, status, count | fillnull value=0

[richgalloway]

The stats command removes all fields except those mentioned in the command. In your example, only the 'count' and 'status' fields remain therefore, the eval fails because the needed fields are not present. Try swapping the order of the commands.