Splunk Search

How to work out the age of a user based on date of birth?

Amohlmann
Communicator

I want to group users by their age which range from roughly 5 years to 90. The dateofbirth field is formatted like this 1963-12-30.
Originally I thought I would just compare the epoch time of the DoB with now(), then turn that back into how many years old the user is. Now obviously this will not work for people who were born before 1970.

| search query...
| eval dateofnow=now()
| fieldformat dateofnow=strftime(dateofnow,"%Y-%m-%d")  
| eval dateofbirthTest=dateofbirth
| convert timeformat="%Y-%m-%d" mktime(dateofbirthTest)
| eval age=(now()-dateofbirthTest)
| eval ageReadable=age/60/60/24/365.25

The above query works fine for all users that were born after 1970. The next thing I thought would be to compare the current year to the year the user was born, but this would be slightly too inaccurate for what I am hoping to achieve. I feel like I am missing something super obvious. Is there an easy way to work out the age of something from before 1970 with accuracy?

Tags (4)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try (Run anywhere sample), give age in years and additional days

| gentimes start=-1 | eval birthdate="1951-03-30" | table birthdate | rex field=birthdate "(?<birthyear>\d+)-(?<birthmonth>\d+)-(?<birthday>\d+)" | eval bdaythisyear=strptime(strftime(now(),"%Y")."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d") | eval lastbday=if(bdaythisyear>now(),strptime((tonumber(strftime(now(),"%Y"))-1)."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d"),strptime(strftime(now(),"%Y")."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d")) | eval ageyear=tonumber(strftime(lastbday,"%Y"))-birthyear | eval ageday=round((now()-lastbday)/86400)

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Give this a try (Run anywhere sample), give age in years and additional days

| gentimes start=-1 | eval birthdate="1951-03-30" | table birthdate | rex field=birthdate "(?<birthyear>\d+)-(?<birthmonth>\d+)-(?<birthday>\d+)" | eval bdaythisyear=strptime(strftime(now(),"%Y")."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d") | eval lastbday=if(bdaythisyear>now(),strptime((tonumber(strftime(now(),"%Y"))-1)."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d"),strptime(strftime(now(),"%Y")."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d")) | eval ageyear=tonumber(strftime(lastbday,"%Y"))-birthyear | eval ageday=round((now()-lastbday)/86400)

Amohlmann
Communicator

Wow, yes this is exactly what I needed.
You way is much more clever than my way. thanks!

0 Karma

lcblucas
Explorer

Hi all,

How can I do to put my real users birthdays on eval, instead 1951-03-30?

My birthdate field is data_nascimento.

Thanks and regards!

0 Karma

Amohlmann
Communicator

First out, chop off the first bit so you are left with this:

Your base query 
|rex field=data_nascimento "(?<birthyear>\d+)-(?<birthmonth>\d+)-(?<birthday>\d+)" 
| eval bdaythisyear=strptime(strftime(now(),"%Y")."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d") 
| eval lastbday=if(bdaythisyear>now(),strptime((tonumber(strftime(now(),"%Y"))-1)."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d"),strptime(strftime(now(),"%Y")."-".tostring(birthmonth)."-".tostring(birthday),"%Y-%m-%d")) 
| eval ageyear=tonumber(strftime(lastbday,"%Y"))-birthyear 
| eval ageday=round((now()-lastbday)/86400)

Also you will need to change the regular expression in that part if your birthday format is not YYYY-MM-DD

0 Karma

Amohlmann
Communicator

The only way I found to do this was to do a bit of brute force. This way is not 100% accurate but was close enough for what I wanted to do.
The first thing I did was split out the year, month, and day of the date of birth and of now(). Yes the regular expression can be tidied:

| eval yearOfNow = strftime(now(),"%Y")
| eval monthOfNow = strftime(now(), "%m")
| eval dayOfNow = strftime(now(), "%d")
| rex field=dateofbirth "(?<yearOfBirth>\d{4})"
| rex field=dateofbirth "-(?<monthOfBirth>\d{2})-"
| rex field=dateofbirth "\d{4}-\d{2}-(?<dayOfBirth>\d{2})"

