topic Re: Calculate the "month" after first appearance in Splunk Search

Calculate the "month" after first appearance

HeinzWaescher — Tue, 13 May 2014 15:12:03 GMT

Hi,

I'm doing an analysis about users whose first event was in January 2014. I want to know, what they did in month 1,2,3 after their first appearance. For these users the January would be month "0". Feb would be "1", March "2" and so on...

So the goal is to add a field to every event: "month afer first appearance" with a numeric value.

Is it possible to calculate to month after the first appearance?
I already did something like this for the "day after first appearance". It looked like this:

| bucket span=1d timestamp_of_first_appearance
| bucket span=1d timestamp
| eval day=(timestamp-timestamp_of_first_appearance)/86400

But I can't do this for a monthly perspective, because the duration for the months varies

Heinz

Re: Calculate the "month" after first appearance

somesoni2 — Mon, 28 Sep 2020 16:35:53 GMT

How about adding this
..your search..| eval month_of_first_appearance=tonumber(strftime(timestamp_of_first_appearance,"%m)) | eval month_of_current= tonumber(strftime(timestamp,"%m")) | eval month=if(month_of_current>month_of_first_appearance,month_of_current-month_of_first_appearance,12-month_of_first_appearance +month_of_current)

Re: Calculate the "month" after first appearance

HeinzWaescher — Mon, 28 Sep 2020 16:36:33 GMT

Thanks for your suggestion.
Unfortunately this approach will cause problems, if month_of_current<=month_of_first_appearance.

Re: Calculate the "month" after first appearance

lguinn2 — Thu, 15 May 2014 01:25:00 GMT

Why not something like this

...

| eval timestampMonth=relative_time(timestamp_of_first_appearance,"@mon")
| eval timestampMonth2=relative_time(timestamp_of_first_appearance,"+1mon@mon")
| eval timestampMonth3=relative_time(timestamp_of_first_appearance,"+2mon@mon")
| eval timestampEnd=relative_time(timestamp_of_first_appearance,"+3mon@mon")
| eval eventMonth = relative_time(timestamp_of_first_appearance,"@mon")
| eval whichMonth = case (eventMonth < timestampMonth,"before first appearance",
                                         eventMonth < timestampMonth2,"In month of appearance",
                                         eventMonth < timestampMonth3,"In second month since appearance",  
                                         eventMonth < timestampEnd,"In third month since appearance",
                                         1==1,"After third month of appearance")

Re: Calculate the "month" after first appearance

HeinzWaescher — Thu, 15 May 2014 12:35:25 GMT

Hi lguinn,

I've adjusted your approach to this and it seems to work fine:

| bucket span=1mon _time
| lookup lookup.csv userid OUTPUT first_appearance
| bucket span=1mon first_appearance

| eval month0=relative_time(first_appearance,"@mon")
| eval month1=relative_time(first_appearance,"+1mon@mon")
| eval month2=relative_time(first_appearance,"+2mon@mon")
| eval month3=relative_time(first_appearance,"+3mon@mon")
| eval month4=relative_time(first_appearance,"+4mon@mon")
| eval month5=relative_time(first_appearance,"+5mon@mon")
| eval month6=relative_time(first_appearance,"+6mon@mon")
.
.
.
| eval month_after_appearance=case(_time=month0, "0",
 _time=month1, "1",
 _time=month2, "2",
 _time=month3, "3",
 _time=month4, "4",
 _time=month5, "5",
 _time=month6, "6",)
.
.
.

| stats dc(userid) by month_after_appearance

Thanks a lot!