Calculating sum from 2 tstats

[klaudiac]

Hi guys,
I'm hoping for a bit of a help.
My total_bytes and src_zone aren't populating. I tried few things at groupby stage both in stats and tstats, At this point I'm running out of ideas on how to fix it. Can you please have a look at it?

| tstats summariesonly=t prestats=t latest(_time) as _time values(All_Traffic.user) as All_Traffic.user, values(All_Traffic.dest_translated_ip) as All_Traffic.dest_translated_ip, values(All_Traffic.rule) as All_Traffic.rule, values(All_Traffic.src_zone) as All_Traffic.src_zone values(All_Traffic.dest_zone) as All_Traffic.dest_zone, values sum(All_Traffic.bytes) AS All_Traffic.bytes values(sourcetype) as sourcetype count from datamodel=Network_Traffic where (nodename = All_Traffic) NOT (index="zscaler") All_Traffic.src_ip="10.24.224.12" All_Traffic.dest_ip="213.52.102.12" groupby All_Traffic.src_ip All_Traffic.dest_ip All_Traffic.action All_Traffic.app 

| tstats summariesonly=t prestats=t append=t latest(_time) as _time values(Web.user) as Web.user, sum(Web.bytes) AS Web.bytes values(sourcetype) as sourcetype count from datamodel=Web where (nodename = Web) Web.src="10.24.224.12" Web.dest_ip="213.52.102.12" groupby Web.src Web.dest_ip Web.action Web.url Web.app


| eval src=case(isnotnull('All_Traffic.src_ip'), 'All_Traffic.src_ip', isnotnull('Web.src'), 'Web.src')
| eval dest=case(isnotnull('All_Traffic.dest_ip'), 'All_Traffic.dest_ip', isnotnull('Web.dest_ip'), 'Web.dest_ip')
| eval action=case(isnotnull('All_Traffic.action'), 'All_Traffic.action', isnotnull('Web.action'), 'Web.action')

| eval All_Traffic_url="N/A"
| eval app=case(isnotnull('All_Traffic.app'), 'All_Traffic.app', isnotnull('Web.app'), 'Web.app')

| stats latest(_time) as _time values(All_Traffic_url) as All_Traffic_url values(All_Traffic.app) as All_Traffic.app values(Web.app) as Web.app values(Web.user) as Web.user, values(All_Traffic.user) as All_Traffic.user, values(All_Traffic.dest_translated_ip) as dest_translated_ip, values(All_Traffic.dest_zone) as dest_zone, values(All_Traffic.src_zone) as src_zone values(All_Traffic.rule) as rule, sum(All_Traffic.bytes) AS All_Traffic.bytes, sum(Web.bytes) AS Web.bytes, values(sourcetype) as sourcetype, count by src dest action app Web.url 

| eval user=case(isnotnull('All_Traffic.user'), 'All_Traffic.user', isnotnull('Web.user'), 'Web.user')
| eval url=case(isnotnull('Web.url'), 'Web.url', isnotnull(All_Traffic_url), All_Traffic_url)

| stats latest(_time) as _time values(user) as user, values(All_Traffic.dest_translated_ip) as dest_translated_ip, values(All_Traffic.src_zone) as src_zone, values(All_Traffic.dest_zone) as dest_zone, values(All_Traffic.rule) as rule, sum(All_Traffic.bytes) AS All_Traffic.bytes, sum(Web.bytes) AS Web.bytes,  values(sourcetype) as sourcetype count by src dest action app url 

| fillnull value=0 All_Traffic.bytes, Web.bytes
| eval total_bytes='All_Traffic.bytes'+'Web.bytes'
| eval total_bytes=tostring(total_bytes/1024/1024, "commas") + " MB"


| fillnull value="N/A" src, src_zone, dest, dest_dns, dest_translated_ip_dns, dest_zone, action, app, rule, user
| fields _time, sourcetype src, src_zone, dest, dest_dns, dest_translated_ip_dns, dest_zone, action, app, rule, user, total_bytes, count, url

Thanks in advance! 

[inventsekar]

| eval total_bytes='All_Traffic.bytes'+'Web.bytes'

