Splunk Search

Calculate the "month" after first appearance

HeinzWaescher
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)
0 Karma
1 Solution

lguinn2
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")

View solution in original post

HeinzWaescher
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!

0 Karma

lguinn2
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")

View solution in original post

HeinzWaescher
Motivator

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

0 Karma

somesoni2
Revered Legend

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)

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!