Then assuming none of the dates are before year 0 you can very very roughly work out the number of days between date of birth and now by doing something like this:

 |eval daysToBirth = (yearOfBirth*365.25) + ((monthOfBirth-1)*30) + (dayOfBirth)
 |eval daysToToday = (yearOfNow*365.25) + ((monthOfNow-1)*30) + (dayOfNow)
 |eval yearsOld = (daysToToday-daysToBirth)/365.25

So if you use the example of someone being born on 06/05/1965 and you try to work out their age (from today which is 12/01/2016) you get the following

yearOfBirth=1965
monthOfBirth=5
dayOfBirth=6

yearOfNow=2016
monthOfNow=1
dayOfNow=12

daysToBirth=717842.25
daysToNow=736356

Which gives you the number of days between the dates as ROUGHLY 18513.75
Which makes

yearsOld=50.687

This example gives you a reasonably accurate answer (the correct answer is actually 18513 days exactly). The inaccuracy is due to the fact I am cheating and saying that each month in a year is 30 days long.

0 Karma

lcblucas
Explorer

Hi Amohlmann,

I´m new splunk user and not familiar with splunk commands yet. Did you have success to your request? I don´t undertand the expression that you are using.

I need the same of you, extract the user age from birth date. Can you help me, please?

My field is birthdate and have this formatting: %d%m%y.

After yhis, my ideia is create 3 age groups. Example:

1 - Up 18 years old
2 - 18 - 25 years old
3 - 25+ years old

Does anybody can help me?

Thanks and regards!

0 Karma

Amohlmann
Communicator

I never got a super accurate way of doing it. The closest I got was to split out and get the year month and day in separate fields for both the date of birth and now().
Then assuming none of the dates are before year 0 you can very very roughly work out the number of days between date of birth and now by doing something like this:

|eval daysToBirth = (yearOfBirth*365.25) + ((monthOfBirth-1)*30) + (dayOfBirth)
|eval daysToToday = (yearOfNow*365.25) + ((monthOfNow-1)*30 + (dayOfNow)
|eval yearsOld = (daysToToday-daysToBirth)/365.25

So if you use the example of someone being born on 06/05/1965 and you try to work out their age (from today which is 12/01/2016) you get the following

yearOfBirth=1965
monthOfBirth=5
dayOfBirth=6

yearOfNow=2016
monthOfNow=1
dayOfNow=12

daysToBirth=717842.25
daysToNow=736356

Which gives you the number of days between the dates as ROUGHLY 18513.75
Which makes

yearsOld=50.687

This example gives you a reasonably accurate answer (the correct answer is actually 18513 days exactly). The inaccuracy is due to the fact I am cheating and saying that each month in a year is 30 days long.

The reason I have not accepted this as the answer as it is not accurate. It is close enough for what I am doing but I was hoping someone out there as a much cleverer way of doing it instead of my brute force attempt.

0 Karma

javiergn
SplunkTrust
SplunkTrust

Rather than converting to epoch try using some of the existing algorithms to get the number of days from a date and the reverse operation. Then substract days_now and days_dateOfBirth, and apply the reverse operation to the result in order to obtain the year/month/day from there.

A quick Google search gave me this (not tested though):

https://alcor.concordia.ca/~gpkatch/gdate-algorithm.html

0 Karma

ncrofts_splunk
Splunk Employee
Splunk Employee

Hi Amohlmann,

Could this existing Splunk Answers help?
https://answers.splunk.com/answers/231771/how-to-extract-age-from-a-birthday-field-before-th.html

Cheers Nick

aljohnson_splun
Splunk Employee
Splunk Employee

I think this has been solved by alacer

eval age = tonumber(strftime(now(), "%Y")) - tonumber(mvindex(split(dateofbirth,"-"),0))

0 Karma

Amohlmann
Communicator

This makes the age slightly older than they actually are. If someone is 16 years and 1 day old it will say they are 17.
The way I have it is a little more accurate but not nearly as elegant.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...