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

[jip31]

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

[FrankVl · Accepted solution]

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?

[jip31]

thanks its perfect

[FrankVl]

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]

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