Query for different lines with status

[graziaedu]

I have a log as a below

cod:5678,status:600
cod:9012,staus:600
cod:1234,status:600
cod: 1234,status:900
cod:4987,status:600
cod:4987,status:900
cod:3655,status:600
cod:3655,status:900

I need a query that give me this result

cod	status1	status2
1234	600	900
5678	600
9012	600
4987	600	900
3655	600	900

how can i write a query for this?

Thanks

[Richfez]

OK, so assuming you strictly interpret the need here, and assume that it's only values of 600 and 900 and no others, and I took out one 600-value from the test data I built so I could confirm that if there's a 900 and NOT a 600, that it does what I expected.

Then, with those caveats and this fragility, one could do the last two lines here.

| makeresults 
| eval testdata = "cod:5678,status:600XXcod:9012,status:600Xcod:1234,status:600Xcod: 1234,status:900Xcod:4987,status:600Xcod:4987,status:900Xcod:3655,status:900"
| makemv testdata delim="X"
| mvexpand testdata
| rex field=testdata "cod:\s*(?<cod>[^,]*),status:\s*(?<status>\d*)"
| chart values(status) over cod by status
| rename 600 AS Status1, 900 AS Status2

WHERE - the first 5 lines (run them all by themselves!) are just to build a test data set as a run-anywhere search.

The chart command and the rename command - the last two lines - are the ones that do the work.

And it assumes you have this data ingested in a way there are two fields for each record - a cod field and a status field.  Otherwise, really, you should go back and reingest the data and set that up.

[ashvinpandey]

@graziaedu How actually you are looking for results ? if status1 is 600 then status2 should also be 600 ? am i getting this right ?

[PickleRick]

What if for a given value of cod you had just status=900?

[graziaedu]

I have 900 only if I have 600. 

[PickleRick]

OK. So why not just list the max value? (either the 600 or 900)

Of course you can do it in two columns but it's much easier to just

| stats max(status) by cod

than to try to combine the values, then split them into different columns.

It is of course possible, but this one is way, way simpler

[graziaedu]

I need 2 columns because I have to analyze if the cod changed status.

Cod 600 is processing and 900 is processed. I need this view in a table.

[PickleRick]

I don't get it. You say that there's no possiblity that you have just 900, without 600. So if you have 600 as the result, it's still processing. If 900 - it's finished (must have been 600 previously, but now you have both events)

As I said - it's possible to do it in two columns but it's just an overkill and unnecessary complication.