Splunk Search

performing calculations on fields from different sourcetypes

flyingpiglet
Engager

Hi
I need to calculate a sum of different counters from several sourcetypes. They are located in one index, but simple operations give no results for some reason when counters are taken from different sourcetypes. For example:

host=host1 sourcetype IN (st1,st2,st3,st4,st5,st6)
| sort 0 - _time |reverse
| eval totals=c0+c1+c2+c3
| delta totals as dtot
| timechart span=5m per_second(dtot)

works fine when c0-c3 are all from the same sourcetype. But when i add at least one counter from another sourcetype - there's no data.

Can you explain what's wrong with that? Am i violating some important constraints within splunk?  Is there a workaround for this ?

Cheers,
Alex

Labels (2)
0 Karma
1 Solution

tscroggins
Influencer

@flyingpiglet 

If you have timestamp extraction configured correctly, the _time field should equal epochtime, but if it doesn't, you assign the value directly to _time for ease of use in other commands:

host=host1 sourcetype IN (sourcetype1 sourcetype2)
| eval _time=epochtime
| bin _time span=5m
| stats values(c0) as c0 values(c1) as c1 values(c2) as c2 values(c3) as c3 values(c3) as c4 by _time
| eval totals=coalesce(c0,0)+coalesce(c1,0)+coalesce(c2,0)+coalesce(c3,0)+coalesce(c4,0)
| delta totals as dtot
| timechart fixedrange=f span=5m per_second(dtot)

The base search returns all events with host1 and source type of sourcetype1 or sourcetype2.

The first eval command assigns the value of epochtime to the field _time.

The bin command changes all _time values to the nearest 5 minute boundary: :00, :05, :10, etc. E.g. 00:23 => 00:20, 3:34 PM => 15:30.

The stats command collates the values of c0, c1, c2, c3, and c4 by _time. Note that if you multiple values of c0, c1, c2, etc. with the same epochtime value, the result will contain multi-valued fields. This example assumes you have no duplication of epochtime values at the cN level.

After stats, the events should be sorted by _time.

The second eval command sums the cN fields, defaulting to a value of 0 for null (missing) fields. You may want to additionally validate that cN is numeric.

The delta command computes the difference between totals on events 2, 3, 4, ... n. The first event will have a null dtot value.

The timechart command converts the 5 minute subtotals into per second rates. Please do validate that the combination of time bins and per_second aggregation returns the values you expect.

View solution in original post

0 Karma

tscroggins
Influencer

Hi Alex,

Your fields--c0, c1, c2, and c3--are likely in different events:

Sat Feb 20 12:47:40 EST 2021 c0=123
host=host1 sourcetype=st1

Sat Feb 20 12:47:40 EST 2021 c1=456
host=host1 sourcetype=st2

Sat Feb 20 12:47:40 EST 2021 c2=789
host=host1 sourcetype=st3

Sat Feb 20 12:47:40 EST 2021 c3=234
host=host1 sourcetype=st4

You'll need to group your events in some way to sum field values. If your events have synchronized time stamps, for example, you may be able to group by time:

host=host1 sourcetype IN (st1,st2,st3,st4,st5,st6)
| bin _time span=5m
| stats values(c0) as c0 values(c1) as c1 values(c2) as c2 values(c3) as c3 by _time
| eval totals=coalesce(c0,0)+coalesce(c1,0)+coalesce(c2,0)+coalesce(c3,0)
| delta totals as dtot
| timechart fixedrange=f span=5m per_second(dtot)

There are many other ways to aggregate/group events. The chart, stats, timechart, and tstats commands are usually the most efficient. The transaction command is very flexible, but it doesn't scale over large result sets.

-Trev

0 Karma

flyingpiglet
Engager

Thank you , Trev

I feel "the truth is somwhere near" but still not clear for me. For example, i have common field for every sourcetype called epochtime (event timestamp actually). I also have for example two sourcetypes with c0,c1,c2,c3 in sourcetype1 and c4 in sourcetype2. Now can you repeat the example using common field epochtime to collate the events?
Sorry - that all staff is quite difficult from the very beginning.

0 Karma

flyingpiglet
Engager

Ha,

that works
host=host1 sourcetype IN (cisco:bulkstats:cp:mmeSch13,cisco:bulkstats:cp:mmeSch14)
| bin _time span=5m
| stats values(epsattach_imsi_success) as epsattach_imsi_success values(combinedattach_imsi_success) as combinedattach_imsi_success by _time
| eval totals=coalesce(epsattach_imsi_success,0)+coalesce(combinedattach_imsi_success,0)
| delta totals as dtot
| timechart span=5m per_second(dtot)

Thanks a lot

0 Karma

tscroggins
Influencer

@flyingpiglet 

If you have timestamp extraction configured correctly, the _time field should equal epochtime, but if it doesn't, you assign the value directly to _time for ease of use in other commands:

host=host1 sourcetype IN (sourcetype1 sourcetype2)
| eval _time=epochtime
| bin _time span=5m
| stats values(c0) as c0 values(c1) as c1 values(c2) as c2 values(c3) as c3 values(c3) as c4 by _time
| eval totals=coalesce(c0,0)+coalesce(c1,0)+coalesce(c2,0)+coalesce(c3,0)+coalesce(c4,0)
| delta totals as dtot
| timechart fixedrange=f span=5m per_second(dtot)

The base search returns all events with host1 and source type of sourcetype1 or sourcetype2.

The first eval command assigns the value of epochtime to the field _time.

The bin command changes all _time values to the nearest 5 minute boundary: :00, :05, :10, etc. E.g. 00:23 => 00:20, 3:34 PM => 15:30.

The stats command collates the values of c0, c1, c2, c3, and c4 by _time. Note that if you multiple values of c0, c1, c2, etc. with the same epochtime value, the result will contain multi-valued fields. This example assumes you have no duplication of epochtime values at the cN level.

After stats, the events should be sorted by _time.

The second eval command sums the cN fields, defaulting to a value of 0 for null (missing) fields. You may want to additionally validate that cN is numeric.

The delta command computes the difference between totals on events 2, 3, 4, ... n. The first event will have a null dtot value.

The timechart command converts the 5 minute subtotals into per second rates. Please do validate that the combination of time bins and per_second aggregation returns the values you expect.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

eval is adding fields from the same event - when the events come from different source types, some of the fields may not be present in the event. In order to add the fields, you need to collate them into the same event. This could be done with the stats command if the events from the different source types have a common field with a common value.

flyingpiglet
Engager

I probably understand what you mean.

All events from the sourcetypes have common field - epochtime. This is unix timestamp showing when the event occured. All the events having the same epochtime (despite the sourcetype) are simultaneous.

Having said that i stil not sure i understand how i can use that.

0 Karma
Get Updates on the Splunk Community!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...