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

HeinzWaescher
Motivator

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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