topic Re: How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day? in Splunk Search

How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day?

mhassan24 — Fri, 14 Apr 2017 20:06:34 GMT

Hi,

I am trying to create a report that looks at two fields: mem and cpu
It should display the count of mem and cpu by devname (Device ID) when mem>80 and when cpu>80 over time (7 days).

Each log contains a value for devname, mem and cpu

Something like this:

I can do a stats count for ONE field, but wanted to incorporate both. Maybe need to use contingency?
If the report as shown in the above is unattainable, I am open to doing two reports: one for mem and another for cpu

Thanks everyone in advance!
I'll post comments of trial and error as I keep searching the web.

Re: How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day?

niketn — Fri, 14 Apr 2017 22:43:14 GMT

@mhassan24, Have you already built the data in tabular format that you have posted? If not you will have to give some sample raw events.

Re: How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day?

lguinn2 — Sat, 15 Apr 2017 06:16:26 GMT

What you want is pretty easy to calculate, although I don't know if it makes sense to sum the cpu and memory in this way...
But the hard part is formatting... it is quite difficult to produce this exact format in Splunk.

yoursearchhere
| bucket _time span=1d
| stats sum(mem) as daily_mem sum(cpu) as daily_cpu by devname _time
| eventstats sum(mem) as totalmem sum(cpu) as totalcpu by devname
| where totalmem > 80 or totalcpu > 80
| fields - totalmem totalcpu
| eval daily = daily_mem . "     " . daily_cpu
| eval date = strftime(_time,"%x") . "\nmem      cpu"
| xyseries devname date daily

The alignment of the data may need to be tweaked, and it may never be perfect.
To make the reports separately is easier; here is one for just memory

yoursearchhere
| bucket _time span=1d
| eval date = strftime(_time,"%x")
| stats sum(mem) as mem by devname date
| addtotals
| where total > 80
| fields - total

Re: How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day?

woodcock — Sun, 16 Apr 2017 00:15:37 GMT

Like this:

Your base search here
| eval OverMem=if((mem>80), "YES", null()),
       OverCpu=if((cpu>80), "YES", null())
| bin _time span=1d
| eval time=strftime(_time, "%m/%d")
| chart count(OverCpu) AS cpu count(OverMem) AS mem OVER host BY time
| rename "cpu: *" AS "* cpu" "mem: *" AS "* mem"

Re: How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day?

mhassan24 — Mon, 17 Apr 2017 13:47:07 GMT

Thanks woodcock! I altered the report to only have mem for now.

But, had two questions for you:

1) How could the report be altered to only show devnames with counts (i.e. instances of mem > 80), omitting the others?
2) Is there a way to get a total count as the last column?

Thanks again for your help!

Re: How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day?

woodcock — Mon, 17 Apr 2017 14:42:15 GMT

OK, for older versions, try this:

Your base search here
| eval OverMem=if((mem>80), "YES", null())
| eval OverCpu=if((cpu>80), "YES", null())
| bin _time span=1d
| eval time=strftime(_time, "%m/%d")
| chart count(OverCpu) AS cpu count(OverMem) AS mem OVER host BY time
| rename "cpu: *" AS "* cpu" "mem: *" AS "* mem"

Re: How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day?

mhassan24 — Mon, 17 Apr 2017 15:06:36 GMT

Works really well and the query makes sense!

How could the following be achieved?
Feel free to give a tip without the actual query. I don't mind searching and figuring it out

1) How could the report be altered to only show devnames with counts (i.e. instances of mem > 80), omitting the others?
2) Is there a way to get a total count as the last column?

Re: How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day?

woodcock — Mon, 17 Apr 2017 23:08:00 GMT

I am not sure about #1 (I do not understand the ask) but for #2 use addtotals command:

http://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/Addtotals

P.S. Don't forget to upvote helpful answers and click Accept if when something works.

Re: How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day?

mhassan24 — Tue, 18 Apr 2017 15:25:50 GMT

Thanks woodcock! Much appreciated

Re: How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day?

mhassan24 — Tue, 18 Apr 2017 15:30:26 GMT

For #1, the ask is if the total mem>80 is 0, then I'd like it not to be on the chart displayed by the query

Re: How to generate a search that will the display the count of two fields if the memory and CPU usage is greater than 80 per day?

mhassan24 — Tue, 18 Apr 2017 15:37:27 GMT

Nvm, was able to do it with: where Total > 1
Thank you again!