topic Re: Calculate percentage between 2 reports in Splunk Search

Calculate percentage between 2 reports

jip31 — Mon, 02 Jul 2018 17:13:43 GMT

Hello

I use 2 reports with the code below

index="windows-wmi" sourcetype="wmi:DiskRAMLoad" host="$field1$" (Name="mfetp.exe" OR Name="mcshield.exe") Name=$Service$ | head 10 | table _time Name host Name ReadOperationCount ReadTransferCount WriteOperationCount  WriteTransferCount | eval _time = strftime(_time, "%Y-%m-%d %H:%M") | stats values(Name) as Name, avg(ReadOperationCount), avg(ReadTransferCount), avg(WriteOperationCount), avg(WriteTransferCount) BY host | rename avg(ReadOperationCount) as ReadOperation_AVG, avg(ReadTransferCount) as ReadTransfer_AVG, avg(WriteOperationCount) as WriteOperation_AVG, avg(WriteTransferCount) as WriteTransfer_AVG

On the first report, i get the value corresponding to Name="mfetp.exe" and on the second, i get the value corresponding to Name="mcshield.exe")
With the values of the 2 reports, i have to calculate the percentage between the values
example : i want to obtain the percentage between avg(ReadOperationCount) from the first report and avg(ReadOperationCount) from the second report
have you any idea how to do please??

Re: Calculate percentage between 2 reports

niketn — Mon, 02 Jul 2018 17:50:37 GMT

@jip31, a lot of cleanup seems to be required in your code. Can you please try the following search first?

index="windows-wmi" sourcetype="wmi:DiskRAMLoad" host="$field1$" Name="mfetp.exe" 
| head 10 
| stats avg(ReadOperationCount) as mfetp_ReadOperation_AVG, avg(ReadTransferCount) as mfetp_ReadTransfer_AVG, avg(WriteOperationCount) as mfetp_WriteOperation_AVG, avg(WriteTransferCount) as mfetp_WriteTransfer_AVG 
| appendcols 
    [ search index="windows-wmi" sourcetype="wmi:DiskRAMLoad" host="$field1$" Name="mcshield.exe" 
    | head 10 
    | stats avg(ReadOperationCount) as mcshield_ReadOperation_AVG, avg(ReadTransferCount) as mcshield_ReadTransfer_AVG, avg(WriteOperationCount) as mcshield_WriteOperation_AVG, avg(WriteTransferCount) as mcshield_WriteTransfer_AVG] 
| eval percReadOperation_AVG=round((mfetp_ReadOperation_AVG/mcshield_ReadOperation_AVG)*100,2),
    percReadTransfer_AVG=round((mfetp_ReadTransfer_AVG/mcshield_ReadTransfer_AVG)*100,2),
    percWriteOperation_AVG=round((mfetp_WriteOperation_AVG/mcshield_WriteOperation_AVG)*100,2),
    percWriteTransfer_AVG=round((mfetp_WriteTransfer_AVG/mcshield_WriteTransfer_AVG)*100,2)

PS: I have used ratio*100 for percent. However, you can use your own formula.
Also since you are using tokens like host="$field1$", is it fair to assume that your reports for mfetp.exe and mcshield.exe are actually dashboard panels? If they are then besides appendcols you have another option to use search event handler. For example you can code the <done> search event handler to get the predefined token $result.<yourSearchResultFieldName>$ to access specific field name. Refer to one of older answer to adopt to your use case: https://answers.splunk.com/answers/613939/how-to-calculate-percentage-based-on-2-different-s.html

Re: Calculate percentage between 2 reports

somesoni2 — Mon, 02 Jul 2018 17:51:35 GMT

How many rows does your report generates? (or in other words, how many hosts would be there in a report). What columns should your final (expected) query should show?

Re: Calculate percentage between 2 reports

jip31 — Mon, 02 Jul 2018 18:34:49 GMT

hello niletnilnay

It will be almost perfect but there is a misunderstoog
the percentage has to be calculated not between mfetp and mcshield but between mfetp and meftp or between mcshield and mcshield
i m explaining : i want to compare between 2 machines (host) the percentage of disk and memory used for a specifique service (mfetp or mcshield)
do you understand what i mean?
is it possible to adapt the code please?
i need also something else : if the result of the percentage is negative (value of first mfetp field > value of second mfetp field) i want for example to display +5% and if the result of the percentage is positive (value of first mfetp field < value of second mfetp field) i want to display -5%
could you help me please???
many thanks

Re: Calculate percentage between 2 reports

jip31 — Tue, 29 Sep 2020 20:18:50 GMT

so i have done this:

index="windows-wmi" sourcetype="wmi:DiskRAMLoad" Name="mfetp.exe"
| head 10
| stats avg(ReadOperationCount) as mfetp_ReadOperation_AVG, avg(ReadTransferCount) as mfetp_ReadTransfer_AVG, avg(WriteOperationCount) as mfetp_WriteOperation_AVG, avg(WriteTransferCount) as mfetp_WriteTransfer_AVG
| appendcols
[ search index="windows-wmi" sourcetype="wmi:DiskRAMLoad" Name="mfetp.exe"
| head 10
| stats avg(ReadOperationCount) as mfetp_ReadOperation_AVG2, avg(ReadTransferCount) as mfetp_ReadTransfer_AVG2, avg(WriteOperationCount) as mfetp_WriteOperation_AVG2, avg(WriteTransferCount) as mfetp_WriteTransfer_AVG2]
| eval percReadOperation_AVG=round((mfetp_ReadOperation_AVG/mfetp_ReadOperation_AVG2)*100,2),
percReadTransfer_AVG=round((mfetp_ReadTransfer_AVG/mfetp_ReadTransfer_AVG2)*100,2),
percWriteOperation_AVG=round((mfetp_WriteOperation_AVG/mfetp_WriteOperation_AVG2)*100,2),
percWriteTransfer_AVG=round((mfetp_WriteTransfer_AVG/mfetp_WriteTransfer_AVG2)*100,2)

but there is an issue somewhere because the result is a big number even if i do the test in my local pc
so it means for example that i divise 50/50 so the increase is 0%..... What can i do please??