topic Re: How to sort and group data by day and ID? in Splunk Search

How to sort and group data by day and ID?

Yaichael — Mon, 01 May 2017 20:47:32 GMT

I would like to display the events as the following:

where it is grouped and sorted by day, and sorted by ID numerically (after converting from string to number). I have only managed to group and sort the events by day, but I haven't reached the desired result.

Any better approach?

Thanks!

Re: How to sort and group data by day and ID?

somesoni2 — Mon, 01 May 2017 21:02:17 GMT

I'm not sure if the two level grouping is possible (group by Date and Group by num, kind of excel type merging/grouping). You may be able to achieve this.

Dates ID Names Count total
Date1 num1 ABC 10 100
           DEF 90
Date1 num2 XYZ 20 50
           PQR 30

If you can post your current query, I can update it to provide above format

Re: How to sort and group data by day and ID?

Yaichael — Mon, 01 May 2017 21:21:31 GMT

Thanks for the reply, someoni2.

Here's my current query:

(...)
| rex mode=sed field=amount "s/[^\d\.-]//g"
| bucket _time span=1d
| stats count by _time id name amount
| rename id as IDs
| stats values(_time) as Dates values(name) as Names list(count) as "Count by Name" sum(amount) AS total by IDs
| eval Total = "$".total
| rename Total as "Total Amount"
| fields - total
| sort - Dates
| convert timeformat="%m/%d/%Y" ctime(Dates)

Re: How to sort and group data by day and ID?

somesoni2 — Mon, 01 May 2017 21:36:10 GMT

Give this a try

(...)
 | rex mode=sed field=amount "s/[^\d\.-]//g"
 | bucket _time span=1d
 | stats count by _time id name amount
 | rename id as IDs
 | stats list(name) as Names list(count) as "Count by Name" sum(amount) as "Total Amount" by _time IDs
 | eval "Total Amount" = "$".'Total Amount'
 | eval Date=strftime(_time,"%m/%d/%Y") | fields - _time

Re: How to sort and group data by day and ID?

Yaichael — Mon, 01 May 2017 21:48:10 GMT

Thanks. I forgot to put Names as the following:

| stats values(name) as Names (...)

because I would like to have the sum of each unique value of it in: list(count).

Re: How to sort and group data by day and ID?

DalJeanis — Mon, 01 May 2017 23:57:18 GMT

Something is very strange about your query. Why are you grouping by amount? Is the amount really always $1? If so, you wouldn't have to group by it. If not, then you probably wouldn't want to group by it.

This is similar to one strategy that I use...

| rex mode=sed field=amount "s/[^\d\.-]//g"
| bucket _time span=1d
| stats count as myCount sum(amount) as myAmount by _time id name
| rename id as IDs
| eval NameCount = name." ----- ".myCount
| stats values(NameCount) as NameCount, sum(myCount) as Count, sum(myAmount) as Total by _time IDs    
| rename COMMENT as "Tne above puts Name and the related Count together line by line into a single multivalue field."

| eval Total = "$".ROUND(Total,2)
| rename Total as "Total_Amount"
| rename NameCount as "Name_____Count_By_Name"
| eval Dates = _time
| sort 0 Dates
| convert timeformat="%m/%d/%Y" ctime(Dates)

Re: How to sort and group data by day and ID?

Yaichael — Tue, 02 May 2017 16:34:19 GMT

Thanks for the help.

How can I split the total amount by counted names?

Re: How to sort and group data by day and ID?

Yaichael — Tue, 02 May 2017 17:59:44 GMT

(...)
| rex mode=sed field=amount "s/[^\d\.-]//g"
| bucket _time span=1d
| stats count as myCount sum(amount) as myAmount by _time id name
| rename id as IDs
| eval NameCount = name." ----- ".myCount
| eval TotalCount = name." ----- $".myAmount
| stats values(NameCount) as NameCount, sum(myCount) as Count, values(TotalCount) as TotalCount , sum(myAmount) as Total by _time IDs
| eval Total = "$".ROUND(Total,2)
| rename Total as "Total Amount" TotalCount as "Amount by Name"
| rename NameCount as "Names ----- Count by Name"

Re: How to sort and group data by day and ID?

DalJeanis — Tue, 02 May 2017 18:43:45 GMT

Yes, that should work. you might want to format the Amount by name...

| eval TotalCount = name." ----- $".Round(myAmount,2)

I would tend to put each count and amount into a single field, and align them

(...)
| rex mode=sed field=amount "s/[^\d\.-]//g"
| bucket _time span=1d
| stats count as myCount sum(amount) as myAmount by _time id name
| eval namelen=len(name), amtlen=len(tostr(round(myAmount,2))), countlen=len(tostr(count))
| eventstats max(namelen) as maxname, max(amtlen) as maxamt,max(countlen) as maxcount
| eval biglongspacestring="                             "
| eval namespacer = substr(biglongspacestring,1,1+maxname-namelen)
| eval amtspacer = substr(biglongspacestring,1,1+maxamt-amtlen)
| eval countspacer = substr(biglongspacestring,1,1+maxcount-countlen)
| eval NameCount = name." ".namespacer." ".myCount." ".countspacer.amtspacer." $".tostr(round(myAmount,2))
| stats values(NameCount) as NameCount, sum(myCount) as Count, sum(myAmount) as Total by _time IDs
| eval Total = "$".round(Total,2)
| rename Total as "Total Amount" 
| rename NameCount as "Per Name-----Count-----Amount"