topic Re: How to report status change over time? in Reporting

How to report status change over time?

Benzula — Wed, 30 Sep 2020 01:08:39 GMT

I am looking at reporting on the changes in status over time. So if I have a list of servers, and they have 3 possible statuses. (High/Med/Low) and they change day to day. I want to know how many have changed from 1 category to the next. The data is indexed and can use the time picker.

So I would need
Low=> Med, Low=> High,
Med=> Low, Med=> High,
High=> Med, High=> Low
Or 6 types of movements
By Month

Field Names would be server_name, category_level

So, for example, let's say I had 10 servers for the month of May and their status below.

ABC1 - High
ABC2 - High
ABC3 - High
ABC4 - Med
ABC5 - Med
ABC6 - High
ABC7 - Med
ABC8 - Low
ABC9 - Low
ABC10 - Low

Then they change sometime in the middle of June to the below.

ABC1 - High
ABC2 - Med
ABC3 - Med
ABC4 - Low
ABC5 - Med
ABC6 - High
ABC7 - Med
ABC8 - High
ABC9 - Low
ABC10 - Low

I would want the totals to be.
2 servers moved from High=>Med
1 server moved from Med=>Low
1 server moved from Low=> High

so the categories would be below for the month of June so if the status change at all during that month, count that change during that month.

Low=> Med - 0
Low=> High - 1
Med=> Low - 1
Med=> High - 0
High=> Med - 2
High=> Low - 0

Re: How to report status change over time?

skalliger — Mon, 01 Jul 2019 20:24:21 GMT

How exactly are you calculating this right now and especially, how are you indexing this?

Summary indexing seems to be a good solution for this.

First option: Run a daily search that populates a summary index with the host and category_level.

The second option would be to use a lookup.

However, you would not be able to look back in time. This would require a search populating a lookup once a month and then a search comparing that lookup from the previous month to your actual results.

A third approach would be, create an index where you write a risk score (numerical) into the index for every host. These risk scores translate to your cagegory_level (calculated field for example, if(score>10 AND <20, "low") ) and you simply sum up the risk scores of the previous 30 days, for a monthly report. A simple lookup defined whether a host is high, medium or low.

The third option has a benefit. You can simply look further into the past and see if a risk score of a host was higher two months ago compared to a month ago.

Skalli

Re: How to report status change over time?

woodcock — Mon, 01 Jul 2019 21:01:48 GMT

Like this:

| makeresults | eval raw="server_name=ABC1,category_level=High server_name=ABC2,category_level=Med server_name=ABC3,category_level=Med server_name=ABC4,category_level=Low server_name=ABC5,category_level=Med server_name=ABC6,category_level=High server_name=ABC7,category_level=Med server_name=ABC8,category_level=High server_name=ABC9,category_level=Low server_name=ABC10,category_level=Low:::server_name=ABC1,category_level=High server_name=ABC2,category_level=High server_name=ABC3,category_level=High server_name=ABC4,category_level=Med server_name=ABC5,category_level=Med server_name=ABC6,category_level=High server_name=ABC7,category_level=Med server_name=ABC8,category_level=Low server_name=ABC9,category_level=Low server_name=ABC10,category_level=Low"
| makemv delim=":::" raw
| mvexpand raw
| streamstats count AS _shift
| eval _shift = "-" . (_shift - 1) . "mon"
| eval _time = relative_time(_time, _shift)
| makemv raw
| mvexpand raw
| rename raw AS _raw
| kv

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| bin _time span=1m
| dedup _time category_level server_name
| reverse
| stats list(category_level) AS transition BY server_name
| eval transition=mvjoin(mvdedup(transition), "->")
| append [|makeresults | eval transition="Low Med High Low->Med Low->High Med->Low Med->High High->Med High->Low" | makemv transition]
| stats dc(server_name) AS count BY transition