Splunk Search
Highlighted

Timechart'ing the result of multiple searches.

Path Finder

I'm trying to put together a time chart that's basically a representation of many separate searches. A stacked column chart would be ideal for this. Here's my base search...

index=main extra.user_id=* AND Create_Date>'2014-02-25 AND Create_Date<'2014-03-03 |dedup extra.user_id date_mday |timechart count

Create_Date represents the date the account was created. (That's pulled in via a lookup table if you're really interested.)

So, what I'm looking to create is a chart of user activity over typically a weeks time where the total of the column will represent the number of unique active users that day. That part is easy.

What I'd like to do is have the column made up of columns based on the range the account was created. I don't mind having to specify the Create_Date date ranges (which are weeks) manually to make up the components.

If someone just wants to give me a template then I can likely adapt it out from there.

Tags (2)
0 Karma
Highlighted

Re: Timechart'ing the result of multiple searches.

SplunkTrust
SplunkTrust

A template would look like this:

base search | eval category = case(...) | timechart count by category

The inside of the case() expression needs to compute the category for each event.

Edit: You might be better off using this to do weekly grouping:

base search | eval Create_Date = "Week starting ".strftime(relative_time(strptime(Create_Date, "%F"), "@w1"), "%F") | timechart count by Create_Date

Change the @w1 to @w0 if your week starts on Sunday rather than Monday.

View solution in original post

Highlighted

Re: Timechart'ing the result of multiple searches.

Path Finder

Could you give me some insight into making case work? I've not used it before sorry and so far after searching around a bit I'm still at a loss.

0 Karma
Highlighted

Re: Timechart'ing the result of multiple searches.

Path Finder

Here's what I've tried initially:

index=main extra.userid=* AND extra.endpoint=setplaybackposition |eval category = case(CreateDate>'2014-02-25 AND Create_Date<'2014-03-03, "week1") |timechart count by category

0 Karma
Highlighted

Re: Timechart'ing the result of multiple searches.

SplunkTrust
SplunkTrust

case() takes pairs of arguments. The first member of the pair is a boolean expression, if true the second member is the result of the case. Here's an example with numbers and colours:

... | eval range = case(errors<=0, "green", errors<10, "yellow", errors < 100, "orange", errors>=100, "red") | ...

I'm not sure what you want to do with the date, so I can't come up with a reasonable example. If you just want weeks you can use bucket:

base search | bucket Create_Date span=1w | timechart count by Create_Date

bucket will "round down" the field value to the nearest week.

0 Karma
Highlighted

Re: Timechart'ing the result of multiple searches.

Path Finder

Getting somewhere, the single quotes in the field value seem to be messing things up. So, I'm modifying the script that generates the CSV lookup table to not have the single quotes in the field values AND getting rid of any spaces in the values.

Stay tuned.

0 Karma
Highlighted

Re: Timechart'ing the result of multiple searches.

Path Finder

index=main extra.userid=* AND extra.endpoint=setplaybackposition |dedup extra.userid datemday|eval category = case(CreateDate>=2014-02-25 AND Create_Date<=2014-03-03, "week1") |timechart count by category

I don't get what I expect, I get results but, no stats or chart showing just events where the create_date falls in the range specified in the case statement.

I've cleaned up the csv file and got rid of quotations and spaces and so that's clean. A basic search works and I can get results tightened down to the date range I'm looking for. So I'm sure I'm just not getting the case() ok

0 Karma
Highlighted

Re: Timechart'ing the result of multiple searches.

SplunkTrust
SplunkTrust

Those comparisons in the case() look very weird, even wrong. Here's what I see in natural language:

Take the value of Create_Date, and compare with 2014-02-25... first, evaluate (2014 - 02 - 25) to get 1987, then compare that number with a string...

That's bound to fail. To verify, leave off the timechart at the end and check if your category values are filled as you expect.

0 Karma
Highlighted

Re: Timechart'ing the result of multiple searches.

Path Finder

Oh, I see where you're getting confused, the value of Create_Date is YYYY-MM-DD and represents the date the account was created. What I'm trying to do is create a stacked column chart showing active users for each day. The components of the column will be the week the account was created. That way I can see of the active users that day what portion were users created in week1, what component were created in week2, etc.

0 Karma
Highlighted

Re: Timechart'ing the result of multiple searches.

SplunkTrust
SplunkTrust

I'm not confused, you're confusing Splunk by telling it to subtract 4 and 25 from 2014... add double quotes around strings.

However, you might be better off using this to do weekly grouping:

base search | eval Create_Date = "Week starting ".strftime(relative_time(strptime(Create_Date, "%F"), "@w1"), "%F") | timechart count by Create_Date

Change the @w1 to @w0 if your week starts on Sunday rather than Monday.

0 Karma