How to get a single value subtraction from two Timecharts?

[mujahidsof]

Hi,

I have two searches Total Memory and Available memory and I want to subtract this two queries result, so that I can get Used Memory.

Total Memory

sourcetype=cf:valuemetric name=CapacityTotalMemory 
       | eval valueGTB=round(case(unit=="MiB", value/1024, unit=="KiB", value/(1024*1024), unit=="GiB", value),2) 
       | timechart span=5s avg(valueGTB) as valueGB by ip
       | filldown        | untable _time ip valueGTB 
       | timechart span=5s sum(valueGTB)

Available Memory

sourcetype=cf:valuemetric name=CapacityRemainingMemory 
       | eval valueGB=round(case(unit=="MiB", value/1024, unit=="KiB", value/(1024*1024), unit=="GiB", value),2) 
       | timechart span=5s avg(valueGB) as valueGB by ip
       | filldown        | untable _time ip valueGB
       | timechart span=5s sum(valueGB)

I tried everything but could not figure it out.

[niketn]

@mujahidsof, Since you are not using ip field in your final timechart seems like you just need two series the sum of average memory available and total for every 5 seconds. Following should still work without join/append or appendcols.

sourcetype=cf:valuemetric ( name=CapacityTotalMemory OR name=CapacityRemainingMemory)
| eval valueGB=round(case(unit=="MiB", value/1024, unit=="KiB", value/(1024*1024), unit=="GiB", value),2) 
| bin _time span=5s
| stats avg(valueGB) by name, ip, _time
| timechart span=5s sum(valueGB) by name
| filldown CapacityTotalMemory CapacityRemainingMemory
| rename "CapacityTotalMemory" as  "total_memory"
| rename "CapacityRemainingMemory" as  "available_memory"

Please try out and confirm. Also you can create an Overlay Field if required using Chart Formatting Options.

[mujahidsof]

Sum(total_memory)-sum(available_memory) = available memory(single value).

I am unable to achieve this with above query may be I am doing wrong 😞

[mayurr98 · Accepted solution]

try this:

sourcetype=cf:valuemetric name=CapacityTotalMemory 
| eval valueGTB=round(case(unit=="MiB", value/1024, unit=="KiB", value/(1024*1024), unit=="GiB", value),2) 
| timechart span=5s avg(valueGTB) as valueGB by ip 
| filldown 
| untable _time ip valueGTB 
| timechart span=5s sum(valueGTB) as "total_memory" 
| join _time 
    [ sourcetype=cf:valuemetric name=CapacityRemainingMemory 
    | eval valueGB=round(case(unit=="MiB", value/1024, unit=="KiB", value/(1024*1024), unit=="GiB", value),2) 
    | timechart span=5s avg(valueGB) as valueGB by ip 
    | filldown 
    | untable _time ip valueGB 
    | timechart span=5s sum(valueGB) as "available_memory"] 
| eval used_memory=total_memory-available_memory

let me know if this helps!

[mujahidsof]

Thank you very much. You saved my hours. 😛 . I don't tried _time.

[Sukisen1981]

what happens if you use appencols, ?
first search followed by the second? if the columns are aligned, you can just subtract the fields

[mujahidsof]

I have used append, have to try appendcols