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
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
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.
Thankyou so much for the detailed comments and answer it worked. This is really helpful for me to understand splunk queries.
@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.
@DalJeanis - Thanks alot.. this worked too. M really thankful 🙂
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
Thankyou so much for the solution. it worked. Also, can i add Total in the last row ?
Add these 2 lines:
| addtotals col=t row=f
| fillnull value="TOTAL"