Reporting

How to report status change over time?

New Member

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

0 Karma

Esteemed Legend

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
0 Karma

SplunkTrust
SplunkTrust

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!