Working with subsearch. How to reduce output results.

[nik_splunk]

Good morning Splunkers,

I'm working on the search detailed below.

By using two subsearches I'm trying to identify top 5 MY_GROUP's members and also top 5 hosts, both of them evaluated by counted LOGINS.

I would like to chart results in a "column table" . I'm using those two subsearches because all of members of MY_GROUP often visits more that 20 hosts and this impact on the density of informations presented in the final chart.

Does anybody know a way to make that code simpler and more efficient?

Thanks all in advance.

p.s. whether I run that search using a startmonthsago=6 instead of last 6 days (I know, it could be smarter to use a summary index, but i need it sporadically)...results are strange: as the two subsearches exit with some partial results. Is that possible in your opinion?

This is the search:

([search tag=LOGIN startdaysago=7 tag="MY_GROUP" | stats count by User_Name |stats sum(count) as Total by User_Name | sort -Total | top limit=5 User_Name |fields User_Name] [ search tag=LOGIN startdaysago=6 tag="MY_GROUP" | stats count by host |stats sum(count) as Total by host | sort -Total| top limit=5 host |fields host]) | chart count(host) by User_Name,host"

---

Thanks gkanapathy for your support.

What I need is a chart where I get over the X-axis the TOP 5 user for logins realized in the last 6 days; on the Y-axis just count of logins. For each of those TOP 5 users, grouped by their name, I wanna show only those of the top 5 server each user has visited in the rangetime.

Thanks...

Ciao

nik

---

...i just finished to try, and results arent those Im searching for. Let say

Ausr,Busr,Cusr,Dusr, Eusr, Fusr, Gusr are member of MY_GROUP

TOP 5 user by their login during last 6 days are :

Ausr,Busr,Cusr,Dusr, Gusr.

1srv, 2srv, 3srv, 4srv, 5srv, 6srv, 7srv, ... 20srv... 50srv...2000srv are servers into my network (and consider each of users access 10-15 servers every week)

TOP 5 server by counted login during last 6 days are :

1srv, 2srv, 3srv, 4srv, 7srv,

What I need is a chart where the output is: EXAMPLE. for those top5 users

Ausr = 1srv, 3srv, 4srv, 7srv,

Busr = 1srv, 2srv, 3srv, 7srv,

Cusr = 1srv, 2srv, 3srv

Dusr = 4srv, 7srv

Gusr = 1srv, 3srv, 7srv

If I use tag=LOGIN tag="MY_GROUP" | stats count by User_Name host

I will get splitted rows... so I cannot control TOP5 users

If I use

**tag=LOGIN tag="MY_GROUP" | chart count over User_Name by host | **

again I loose the control of the output...because all the results come out.

...

Tks again....

[sideview · Accepted solution]

I think you're trying to do something relatively simple, but you've gotten to an incredibly complicated place somehow with all those stats commands. And I dont think subsearches are of any use to you.

Are either of these much simpler searches closer to what you want?

tag=LOGIN tag="MY_GROUP" | chart count over User_Name by host

tag=LOGIN tag="MY_GROUP" | stats count by User_Name host

[nik_splunk]

Hi nick,

thanks a lot for your time and support. I'm impressed by the professionalism of all there (supporters, moderators, developers and so on).
Splunk is a great product!

[gkanapathy]

This is not how subsearch works. Subsearch results are provided as query terms to the parent search, not as results that are given back. Multiple subsearches simply provide more query terms.

It is not clear to me what exactly you want? Is is simply the display of each of your subsearches, listed side by side in one table instead of in separate tables? Is that it? You could look at the join, append, or appendcols search commands, but which one makes sense, or whether you need a subsearch at all (which you might not) would depend exactly what you're trying to do.

(Also, if your subsearches are doing what I think you are trying to do, you should not be using both stats count ... | sort ... and top. One or the other, not both.)