Can you help me with my tstats sub query?

[griggsy]

Hello,

I have a tstats query that works really well. However, I am trying to add a sub search to it to attempt to identify a user logged into the machine. Here is what I am trying to do:

| tstats summariesonly=t count as Count, dc(fw.rule) as dc_rules, values(fw.rule) as rules, max(_time) as LastSeen, values(fw.dest_ip) as Destination FROM datamodel=Firewall.fw WHERE fw.dest_ip = 8.8.4.4 OR fw.dest_ip = 8.8.8.8 AND fw.action = "blocked" BY fw.src_ip, fw.action | rename src_ip as src_host | join src_host [ search index=proxy | fields src_host,UserName] | table src_host,Destination,action,UserName,Count

The proxy index is a heavy index with lots of data ideally I would like to set another the search to only look for traffic from src_host and then return the username. Anyone have any ideas how I can A.) improve the search and B get it to work 🙂

Many thanks!

[whrg]

I believe the map command can help you here:

The map command is a looping operator that runs a search repeatedly for each input event or result.

So try replacing your join command with something like this:

| map search="search index=proxy src_host=$src_host$ | stats count by src_host,UserName"

[jeet3007]

Solution 1: (Easier way)

I would suggest using a summary index rather than using the proxy index directly. I would setup a scheduled search that will keep on writing to a summary index and then use that in the join subsearch. Something like this:

| tstats summariesonly=t count as Count, dc(fw.rule) as dc_rules, values(fw.rule) as rules, max(_time) as LastSeen, values(fw.dest_ip) as Destination FROM datamodel=Firewall.fw WHERE fw.dest_ip = 8.8.4.4 OR fw.dest_ip = 8.8.8.8 AND fw.action = "blocked" BY fw.src_ip, fw.action | rename src_ip as src_host | join src_host [ search index= sourcetype= | fields src_host,UserName] | table src_host,Destination,action,UserName,Count

Solution 2 (Not so straightforward):

Second solution is where you use the tstats in the inner query. But as you may know tstats only works on the indexed fields. So in this solution you can make src_host and UserName as indexed fields that are extracted index time (Writing a transform to keep it simply). Then using these fields using the tstats

| tstats summariesonly=t count as Count, dc(fw.rule) as dc_rules, values(fw.rule) as rules, max(_time) as LastSeen, values(fw.dest_ip) as Destination FROM datamodel=Firewall.fw WHERE fw.dest_ip = 8.8.4.4 OR fw.dest_ip = 8.8.8.8 AND fw.action = "blocked" BY fw.src_ip, fw.action | rename src_ip as src_host | join src_host [ | tstats count where index=proxy sourcetype= by src_host, UserName | fields src_host,UserName] | table src_host,Destination,action,UserName,Count

2nd one is relatively long term solution.

Note: Adding index time fields has some trade-offs and I would consider checking the indexing rates and other performance parameters before doing it.

[whrg]

How about using a scheduled search to cache all combinations for src_host and UserName from the proxy index? This scheduled search could run every 30 minutes or so. Your query above could then access this saved search.

[griggsy]

Unfortunately due to the amount of data it would be hard to do this - I would ideally like to do a search of just the last 15 mins of that index.