Splunk Search

Using Eval for calculated Metrics

DanielFordWA
Contributor

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

0 Karma

woodcock
Esteemed Legend

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
Contributor

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
0 Karma

woodcock
Esteemed Legend

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

0 Karma

DanielFordWA
Contributor

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...