Archive
Highlighted

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

Contributor

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
Highlighted

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

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".

Highlighted

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

Contributor

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
Highlighted

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

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
Highlighted

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

Contributor

thanks its perfect

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.