Reporting

How to report status change over time?

Benzula
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

woodcock
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

skalliger
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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...