Using Eval for calculated Metrics

[DanielFordWA]

Hi,

I have the following search which returns the number of users logging onto a system for the previous month and groups them by a range of Days. The search is only for users onboarded before the start of the previous month.

The append search gives me the total number of users that have ever logged onto the system, prior to that month.

All searches are run off a summary index which is populated daily with | bucket span=1d _time |

index=dbuserday earliest=-1mon@mon latest=@mon cs_username="EXT*" NOT [| inputlookup testers_lookup | fields cs_username] 
| lookup EXTdata cs_username OUTPUTNEW OnboardedDate 
| eval Onboarded=strptime(OnboardedDate,"%d/%m/%Y")  
| eval BegMonth=relative_time(now(), "-1mon@mon")  
| WHERE BegMonth>Onboarded  
| stats dc(_time) AS Days by cs_username
| eval Days=case(Days=1,"1",Days>=2 AND Days<=11,"2-11",Days>11,"12+")
| append [search index=dbuserday earliest=-32mon@mon latest=-1mon@mon cs_username=EXT* NOT [| inputlookup testers_lookup  | fields cs_username] | Eval Days="ALL" | dedup cs_username | table cs_username Days ]
| stats dc(cs_username) as Users by Days

This give me the results...

Days      Users
1          1302
2-11      13543
12+       32075 
ALL       51024

How can I use Eval to subtract the values in Days "1", "2-11", "12+" from "All" to return a Days ="0" Users=4014?

    Days      Users
    0          4014
    1          1302
    2-11      13543
    12+       32075 

Hope you can help,

Thanks,

Dan

[woodcock]

It is whacky, but this will do it:

index=dbuserday earliest=-1mon@mon latest=@mon cs_username="EXT*"
NOT [| inputlookup testers_lookup | fields cs_username] 
| lookup EXTdata cs_username OUTPUTNEW OnboardedDate 
| eval Onboarded=strptime(OnboardedDate,"%d/%m/%Y")  
| eval BegMonth=relative_time(now(), "-1mon@mon")  
| WHERE BegMonth>Onboarded  
| stats dc(_time) AS Days by cs_username
| eval Days=case(Days=1,"1",Days>=2 AND Days<=11,"2-11",Days>11,"12+")
| append [search index=dbuserday earliest=-32mon@mon latest=-1mon@mon cs_username=EXT*
NOT [| inputlookup testers_lookup
| fields cs_username] | Eval Days="ALL" | dedup cs_username | table cs_username Days ]
| stats dc(cs_username) as Users by Days
| transpose | eval "row 5" = $row 5$ - $row 4$ - $row 3$ - $row 2$ | eval "row 5" = coalesce($row 5$, 0)
| transpose | where column!="column" | fields row* | rename "row 1" AS Days | rename "row 2" AS Users

[DanielFordWA]

Thanks for the reply.

I get the following results, but you have given me some ideas.

0          0
1          1302
2-11      13543
12+       32075
ALL       51024

[woodcock]

Are you sure it doesn't work? I actually tested it against faux data and it worked exactly as you described.

[DanielFordWA]

This may be my mistake, I'll try again. Thanks for checking against dummy data.