Splunk Search

age group report from birth year data

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

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

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

Champion

it needs the tick mark if it solved the problem 🙂

0 Karma

Builder

it works, Thank you!.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!