topic Re: How to group evaluated columns into 3 specific columns ? in Splunk Search

How to group evaluated columns into 3 specific columns ?

AditiKhare — Tue, 29 Sep 2020 15:07:46 GMT

Hi,
I am very new to splunk and wanted to know if someone can help me in groping columns fo rmy query below :
source="metric.log" sourcetype="alerts" |
eval Diff=strptime(alertTs, "%Y-%m-%d %H:%M:%S")-strpTime(tranTs, "%Y-%m-%d %H:%M:%S") |
stats count(alertId) as Total,
count(eval(Diff<60)) as Less_Than_1M,
count(eval(Diff>=60 and Diff<300)) as Within_1M_5M,
count(eval(Diff>=300)) as Greater_Than_5M|
eval percent_Less_Than_1M=((Less_Than_1M/Total)*100)|
eval percent_Within_1M_5M=((Within_1M_5M/Total)*100)|
eval percent_Greater_Than_5M=((Greater_Than_5M/Total)*100)|
stats values(Less_Than_1M) as Count_Less_Than_1min, values(percent_Less_Than_1M) as Percent_Less_Than_1min,
values(Within_1M_5M) as Count_Between_1-5min, values(percent_Within_1M_5M) as Percent_Between_1-5min,
values(Greater_Than_5M) as Count_More_Than_5min, values(percent_Greater_Than_5M) as Percent_More_Than_5min

I want to display a table like this :

Thankyou for help
Aditi

Re: How to group evaluated columns into 3 specific columns ?

woodcock — Wed, 02 Aug 2017 00:38:19 GMT

Add this to the bottom of your search:

| transpose
| rename column AS "Time Range" "row 1" AS Count
| eventstats sum(Count) AS Total
| eval Percentage = round(100*Count/Total,2)
| fields - Total

Re: How to group evaluated columns into 3 specific columns ?

DalJeanis — Wed, 02 Aug 2017 14:59:21 GMT

This version simplifies your entire search.

source="metric.log" sourcetype="alerts" 
| eval Diff=strptime(alertTs, "%Y-%m-%d %H:%M:%S")-strpTime(tranTs, "%Y-%m-%d %H:%M:%S") 
| stats count(eval(Diff<60)) as 1Less_Than_1min, 
        count(eval(Diff>=60 and Diff<300)) as 2Between_1-5min, 
        count(eval(Diff>=300)) as 3Greater_Than_5min
| rename COMMENT as "The above gets the count from the three possible timeframes. Use the names you want for the time ranges, with a one-digit number in front to set their sort order."


| rename COMMENT as "The following simulates the above search language.  User this line instead of the above for a run-anywhere test, delete it and use the above instead for your real data. "
| makeresults | eval 1Less_Than_1min = 15 , 2Between_1-5min=3,  3Greater_Than_5min=2


| rename COMMENT as "This section breaks up the three values into separate transactions, calculates the total and percentage, then gets rid of unneeded fields"
| eval junk=1
| untable junk TimeRange Count
| eventstats sum(Count) as Total
| eval Percentage=round(100*Count/Total,2)
| fields - junk - Total

| rename COMMENT as "Finally, we get rid of the number on the front that forces the sort order, and add totals at the end."
| rex mode=sed field=TimeRange "s/^\d//"
| addtotals row=f col=t labelfield=TimeRange label="Total"

With this result...

TimeRange         Count Percentage
Less_Than_1min       15   75.00  
Between_1-5min        3   15.00  
Greater_Than_5min     2   10.00  
Total                20  100.00

updated to add totals.

Re: How to group evaluated columns into 3 specific columns ?

AditiKhare — Wed, 02 Aug 2017 18:01:53 GMT

Thankyou so much for the solution. it worked. Also, can i add Total in the last row ?

Re: How to group evaluated columns into 3 specific columns ?

AditiKhare — Wed, 02 Aug 2017 18:03:55 GMT

Thankyou so much for the detailed comments and answer it worked. This is really helpful for me to understand splunk queries.

Re: How to group evaluated columns into 3 specific columns ?

DalJeanis — Wed, 02 Aug 2017 18:14:43 GMT

@AditiKhare - You are quite welcome. I added a line to the end of my code to give you your totals. you can use the same line ( modify the labelfield argument) after @woodcock's solution as well.

Re: How to group evaluated columns into 3 specific columns ?

AditiKhare — Wed, 02 Aug 2017 18:26:02 GMT

@DalJeanis - Thanks alot.. this worked too. M really thankful 🙂

Re: How to group evaluated columns into 3 specific columns ?

woodcock — Wed, 02 Aug 2017 23:37:20 GMT

Add these 2 lines:

| addtotals col=t row=f
| fillnull value="TOTAL"