topic Re: [hope someone could help me!] How can reduce records in each row in splunk table. in Splunk Search

[hope someone could help me!] How can reduce records in each row in splunk table.

jujis008 — Thu, 28 Jul 2016 10:48:07 GMT

Hi,
I was preparing a dashboard but i have some problems while generating the table. I am using sort and stats to group results, but it returns multiple records for each row after grouping, however it is acceptable if records less than 10, but sometimes returns over 20 records. so how can i reduce the records after grouping in table for each row.
Here are my queries as follows.

sourcetype="perf_log_bizx" "EVENT-"| rex field=_raw "\]\s+\[(?<comp_id>[\w]+),(?<comp_name>[\w]+),(?<schema>[\w]+\.),(?<dbpool>[\w]+),(?<user_id>[\w]+),(?<user_name>[\w]+),(?<locale>[\w]+)\]\s+(?<event_name>[\w]+-[\w]+)\s+(?P<event_id>EVENT-.+)\s+(?P<render_time>[\d]+)\s+(?P<server_time>[\d]+)\s+(?P<timems>[\d]+)\s+(?P<js_count>[\d]+)\s+(?P<css_count>[\d]+)\s+.+\]\s+(?P<call_id>[\d]+-[\d]+)\s+(?P<module_id>[\w]+)\s+(?P<page_id>[\w]+)\s+(?<page_qualifier>[\w]+)\s+\[\[(?<memory>\d+)KB\s+(?<TotalCPU>\d+)ms\s+(?<UserCPU>\d+)ms\s+(?<SystemCPU>\d+)ms\s+(?<localread>\d+)KB\s+(?<localwrite>\d+)KB\s+(?<netread>\d+)KB\s+(?<netwrite>\d+)KB\s+(?<openfile>\d+)\s+(?<opensocket>\d+)\s+\]\]" | eval realtime=round(timems/1000, 2) | where realtime>30 | eval samepage= module_id."-".page_id."-".page_qualifier| stats count(samepage) as Frequency, values(module_id) as "Module Id", values(page_id) as "Page Id", values(page_qualifier) as "Page Qualifier", values(event_id) as "Event Id", values(comp_id) as "Company Id", values(user_id) as "User Id", max(realtime) as MaximiumTime(s),  values(realtime) as End2EndTime(s), avg(realtime) as ae2e by samepage | sort 10 -MaximiumTime(s) by samepage | eval AvgE2ETime(s) = round(ae2e, 2)| table "Module Id", "Page Id", "Page Qualifier", "Company Id", "User Id", MaximiumTime(s), Frequency, AvgE2ETime(s), End2EndTime(s)

and return results as follows:

Repeat: what am i want is for example the first row in above table only show 5 or 10 records.
hope someone could help me! cross finger!

Re: [hope someone could help me!] How can reduce records in each row in splunk table.

sundareshr — Thu, 28 Jul 2016 21:41:16 GMT

Try this. I added a streamstats command to count number of events by samepage and use only event where count<=10. You can change the where to the number of events you would like to include. You can also add a sort before the streamstats to adjust the order (top 10 vs bottom 10 for example)

sourcetype="perf_log_bizx" "EVENT-"| rex field=_raw "\]\s+\[(?<comp_id>[\w]+),(?<comp_name>[\w]+),(?<schema>[\w]+\.),(?<dbpool>[\w]+),(?<user_id>[\w]+),(?<user_name>[\w]+),(?<locale>[\w]+)\]\s+(?<event_name>[\w]+-[\w]+)\s+(?P<event_id>EVENT-.+)\s+(?P<render_time>[\d]+)\s+(?P<server_time>[\d]+)\s+(?P<timems>[\d]+)\s+(?P<js_count>[\d]+)\s+(?P<css_count>[\d]+)\s+.+\]\s+(?P<call_id>[\d]+-[\d]+)\s+(?P<module_id>[\w]+)\s+(?P<page_id>[\w]+)\s+(?<page_qualifier>[\w]+)\s+\[\[(?<memory>\d+)KB\s+(?<TotalCPU>\d+)ms\s+(?<UserCPU>\d+)ms\s+(?<SystemCPU>\d+)ms\s+(?<localread>\d+)KB\s+(?<localwrite>\d+)KB\s+(?<netread>\d+)KB\s+(?<netwrite>\d+)KB\s+(?<openfile>\d+)\s+(?<opensocket>\d+)\s+\]\]" | eval realtime=round(timems/1000, 2) | where realtime>30 | eval samepage= module_id."-".page_id."-".page_qualifier |  streamstats count by samepage | where count<=10 | stats count(samepage) as Frequency, values(module_id) as "Module Id", values(page_id) as "Page Id", values(page_qualifier) as "Page Qualifier", values(event_id) as "Event Id", values(comp_id) as "Company Id", values(user_id) as "User Id", max(realtime) as MaximiumTime(s),  values(realtime) as End2EndTime(s), avg(realtime) as ae2e by samepage | sort 10 -MaximiumTime(s) by samepage | eval AvgE2ETime(s) = round(ae2e, 2)| table "Module Id", "Page Id", "Page Qualifier", "Company Id", "User Id", MaximiumTime(s), Frequency, AvgE2ETime(s), End2EndTime(s)

Re: [hope someone could help me!] How can reduce records in each row in splunk table.

jujis008 — Fri, 29 Jul 2016 03:09:05 GMT

Thanks Sundareshr!
very close to my requirement, this will only streamstats 10 events, but I want to stats all event count, only show top 10 for each row.