Archive
Highlighted

Percentage Difference between 2 indexes

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
Highlighted

Re: Percentage Difference between 2 indexes

Contributor

I have tried something like

index="devicelist" OR index="softwareimport" | stats count by ProductName | stats count by DeviceState
| eval percentage=DeviceState/ProductName

0 Karma
Highlighted

Re: Percentage Difference between 2 indexes

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
Highlighted

Re: Percentage Difference between 2 indexes

Contributor

I see. Will try that and get back

0 Karma
Highlighted

Re: Percentage Difference between 2 indexes

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

Re: Percentage Difference between 2 indexes

SplunkTrust
SplunkTrust

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
Highlighted

Re: Percentage Difference between 2 indexes

Contributor

Works beautifully. Thanks for the support!

0 Karma
Highlighted

Re: Percentage Difference between 2 indexes

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 countsoftwareimport count(eval(index="devicelist")) as countdevice
| eval "ActiveX Compliance" = round((countsoftwareimport*100)/countdevice,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 countsoftwareimport count(eval([search index="devicelist" "Device State"=Active "Operating System"="Windows"])) as countdevice
| eval "ActiveX Compliance" = round((countsoftwareimport*100)/countdevice,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
Highlighted

Re: Percentage Difference between 2 indexes

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
Highlighted

Re: Percentage Difference between 2 indexes

SplunkTrust
SplunkTrust

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