topic Re: Using Eval for calculated Metrics in Splunk Search

Using Eval for calculated Metrics

DanielFordWA — Fri, 05 Jun 2015 09:54:51 GMT

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

Re: Using Eval for calculated Metrics

woodcock — Fri, 05 Jun 2015 13:40:06 GMT

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

Re: Using Eval for calculated Metrics

DanielFordWA — Tue, 09 Jun 2015 10:05:07 GMT

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

Re: Using Eval for calculated Metrics

woodcock — Tue, 09 Jun 2015 17:34:06 GMT

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

Re: Using Eval for calculated Metrics

DanielFordWA — Wed, 10 Jun 2015 07:47:22 GMT

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