I am having issues with a search / Sub-search with appendcols when the number of rows are different.
I have a summary search to collect the license usage data by index into a summary index for the the MBs Usedfor each index I have in the environment.
The data looks like this in the index:
Index=syslog MBs Used=660336
Index=wineventlog MBs Used=347123
Now we are trying to get a search that will show us the % difference for the index by day or week
This is to provide us information if our license volume shoots up, we can find the indexes involved in this issue.
The search that uses this summary index is below.
This was working fine until we added a new index. Once the new index was added, each Index2 column was off by 1 row once that index name hit in the list. And this is skewing the Index2 column so the comparison (%difference) is skewed. I expect the same will happen as we decommission indexes also.
Any assistance on how to take this issue into account having a different number of rows for each search would be appreciated.
%difference search - comparing yesterday to 7 days ago
index=index_metric_summary "License Pool"=site1 earliest=@d latest=now
| sort Index | stats median("MB's Used") as yesterday by Index
| appendcols
[ search index=index_metric_summary "License Pool"=site1 earliest=-7d@d latest=-6d@d | sort Index
| rename Index as Index2
| stats median("MB's Used") as previous by Index2]
| eval %difference=round(((yesterday-previous)/previous)*100,2)
| table Index Index2 previous yesterday %difference
This is what the output looks like around the point with the new index is listed
Index Index2 previous yesterday %difference
atapower atapower 376 400 6.38
cardfile cardfile 596 594 -0.34
cbs_logs_new cbs_logs_qa 0 63890 249778 <<-- wrong compare for %difference
cbs_logs_qa cbs_logs_stress 1046 7262 94.26
With help from to4kawa, here is my final version of this search to find the difference in the index daily license usage to see if any indexes are negatively impacting us.
Had to rename my column for MBs because I named it in the summary index with a space and a hyphen (ugh)
| multisearch [ search index=index_metric_summary "License Pool"=pool1 earliest=@d latest=now | eval days="yesterday" ]
[ search index=index_metric_summary "License Pool"=pool1 earliest=-7d@d latest=-6d@d | eval days="previous"]
| rename "MB's Used" AS MB
| stats median(eval(if(days="yesterday", MB, null()) )) as yesterday, median(eval(if(days="previous", MB, null()) )) as previous by Index
| eval %difference=round(((yesterday-previous)/previous)*100,2)
search %difference > 20 OR %difference < -20
| sort - %difference
| table Index previous yesterday %difference
With help from to4kawa, here is my final version of this search to find the difference in the index daily license usage to see if any indexes are negatively impacting us.
Had to rename my column for MBs because I named it in the summary index with a space and a hyphen (ugh)
| multisearch [ search index=index_metric_summary "License Pool"=pool1 earliest=@d latest=now | eval days="yesterday" ]
[ search index=index_metric_summary "License Pool"=pool1 earliest=-7d@d latest=-6d@d | eval days="previous"]
| rename "MB's Used" AS MB
| stats median(eval(if(days="yesterday", MB, null()) )) as yesterday, median(eval(if(days="previous", MB, null()) )) as previous by Index
| eval %difference=round(((yesterday-previous)/previous)*100,2)
search %difference > 20 OR %difference < -20
| sort - %difference
| table Index previous yesterday %difference
Two mistakes in the above - sorry
%difference search - comparing yesterday to 7 days ago
index=index_metric_summary "License Pool"=site1 earliest=@d latest=now
| sort Index | stats median("MB's Used") as yesterday by Index
| appendcols
[ search index=index_metric_summary "License Pool"=site1 earliest=-7d@d latest=-6d@d | sort Index
| rename Index as Index2
| stats median("MB's Used") as previous by Index2]
| eval %difference=round(((yesterday-previous)/previous)*100,2)
| table Index Index2 previous yesterday %difference
And the output report for the "skewed" line should be:
cbs_logs_new cbs_logs_qa 63890 0 -249778 <<-- wrong compare for %difference