Installation

Can you help me improve the performance of the following query?

jip31
Motivator

Hello

I would like to optimize performances with my query below .

I also try to display "mAH" unit for FullChargedCapacity, but it didn't work.

Could you help me please??

index="windows-wmi" OR sourcetype="wmi:BatteryStatic" 
| stats first(FullChargedCapacity) AS FullChargedCapacity first(DesignedCapacity) AS DesignedCapacity first(_time) AS _time BY host 
| eval time = strftime(_time, "%m/%d/%Y %H:%M") 
| eval FullChargedCapactity = (FullChargedCapactity." mAH") 
| eval Wear_Rate = (100-(FullChargedCapacity *100/DesignedCapacity))." %"
| where Wear-Rate >0 
| table time host FullChargedCapacity DesignedCapacity Wear_Rate
Tags (1)
0 Karma
1 Solution

FrankVl
Ultra Champion

So (apart from a bunch of typo's which I assume are not in your actual search but only in the sample you posted here):
- As I mentioned, you cannot add a string to the end of a number field and then still use it in calculations
- You cannot add a string to a number field and then still use it in a where >0 condition

So calculation and filtering first, then formatting:

 index="windows-wmi" sourcetype="wmi:BatteryFull"  OR sourcetype="wmi:BatteryStatic" 
| stats first(FullChargedCapacity) AS FullChargedCapacity first(DesignedCapacity) AS DesignedCapacity first(_time) AS _time BY host 
| eval time = strftime(_time, "%m/%d/%Y %H:%M") 
| eval Wear_Rate = 100-(FullChargedCapacity *100/DesignedCapacity)
| where Wear_Rate >0
| eval FullChargedCapacity = FullChargedCapacity." mAH"
| eval Wear_Rate = Wear_Rate."%"
| table time host FullChargedCapacity DesignedCapacity Wear_Rate

Now, regarding performance: there is nothing specifically in this query that is an obvious candidate for improvement. I guess you just have a lot of data in that index?

So if you want to improve the speed, you may want to search over a shorter time period. I'm also a bit puzzled by the purpose of using first() in the stats. So perhaps you can explain a bit more what the data set looks like, how you run this search (over what time window) and why first() is used?

View solution in original post

0 Karma

FrankVl
Ultra Champion

So (apart from a bunch of typo's which I assume are not in your actual search but only in the sample you posted here):
- As I mentioned, you cannot add a string to the end of a number field and then still use it in calculations
- You cannot add a string to a number field and then still use it in a where >0 condition

So calculation and filtering first, then formatting:

 index="windows-wmi" sourcetype="wmi:BatteryFull"  OR sourcetype="wmi:BatteryStatic" 
| stats first(FullChargedCapacity) AS FullChargedCapacity first(DesignedCapacity) AS DesignedCapacity first(_time) AS _time BY host 
| eval time = strftime(_time, "%m/%d/%Y %H:%M") 
| eval Wear_Rate = 100-(FullChargedCapacity *100/DesignedCapacity)
| where Wear_Rate >0
| eval FullChargedCapacity = FullChargedCapacity." mAH"
| eval Wear_Rate = Wear_Rate."%"
| table time host FullChargedCapacity DesignedCapacity Wear_Rate

Now, regarding performance: there is nothing specifically in this query that is an obvious candidate for improvement. I guess you just have a lot of data in that index?

So if you want to improve the speed, you may want to search over a shorter time period. I'm also a bit puzzled by the purpose of using first() in the stats. So perhaps you can explain a bit more what the data set looks like, how you run this search (over what time window) and why first() is used?

0 Karma

jip31
Motivator

thanks its perfect

0 Karma

FrankVl
Ultra Champion

1: no need to shout at us, please change the title to not use all caps
2: what does "it doesnt works" mean? Crystal balls are in short supply, please provide example data / screenshots that show the issue, show the input data and then explain what is wrong and what you want to get as a result.

Regarding improving the query: what is the idea behind OR sourcetype="wmi:BatteryStatic"? Do you have data with that sourcetype in another index (if so: specify that index, to prevent splunk hunting for that sourcetype in all your indexes)? Or should this have been AND instead of OR?
And one other bug: when you concatenate "mAH" to your field values, they change into string, so you cannot use it in calculations anymore like you do. So first calculate, then add the "mAH".

jip31
Motivator

sorry I have forgottent a sourcetype...
the code is

index="windows-wmi" sourcetype="wmi:BatteryFull"  OR sourcetype="wmi:BatteryStatic" 
| stats first(FullChargedCapacity) AS FullChargedCapacity first(DesignedCapacity) AS DesignedCapacity first(_time) AS _time BY host 
| eval time = strftime(_time, "%m/%d/%Y %H:%M") 
| eval FullChargedCapactity = (FullChargedCapactity." mAH") 
| eval Wear_Rate = (100-(FullChargedCapacity *100/DesignedCapacity))." %" 
|  where Wear-Rate >0
| table time host FullChargedCapacity DesignedCapacity Wear_Rate

when i say it doesnt works its about mAH only
my code works but i want to know if its possible to optimize it
please see the screenshot https://www.cjoint.com/c/HLgl3RPjJed

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...