topic Re: Percentage Difference between 2 indexes in Splunk Search

Percentage Difference between 2 indexes

willadams — Mon, 15 Jan 2018 07:13:18 GMT

I have 2 searches from 2 different indexes. The first search is

index="softwareimport" Product_Name="*ActiveX*"  | stats count by Product_Name

The second search is

index="device_list" device_state="Active" | stats count by device_state

How do I then do a percentage of these 2 values so that I can show a percentage value (i.e. xx%)?

I am just starting to learn how to use eval and came across something called appendcols but not sure if this is right for the context I am working in.

Re: Percentage Difference between 2 indexes

willadams — Tue, 29 Sep 2020 17:39:17 GMT

I have tried something like

index="devicelist" OR index="softwareimport" | stats count by Product_Name | stats count by Device_State
| eval percentage=Device_State/Product_Name

Re: Percentage Difference between 2 indexes

martin_mueller — Mon, 15 Jan 2018 08:09:51 GMT

This should do:

(index="softwareimport" Product_Name="*ActiveX*") OR (index="device_list" device_state="Active")
| stats count(eval(index="softwareimport")) as count_softwareimport count(eval(index="device_list")) as count_device_list
| eval percentage = count_device_list / count_softwareimport * 100

Re: Percentage Difference between 2 indexes

willadams — Mon, 15 Jan 2018 08:21:37 GMT

I see. Will try that and get back

Re: Percentage Difference between 2 indexes

mayurr98 — Mon, 15 Jan 2018 09:01:48 GMT

Hey I think you want to show percentage something like this so in addition to above query you can write something like this

(index="softwareimport" Product_Name="*ActiveX*") OR (index="device_list" device_state="Active") 
| stats count(eval(index="softwareimport")) as Total_ProductName_Count count(eval(index="device_list")) as Total_DeviceState_Count 
| eval Percentage=round((Total_DeviceState_Count*100)/Total_ProductName_Count,2)."%"

Let me know if this helps!

Re: Percentage Difference between 2 indexes

mayurr98 — Mon, 15 Jan 2018 10:34:15 GMT

Sure sir, Try and let us know we are glad to help you out!

Re: Percentage Difference between 2 indexes

willadams — Tue, 16 Jan 2018 01:29:21 GMT

Works beautifully. Thanks for the support!

Re: Percentage Difference between 2 indexes

willadams — Tue, 29 Sep 2020 17:39:48 GMT

I have experimented a bit with doing some joins to match my data between 2 indexes and then tried adapting my configuration using what I have learnt from the percentage question. So related but different. I have the following search

index="softwareimport" "Product Name"="Adobe Flash Player 28 ActiveX" OR "Product Name"="Adobe Flash Player 27 ActiveX"
| join "Device Name" [search index="devicelist" "Device State"=Active "Operating System"="Windows" AND "Operating System"!="server"]
| stats count(eval(index="softwareimport")) as count_softwareimport count(eval(index="devicelist")) as count_device
| eval "ActiveX Compliance" = round((count_softwareimport*100)/count_device,0)."%"
| table "ActiveX Compliance"

This generates a value of "0%"

What I am trying to do is get a percentage based on this configuration. If I run this search:

This returns a value of "3990".

I did then try to do the following search

index="softwareimport" "Product Name"="Adobe Flash Player 28 ActiveX" OR "Product Name"="Adobe Flash Player 27 ActiveX"
| join "Device Name" [search index="devicelist" "Device State"=Active "Operating System"="Windows" AND "Operating System"!="server"]
| stats count(eval(index="softwareimport")) as count_softwareimport count(eval([search index="devicelist" "Device State"=Active "Operating System"="Windows"])) as count_device
| eval "ActiveX Compliance" = round((count_softwareimport*100)/count_device,0)."%"
| table "ActiveX Compliance"

This ends up generating "Error in 'SearchProcessor': Mismatched quotes and/or parenthesis." even though all the quotes are matched.

Any suggestions?

Re: Percentage Difference between 2 indexes

willadams — Tue, 16 Jan 2018 14:44:27 GMT

So the first query I will always get a 0% which is not correct. On the last query I get the error. I checked and all search terms are validated by SPLUNK (i.e. AND, Eval, AND, AS) but I get either the "0" behaviour or the "mismatched quotes" despite there not being a mismatched quote that I can see.

Re: Percentage Difference between 2 indexes

mayurr98 — Tue, 16 Jan 2018 14:56:38 GMT

Hey join is by default left join so you will get the events for sure even if there is no match just that you will not able to do further analysis this is what happening with your query. You need to look into your data. What i suggest is to do not run full query. Rather run it in bits and pieces check the output of the query after every | specially after | stats count(eval(index="softwareimport")) as count_softwareimport count(eval([search index="devicelist" "Device State"=Active "Operating System"="Windows"])) as count_device this command see if you are getting proper results. Percentage is just a calculation on this set of results so if you are getting proper results then you should get your output. So just debug it at each | and see where you aint getting results .

Also,if you are not able to debug this then open a new ticket and post this question with detail description and sample events. I am sure someone from community or me would help you.

I hope this helps you!