Windows Server Performance Dashboard

[nigelapt]

Hi Folks

I need to produce a Windows Server dashboard which will allow the users to pick a server or group of servers and then display data on a number of charts on one page.  The data comes from a SQL query which just pulls the last 7 days performance data for all servers into a single table.

This is what I am trying to achieve, but I don't understand how to scope each chart to the relevant performance counter.

If I try to filter for % Free Space by Date all the other charts go blank.  

Please advise how my data should be organized in the Load Script?  Or has anyone down something similar in QS I could look at.

Thanks in advance!

[KGalloway]

Could you provide an example of what your data looks like?

You should be able to create six charts with specific dimensions and measures from your data to achieve what you want.

For example, the first chart would have a dimension of the date field from your data and a measure of avg/sum/max/min % Free Space (depending on how you want to aggregate it). If you also want to split them by server, you would need a serverID field in your data. It could be added as a second dimension to the chart.

[nigelapt]

I think its the way the data is loaded.

I probably need each counter across top.

Is there anyway to transpose data during load?

PR Device	PR Sample Date/Time	PR Object	PR Counter	PR Sample Val
Server1	2023-09-29 00:05:58	SQL Database	DB Transactions/sec	4.5
Server1	2023-09-29 00:21:08	SQL Database	DB Transactions/sec	1.8
Server1	2023-09-29 00:35:27	SQL Database	DB Disk Write Latency (ms)	1
Server1	2023-09-29 00:36:18	SQL Database	DB Transactions/sec	0
Server1	2023-09-29 00:50:18	SQL Database	DB Active Requests	0
Server1	2023-09-29 00:50:27	SQL Database	DB Disk Read Latency (ms)	0
Server1	2023-09-29 00:50:27	SQL Database	DB Disk Write Latency (ms)	2
Server1	2023-09-29 00:51:28	SQL Database	DB Transactions/sec	1.4
Server1	2023-09-29 01:05:23	SQL Database	DB Disk Write Latency (ms)	7
Server1	2023-09-29 01:06:38	SQL Database	DB Transactions/sec	0.8
Server1	2023-09-29 01:20:23	SQL Database	DB Disk Write Latency (ms)	0
Server1	2023-09-29 01:21:49	SQL Database	DB Transactions/sec	1.3
Server1	2023-09-29 01:35:23	SQL Database	DB Disk Write Latency (ms)	10
Server2	2023-09-29 01:36:59	SQL Database	DB Transactions/sec	2.4
Server2	2023-09-29 01:50:13	SQL Database	DB Active Requests	0
Server2	2023-09-29 01:50:23	SQL Database	DB Disk Read Latency (ms)	0
Server2	2023-09-29 01:50:23	SQL Database	DB Disk Write Latency (ms)	27
Server2	2023-09-29 01:52:09	SQL Database	DB Transactions/sec	1.7
Server2	2023-09-29 02:05:22	SQL Database	DB Disk Write Latency (ms)	1
Server2	2023-09-29 02:07:19	SQL Database	DB Transactions/sec	0.8

[KGalloway]

With your existing data structure, you can do something like this:

This is a bar chart with two dimensions and one measure.

I know of a way to transpose in the load script, but I don't think it is the most optimal. Here it is:

original_table:

load * inline [

PR Device, PR Sample Date/Time, PR Object, PR Counter, PR Sample Val

Server1, 45198.0041435185, SQL Database, DB Transactions/sec, 4.5

Server1, 45198.0146759259, SQL Database, DB Transactions/sec, 1.8

Server1, 45198.0246180556, SQL Database, DB Disk Write Latency (ms), 1

Server1, 45198.0252083333, SQL Database, DB Transactions/sec, 0

Server1, 45198.0349305556, SQL Database, DB Active Requests, 0

Server1, 45198.0350347222, SQL Database, DB Disk Read Latency (ms), 0

Server1, 45198.0350347222, SQL Database, DB Disk Write Latency (ms), 2

Server1, 45198.0357407407, SQL Database, DB Transactions/sec, 1.4

Server1, 45198.0454050926, SQL Database, DB Disk Write Latency (ms), 7

Server1, 45198.0462731481, SQL Database, DB Transactions/sec, 0.8

Server1, 45198.0558217593, SQL Database, DB Disk Write Latency (ms), 0

Server1, 45198.0568171296, SQL Database, DB Transactions/sec, 1.3

Server1, 45198.0662384259, SQL Database, DB Disk Write Latency (ms), 10

Server2, 45198.067349537, SQL Database, DB Transactions/sec, 2.4

Server2, 45198.0765393519, SQL Database, DB Active Requests, 0

Server2, 45198.0766550926, SQL Database, DB Disk Read Latency (ms), 0

Server2, 45198.0766550926, SQL Database, DB Disk Write Latency (ms), 27

Server2, 45198.0778819444, SQL Database, DB Transactions/sec, 1.7

Server2, 45198.0870601852, SQL Database, DB Disk Write Latency (ms), 1

Server2, 45198.0884143518, SQL Database, DB Transactions/sec, 0.8

]

;

noconcatenate

transpose_table:

load 

[PR Device],

    date(floor([PR Sample Date/Time])) as [PR Sample Date],

    [PR Object],

    sum(if([PR Counter] = 'DB Transactions/sec', [PR Sample Val])) as [DB Transactions/sec],

    sum(if([PR Counter] = 'DB Disk Write Latency (ms)', [PR Sample Val])) as [DB Disk Write Latency (ms)],

    sum(if([PR Counter] = 'DB Active Requests', [PR Sample Val])) as [DB Active Requests],

    sum(if([PR Counter] = 'DB Disk Read Latency (ms)', [PR Sample Val])) as [DB Disk Read Latency (ms)]

resident original_table

group by [PR Device], date(floor([PR Sample Date/Time])), [PR Object];

drop table original_table;

Notably, I grouped by the date of the date/time and used a sum. It may be more appropriate for your needs to change the grouping or the aggregation function.

Then you can produce the same chart a little differently:

This is a combo chart with one dimension and 4 measures.

Let me know if I can clarify anything.

[nigelapt]

Thanks K

I was able to use that to cross tab the data.

The only issue is that the it adds in zero values during that process and this affects the charts.

Eg If I filter either Memory or Processor I get a totally different chart profile.

[KGalloway]

I believe most qlik charts have an "Include zero values" option:

Have you tried using this to remove them?

You could also do an additional table load in the load script to make the zeros null if that is appropriate for your data.