Splunk Search

## Calculate the "month" after first appearance

Motivator

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

BG

Heinz

Tags (2)
1 Solution
Legend

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")
``````
Motivator

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!

Legend

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")
``````
Motivator