topic Re: Create Table Group Results in Splunk Search

Create Table Group Results

caviman2201 — Mon, 21 Jul 2014 18:08:18 GMT

I have the following data:

DateTime	GroupName	Count
2014-07-14T12:00:00	Group1	15
2014-07-14T12:00:00	Group2	17
2014-07-14T12:00:00	Group3	19
2014-07-15T12:00:00	Group1	18
2014-07-15T12:00:00	Group2	20
2014-07-15T12:00:00	Group3	25
2014-07-16T12:00:00	Group1	19
2014-07-16T12:00:00	Group2	20
2014-07-16T12:00:00	Group3	25
2014-07-17T12:00:00	Group1	22
2014-07-17T12:00:00	Group2	25
2014-07-17T12:00:00	Group3	30
2014-07-18T12:00:00	Group1	25
2014-07-18T12:00:00	Group2	32
2014-07-18T12:00:00	Group3	35

What I want is to have Splunk display it like so:

Group	Monday	Tuesday	Wednesday	Thursday	Friday
Group1	15	18	19	22	25
Group2	17	20	20	25	32
Group3	19	25	25	30	35

This will only ever display 1 week's worth of data, so the width of the table isn't a concern. Thanks in advance for any assistance!

So far, I have this to pull the weekday out.

<search> | eval WeekDay=upper(substr(date_wday,1,1)).substr(date_wday,2)

Re: Create Table Group Results

lguinn2 — Mon, 21 Jul 2014 18:15:53 GMT

I would do it this way

yoursearchhere
| eval Weekday = strftime(_time,"%a")
| chart first(Count) as Count by GroupName Weekday
| rename GroupName as Group

Assuming that there is only one event for each group and each day of week (that's why first works here).

Oops, just realized that this is likely to sort by the name of the day of the week, rather than what you want. So try this:

yoursearchhere
| eval Weekday = strftime(_time,"%w %a")
| chart first(Count) as Count by GroupName Weekday
| rename GroupName as Group
| rename "0 Sun" as "Sun", "1 Mon" as "Mon", "2 Tue" as "Tue", "3 Wed" as "Wed", "4 Thu" as "Thu", "5 Fri" as "Fri", "6 Sat" as "Sat"

Re: Create Table Group Results

strive — Mon, 21 Jul 2014 18:27:15 GMT

Try this

Assuming that there will be more than one Count for a day and group combination

<search> | eval WeekDay=upper(substr(date_wday,1,1)).substr(date_wday,2) | chart sum(Count) as Count by GroupName WeekDay

Re: Create Table Group Results

strive — Mon, 21 Jul 2014 18:30:05 GMT

You also need rename option as it is in lguinn's answer