Turning a field with status variables in to a new field with absolute an difference

[Software-Simian]

Hi,

i looked for an answer an some came close. But i could not get it flying.

Here is the Problem Description:

I have a field that contains the status of a ticket ("created_done").

I can easily count the number using by or doing that:

| stats count(eval(created_done="created")) as created count(eval(created_done="done")) as done by title impact

However i would like something like this:
| stats count by title impact status

status at this point should be a field holding the sum of solved tickets and the sum of open tickets:

Title	Impact	Status	Count
title 1	impact 1	solved	90
title 1	impact 1	open	5
title 1	impact 2	solved	45
title 1	impact 2	open	3

Probably this has already been answered, i apologize in advance, but i could not get any solution working.

Kind regards,

Mike

[Software-Simian]

Hi,

actually the values come from exactly that field. so i want to get from:

Title	Impact	Status	Count
title 1	impact 1	solved	90
title 1	impact 1	created	95
title 1	impact 2	solved	45
title 1	impact 2	created	48

to:

Title	Impact	Status	Count
title 1	impact 1	solved	90
title 1	impact 1	open	5
title 1	impact 2	solved	45
title 1	impact 2	open	3

[isoutamo · Accepted solution]

One way to do it:

| makeresults 
| eval _raw="Title,   Impact,   Status,  Count
title 1,impact 1,solved,90
title 1,impact 1,created,95
title 1,impact 2,solved,45
title 1,impact 2,created,48"
| multikv forceheader=1
| fields - _raw, linecount
| streamstats reset_on_change=t window=1 first(Count) as preCount last(Count) as nxtCount by Title, Impact
| eval Open=nxtCount - preCount
| eval Status = if (Status=="created", "open", Status), Count = if(Status=="open", Open, Count)
| table Title, Impact, Status, Count

r. Ismo 

[Software-Simian]

Hi Iso,

thanks, it works like a charme!!

regards,

Mike

[isoutamo]

Hi

if I understand this right, you have almost correct stats here. Just replace status -> created_done and after stats you could rename that field as status.

r. Ismo