the macro's "All_Traffic.bytes" and "Web.bytes" -- are they working fine?

Go to Settings>Advanced Search>Search Macros> you should see the Name of the macro and search associated with it in the Definition field and the App macro resides/used in.

and not sure, but, maybe, try...(its better to use different field names than the splunk's default field names)

values(All_Traffic.src_zone) as SrcZones

values(sourcetype) as SourceTypes 

[klaudiac]

Hi,

These are not macros 😕 although they do look like it... These are just single ticks ' instead of `
I got the original from my work colleague and the working search was looking like this and all was working fine:

| tstats summariesonly=t prestats=t latest(_time) as _time values(All_Traffic.user) as All_Traffic.user, values(All_Traffic.app) as All_Traffic.app, values(All_Traffic.dest_translated_ip) as All_Traffic.dest_translated_ip, values(All_Traffic.rule) as All_Traffic.rule, values(All_Traffic.src_zone) as All_Traffic.src_zone, values(All_Traffic.dest_zone) as All_Traffic.dest_zone, values sum(All_Traffic.bytes) AS All_Traffic.bytes values(sourcetype) as sourcetype count from datamodel=Network_Traffic where (nodename = All_Traffic) NOT (index="zscaler") All_Traffic.src_ip="10.24.224.12" All_Traffic.dest_ip="213.52.102.12" groupby All_Traffic.src_ip All_Traffic.dest_ip All_Traffic.action
| tstats summariesonly=t prestats=t append=t latest(_time) as _time values(Web.user) as Web.user, values(Web.app) as Web.app, sum(Web.bytes) AS Web.bytes first(Web.url) as Web.url values(sourcetype) as sourcetype count from datamodel=Web where (nodename = Web) Web.src="10.24.224.12" Web.dest_ip="213.52.102.12" groupby Web.src Web.dest_ip Web.action


| eval src=case(isnotnull('All_Traffic.src_ip'), 'All_Traffic.src_ip', isnotnull('Web.src'), 'Web.src')
| eval dest=case(isnotnull('All_Traffic.dest_ip'), 'All_Traffic.dest_ip', isnotnull('Web.dest_ip'), 'Web.dest_ip')
| eval action=case(isnotnull('All_Traffic.action'), 'All_Traffic.action', isnotnull('Web.action'), 'Web.action')


| stats latest(_time) as _time values(Web.user) as Web.user, values(All_Traffic.user) as All_Traffic.user, values(All_Traffic.dest_translated_ip) as dest_translated_ip, values(All_Traffic.dest_zone) as dest_zone, values(All_Traffic.src_zone) as src_zone, values(All_Traffic.rule) as rule, values(All_Traffic.app) as All_Traffic.app, values(Web.app) as Web.app, sum(All_Traffic.bytes) AS All_Traffic.bytes, sum(Web.bytes) AS Web.bytes, values(Web.url) as url, values(sourcetype) as sourcetype, count by src dest action


| eval app=case(isnotnull('All_Traffic.app'), 'All_Traffic.app', isnotnull('Web.app'), 'Web.app')
| eval user=case(isnotnull('All_Traffic.user'), 'All_Traffic.user', isnotnull('Web.user'), 'Web.user')
| where isnull(user) OR isnotnull(user)
| where isnull(app) OR isnotnull(app)
| where isnull(action) OR isnotnull(action)

| fillnull value=0 All_Traffic.bytes, Web.bytes
| eval total_bytes='All_Traffic.bytes'+'Web.bytes'
| eval total_bytes=tostring(total_bytes/1024/1024, "commas") + " MB"
| `get_asset(dest)`
| `get_asset(dest_translated_ip)`
| fillnull value="N/A" src, src_zone, "source location", dest, dest_dns, dest_translated_ip, dest_translated_ip_dns, dest_zone, action, app, rule, user
| fields _time, sourcetype src, src_zone, dest, dest_translated_ip, dest_zone, action, app, rule, user, malicious, flow, All_Traffic.bytes, Web.bytes, total_bytes, count, url

I had to twitch it a bit so it shows results with individual URLs as opposed to 1 result with a list of URLs in the last column. So once I added that part, the other 2 stopped working