Splunk Search

How to group evaluated columns into 3 specific columns ?

AditiKhare
Explorer

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 :
alt text

Thankyou for help
Aditi

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

DalJeanis
Legend

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.

AditiKhare
Explorer

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

DalJeanis
Legend

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

AditiKhare
Explorer

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

0 Karma

woodcock
Esteemed Legend

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

AditiKhare
Explorer

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

0 Karma

woodcock
Esteemed Legend

Add these 2 lines:

| addtotals col=t row=f
| fillnull value="TOTAL"
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...