Splunk Search

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

jujis008
Explorer

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

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!

Tags (2)
0 Karma

sundareshr
Legend

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)
0 Karma

jujis008
Explorer

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...