Splunk Search

Percentage Difference between 2 indexes

willadams
Contributor

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.

Tags (1)
0 Karma
1 Solution

mayurr98
Super Champion

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!

View solution in original post

0 Karma

willadams
Contributor

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:

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"!="server"]
| stats count AS "ActiveX Current"

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?

0 Karma

willadams
Contributor

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.

0 Karma

mayurr98
Super Champion

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!

0 Karma

mayurr98
Super Champion

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!

0 Karma

willadams
Contributor

Works beautifully. Thanks for the support!

0 Karma

willadams
Contributor

I see. Will try that and get back

0 Karma

mayurr98
Super Champion

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

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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
0 Karma

willadams
Contributor

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...