Splunk Search

age group report from birth year data

dhavamanis
Builder

I have a raw data and its contains the user birth Year, city and registered date, how to calculate the age group report based on city and (year quarterly / monthly / daily) .

Data dump like :


userID    Birth_year    city       country   registered_date

1           1983         New York    US       04/04/2012

2           1977         Los Angelos US       01/02/1998

Sample Results :


Age    City       Count

2012-Q1 :

7-14  Los Angelos  25

15-21 Los Angelos  12

2012-Q2 :

22-33 Los Angelos  1050

7-14  New York  2050

15-21 New York  301

22-33 New York  101
Tags (3)
1 Solution

linu1988
Champion

The formatting is not really possible in splunk. And i am assuming all the fields are already extracted and accessible by field name. Let's try the below

source=...|eval Qtr=strftime(strptime(registered_date,"%m/%d/%Y"),"%m")|eval Qtr=floor(Qtr%3)+1|eval Qtr="Q".Qtr|eval Year=strftime(strptime(registered_date,"%m/%d/%Y"),"%Y")|eval tage=Year-Birth_year|eval Age_Group=case(tage>6 AND tage<15,"7-14",tage>14 AND tage<22,"15-21",tage>21 AND tage<34,"22-33")|eval YQtr=Year."-".Qtr|stats count as "No of People" by Age_Group,City,YQtr

Thanks

View solution in original post

linu1988
Champion

The formatting is not really possible in splunk. And i am assuming all the fields are already extracted and accessible by field name. Let's try the below

source=...|eval Qtr=strftime(strptime(registered_date,"%m/%d/%Y"),"%m")|eval Qtr=floor(Qtr%3)+1|eval Qtr="Q".Qtr|eval Year=strftime(strptime(registered_date,"%m/%d/%Y"),"%Y")|eval tage=Year-Birth_year|eval Age_Group=case(tage>6 AND tage<15,"7-14",tage>14 AND tage<22,"15-21",tage>21 AND tage<34,"22-33")|eval YQtr=Year."-".Qtr|stats count as "No of People" by Age_Group,City,YQtr

Thanks

linu1988
Champion

it needs the tick mark if it solved the problem 🙂

0 Karma

dhavamanis
Builder

it works, Thank you!.

0 Karma
Get Updates on the Splunk Community!

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 ...

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, ...