Splunk Search

TimeChart implemention over two grouping

jayaraj1717
New Member

i have log file as below need to calculate Execution time for each events and dispay data by grouping with Errorcode and hostnames.

LogTime=2018-05-14T00:47:00.424-0700|Host=hostname1|ErroCode=00|ExcutionTime=2018-05-14 00:47:00.219|Instname=hosnatname1:process1:<date>:<Uniquenumber>
LogTime=2018-05-14T00:47:00.424-0700|Host=hostname1|ErroCode=01|ExcutionTime=2018-05-14 00:47:00.219|Instname=hosnatname1:process2:<date>:<Uniquenumber>
LogTime=2018-05-14T00:47:00.424-0700|Host=hostname2|ErroCode=00|ExcutionTime=2018-05-14 00:47:00.219|Instname=hosnatname2:process1:<date>:<Uniquenumber>
LogTime=2018-05-14T00:47:00.424-0700|Host=hostname3|ErroCode=00|ExcutionTime=2018-05-14 00:47:00.219|Instname=hosnatname3:process1:<date>:<Uniquenumber>
LogTime=2018-05-14T00:47:00.424-0700|Host=hostname2|ErroCode=01|ExcutionTime=2018-05-14 00:47:00.219|Instname=hosnatname2:process2:<date>:<Uniquenumber>
LogTime=2018-05-14T00:47:00.424-0700|Host=hostname2|ErroCode=03|ExcutionTime=2018-05-14 00:47:00.219|Instname=hosnatname2:process1:<date>:<Uniquenumber>
LogTime=2018-05-14T00:47:00.424-0700|Host=hostname1|ErroCode=00|ExcutionTime=2018-05-14 00:47:00.219|Instname=hosnatname1:process1:<date>:<Uniquenumber>
LogTime=2018-05-14T00:47:00.424-0700|Host=hostname4|ErroCode=01|ExcutionTime=2018-05-14 00:47:00.219|Instname=hosnatname4:process2:<date>:<Uniquenumber>
LogTime=2018-05-14T00:47:00.424-0700|Host=hostname4|ErroCode=00|ExcutionTime=2018-05-14 00:47:00.219|Instname=hosnatname4:process1:<date>:<Uniquenumber>

i need data like every 15 minutes what is the avg Execution for each ErrorCode on each hosts

-_time ExecutionTime ErrorCode Host
2018-05-14 00:00:00 avg(<< subrataion of LogTime - ScripExcutionTime>>) 00 Hostname1
2018-05-14 00:15:00 avg(<< subrataion of LogTime - ScripExcutionTime>>) 01 Hostname1
2018-05-14 00:30:00 avg(<< subrataion of LogTime - ScripExcutionTime>>) 00 Hostname2
2018-05-14 00:45:00 avg(<< subrataion of LogTime - ScripExcutionTime>>) 01 Hostname2
2018-05-14 01:00:00 avg(<< subrataion of LogTime - ScripExcutionTime>>) 03 Hostname2
2018-05-14 01:15:00 avg(<< subrataion of LogTime - ScripExcutionTime>>) 00 Hostname3
2018-05-14 01:30:00 avg(<< subrataion of LogTime - ScripExcutionTime>>) 01 Hostname4
2018-05-14 01:45:00 avg(<< subrataion of LogTime - ScripExcutionTime>>) 02 Hostname4
0 Karma

FrankVl
Ultra Champion

You can't have more than 1 field in the BY clause of a timechart command unfortunately. What you can do, is concatenate the 2 fields into 1 before applying the timechart command. So for example:

| eval hostErrorCode = host."_".ErrorCode | timechart avg(ExecutionTime) by hostErrorCode

Calculating the Executiontime can be done by first converting the ScripExcutionTime to a timestamp value and then subtracting it from _time (assuming log time is already captured in _time, otherwise do a similar conversion for log time as well).

| eval ScripExcutionTime = strptime(ScripExcutionTime,"%Y-%m-%d %H:%M:%S.%3Q")
0 Karma

jayaraj1717
New Member

i have 12 host and 10 plus ErrorCode. only few host are returning and remaining are moving to OTHER. same happening for ErrCode. only few's are coming.
_time OTHER XXXXXXXXXX3_00 XXXXXXXXX4_00 XXXXXXXXX3_10 XXXXXXXXX6_00 XXXXXXXXX7_00 XXXXXXXXX9_00 XXXXXXXXX0_00 XXXXXXXXX8_00 XXXXXXXXX8_10 XXXXXXXXX0_00
2018-05-06 00:00:00 0.169741 0.204697 0.204866 0.204312 0.204415 0.201709 0.171336 0.173867 0.170226 0.169900 0.168801
2018-05-06 00:15:00 0.170710 0.209113 0.205724 0.205761 0.205966 0.205338 0.174896 0.175960 0.172567 0.172088 0.172196
2018-05-06 00:30:00 0.169880 0.211389 0.207215 0.206823 0.203674 0.203884 0.183361 0.179539 0.169477 0.173195 0.171637

0 Karma

FrankVl
Ultra Champion

Add a limit=0 to your timechart command, to keep all values instead of having them aggregated into OTHER.

Alternatively you could look at the xyseries, which I think should allow you to have multiple fields in the by clause, but doesn't give you the nice continuous timerange that timechart does.

Or take the effort to create a dashboard with 12 manually constructed panels, one for each host, such that each graph itself only shows the executiontime by errorcode. For 12 hosts that is still fairly feasible.

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!