Cleaning up the Legend

Grafana Dashboards
1
  • What Grafana version and what operating system are you using?
    Linux 10.4.10
  • What are you trying to achieve?
    I am grouping my data by date and transid, then calculating total MIPS for each group. This is working perfectly and giving a time series like this:

image
image574×445 40.7 KB

I want the legend to only show the TRANSID, in other words I want to strip off the "Billed MIPS (sum) from each entry in the legend. Billing MIPS (sum) AADF should show as AADF.

  • How are you trying to achieve it?

I started putting in field overrides to change the standard display name for each entry, however, there are about 500 transids - lot of typing and clicking! I tried using Value Mapping, Overriding using the Override with matching REGEX and even added a transform after the the group by transform to rename fields using regex. The only thing I can get to work reliably is to override by field and set the standard display name for each transid.

  • What happened?
    Nothing. The override by field name works but is tedious and repetitive. There’s got to be a better way!

  • What did you expect to happen?
    I expected a cleaner looking legend with just each of the 4 character transids listed.

  • Can you copy/paste the configuration(s) that you are having problems with?
    No, I don’t have access

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
    I don’t know, I’m just a lowly user.

  • Did you follow any online instructions? If so, what is the URL?
    Many topics on this forum dealing with similar items but I just can’t seem to get there from here.

A brief snippet of the data looks like this:

image
image1026×649 47.4 KB

Any advice would be helpful.

Thanks,

Greg

2

Try to use Rename fields by regex transformation:

image
image1302×574 29.4 KB

(I’m not saying that regexp is 100% valid for your case, so it may need some polishing - test&improve it)

3

what is your datasource?

4

I’m using a CSV file as the data source (I know, but we already had the spreadsheets).

I had 3 transforms and added the Rename by REGEX transform as step 3, thinking sequentially the group by transform sums the Billing MIPS and groups it by date and transid. So Billing MIPS doesn’t exist until the Group By sums the total. I added the REGEX as the 3rd transform and nothing changed. I then moved it to the 4th step and still nothing changes in the legend.

image
image934×694 66.1 KB

I ran the regex on regx101.com with a sample data of Billing MIPS (sum) AADF. It gave me this as an explanation:

/

(Billed MIPS (sum)) (.*)

/

gm

1st Capturing Group

(Billed MIPS (sum))

Billed MIPS

matches the characters Billed MIPS literally (case sensitive)

( matches the character ( with index 4010 (2816 or 508) literally (case sensitive)

sum

matches the characters sum literally (case sensitive)

) matches the character ) with index 4110 (2916 or 518) literally (case sensitive)

matches the character with index 3210 (2016 or 408) literally (case sensitive)

2nd Capturing Group

(.*)

.

matches any character (except for line terminators)

  • matches the previous token between zero and unlimited times, as many times as possible, giving back as needed (greedy)

Global pattern flags

g modifier: global. All matches (don’t return after first match)

m modifier: multi line. Causes ^ and $ to match the begin/end of each line (not only begin/end of string)

Match Information

And the Match Information is:

image
image430×212 8.11 KB

How do we know that the variable $1 holds the AADF portion (the group 2 information). I tried it using both $1 and $2 (together and separately) and still nothing changes.

I am a complete newbie so I’m sure I’m making an obvious blunder.

Thanks,

Greg

5

please provide a sample csv data, obfuscate/hide any sensitive data

foo,bar
xo,vader

edit the csv file using notepadd++, copy and paste here would be a good option also if you do not want to type it. we cant work from an image csv data

6

OK, here’s a small part of the file.

"Date","Instance","TRAN","Transactions","Average MIPS","Billed MIPS","Average MIPS/transaction","Average CPU/transaction","Average Response/transaction","Average SQL/transaction"
"11/01/2024","B2B","AADF",4,0.00,0.00,0.00,0.0002,0.0273,0              
"11/01/2024","B2B","AAON",22,0.00,0.00,0.00,0.0000,78195.2717,0         
"11/01/2024","B2B","CATA",30,0.00,0.00,0.00,0.0005,0.0013,0             
"11/01/2024","B2B","CATD",27,0.00,0.00,0.00,0.0004,0.0032,0             
"11/01/2024","B2B","CATR",3,0.00,0.00,0.00,0.0008,0.0015,0              
"11/01/2024","B2B","CECI",0,0.00,0.00,0.00,0.0003,0.0005,0              
"11/01/2024","B2B","CEMT",15,0.00,0.00,0.00,0.0029,0.0648,0             
"11/01/2024","B2B","CEPF",3,0.00,0.00,0.00,0.0002,492932.627,0          
"11/01/2024","B2B","CEPM",3,0.00,0.00,0.00,0.0000,492933.672,0          
"11/01/2024","B2B","CESC",31,0.00,0.00,0.00,0.0005,0.0008,0             
"11/01/2024","B2B","CESF",0,0.00,0.00,0.00,0.0006,0.0016,0              
"11/01/2024","B2B","CEX2",22,0.17,0.24,0.19,0.4027,78433.2494,0         
"11/01/2024","B2B","CFCT",3,0.00,0.00,0.00,0.0003,0.0023,0              
"11/01/2024","B2B","CGRP",3,0.00,0.00,0.00,0.0002,0.0215,0              
"11/01/2024","B2B","CHCK",3,0.02,0.03,0.21,0.4569,492931.006,0          
"11/01/2024","B2B","CISB",7,0.00,0.00,0.00,0.0001,0.0004,0              
"11/01/2024","B2B","CISC",112,0.00,0.00,0.00,0.0009,6.1828,0            
"11/01/2024","B2B","CISD",3,0.00,0.00,0.00,0.0012,0.0810,0              
"11/01/2024","B2B","CISE",22,0.00,0.00,0.00,0.0000,78195.3330,0         
"11/01/2024","B2B","CISM",22,0.00,0.00,0.00,0.0000,78195.3330,0         
"11/01/2024","B2B","CISP",22,0.02,0.03,0.02,0.0494,78195.3330,0         
"11/01/2024","B2B","CISQ",32,0.00,0.00,0.00,0.0001,0.0003,0             
"11/01/2024","B2B","CISR",22,0.74,1.03,0.80,1.7304,78195.3329,0         
"11/01/2024","B2B","CISS",87,0.00,0.00,0.00,0.0012,0.2938,0             
"11/01/2024","B2B","CIST",28,0.00,0.00,0.00,0.0010,0.0197,0             
"11/01/2024","B2B","CJSL",3,0.00,0.00,0.00,0.0003,492923.616,0          
"11/01/2024","B2B","CJSR",5,0.00,0.00,0.00,0.0003,0.4715,0              
"11/01/2024","B2B","CJTR",3,0.00,0.00,0.00,0.0002,0.0062,0              
"11/01/2024","B2B","CKAM",22,0.00,0.00,0.00,0.0002,78195.2910,0         
"11/01/2024","B2B","CLQ2",44,0.00,0.00,0.00,0.0002,1.1647,0             
"11/01/2024","B2B","CLR2",39,0.00,0.00,0.00,0.0001,0.0020,0             
"11/01/2024","B2B","CMPE",3,0.00,0.00,0.00,0.0000,492933.640,0          
"10/01/2024","B2B","?",0,0.00,0.00,0.00,0.0024,0.0052,0                 
"10/01/2024","B2B","AAON",22,0.00,0.00,0.00,0.0001,77603.7656,0         
"10/01/2024","B2B","CATA",30,0.00,0.00,0.00,0.0006,0.0017,0             
"10/01/2024","B2B","CATD",26,0.00,0.00,0.00,0.0005,0.0036,0    
"10/01/2024","B2B","CATR",4,0.00,0.00,0.00,0.0009,0.0015,0      
"10/01/2024","B2B","CECI",0,0.00,0.00,0.00,0.0008,0.0112,0      
"10/01/2024","B2B","CEMT",21,0.00,0.00,0.00,0.0027,0.0573,0     
"10/01/2024","B2B","CEPF",3,0.00,0.00,0.00,0.0002,555267.585,0  
"10/01/2024","B2B","CEPM",3,0.00,0.00,0.00,0.0000,555268.057,0  
"10/01/2024","B2B","CESC",30,0.00,0.00,0.00,0.0006,0.0009,0     
"10/01/2024","B2B","CESF",0,0.00,0.00,0.00,0.0010,0.0023,0      
"10/01/2024","B2B","CEX2",22,0.17,0.23,0.19,0.4063,77604.2186,0 
"10/01/2024","B2B","CFCT",4,0.00,0.00,0.00,0.0003,0.0020,0      
"10/01/2024","B2B","CGRP",4,0.00,0.00,0.00,0.0002,0.0227,0      
"10/01/2024","B2B","CHCK",3,0.03,0.04,0.23,0.4933,555267.043,0  
"10/01/2024","B2B","CISB",8,0.00,0.00,0.00,0.0001,0.0004,0      
"10/01/2024","B2B","CISC",144,0.00,0.00,0.00,0.0011,4.4112,0    
"10/01/2024","B2B","CISD",3,0.00,0.00,0.00,0.0015,0.0967,0      
"10/01/2024","B2B","CISE",22,0.00,0.00,0.00,0.0000,77603.8505,0 
"10/01/2024","B2B","CISM",22,0.00,0.00,0.00,0.0000,77603.8505,0 
"10/01/2024","B2B","CISP",22,0.03,0.04,0.03,0.0735,77603.8497,0 
"10/01/2024","B2B","CISQ",39,0.00,0.00,0.00,0.0001,0.0003,0     
"10/01/2024","B2B","CISR",22,0.73,0.99,0.81,1.7411,77835.5038,0 
"10/01/2024","B2B","CISS",144,0.00,0.00,0.00,0.0012,0.2247,0    
"10/01/2024","B2B","CIST",40,0.00,0.00,0.00,0.0010,0.0297,0     
"10/01/2024","B2B","CJSL",3,0.00,0.00,0.00,0.0004,555266.794,0  
"10/01/2024","B2B","CJSR",8,0.00,0.00,0.00,0.0004,0.4719,0      
"10/01/2024","B2B","CJTR",4,0.00,0.00,0.00,0.0002,0.0063,0      
"10/01/2024","B2B","CKAM",22,0.00,0.00,0.00,0.0003,77603.7918,0 
"10/01/2024","B2B","CLQ2",65,0.00,0.00,0.00,0.0003,1.1129,0     
"10/01/2024","B2B","CLR2",55,0.00,0.00,0.00,0.0002,0.0019,0     
"10/01/2024","B2B","CMPE",3,0.00,0.00,0.00,0.0000,555268.124,0  
"10/01/2024","B2B","COIE",72,0.17,0.23,0.06,0.1213,23322.8162,0 
"10/01/2024","B2B","COI0",22,0.01,0.02,0.02,0.0353,77603.8245,0 
"09/01/2024","B2B","?",0,0.00,0.00,0.00,0.0027,0.0049,0         
"09/01/2024","B2B","AADF",1,0.00,0.00,0.00,0.0002,0.1000,0      
"09/01/2024","B2B","AAON",22,0.00,0.00,0.00,0.0000,79657.2106,0 
"09/01/2024","B2B","CATA",29,0.00,0.00,0.00,0.0006,0.0076,0     
"09/01/2024","B2B","CATD",28,0.00,0.00,0.00,0.0004,0.0066,0     
"09/01/2024","B2B","CATR",2,0.00,0.00,0.00,0.0009,0.0014,0      
"09/01/2024","B2B","CEGN",0,0.00,0.00,0.00,0.0008,0.0024,0      
"09/01/2024","B2B","CEMT",10,0.00,0.00,0.00,0.0027,0.0621,0     
"11/01/2024","eTNI","AAON",8,0.00,0.00,0.00,0.0000,86157.7482,0   
"11/01/2024","eTNI","CATA",10,0.00,0.00,0.00,0.0004,0.0013,0      
"11/01/2024","eTNI","CATD",10,0.00,0.00,0.00,0.0003,0.0037,0      
"11/01/2024","eTNI","CATR",0,0.00,0.00,0.00,0.0007,0.0011,0       
"11/01/2024","eTNI","CEMT",1,0.00,0.00,0.00,0.0021,0.0052,0       
"11/01/2024","eTNI","CESC",11,0.00,0.00,0.00,0.0004,0.0008,0      
"11/01/2024","eTNI","CESN",0,0.00,0.00,0.00,0.0010,0.0191,0       
"11/01/2024","eTNI","CEX2",8,0.05,0.07,0.14,0.2879,86157.8208,0   
"11/01/2024","eTNI","CFCT",0,0.00,0.00,0.00,0.0002,0.0010,0       
"11/01/2024","eTNI","CGRP",0,0.00,0.00,0.00,0.0002,0.0179,0       
"11/01/2024","eTNI","CISC",0,0.00,0.00,0.00,0.0002,0.0065,0       
"11/01/2024","eTNI","CISE",8,0.00,0.00,0.00,0.0000,86157.7796,0   
"11/01/2024","eTNI","CISM",8,0.00,0.00,0.00,0.0000,86157.7796,0   
"11/01/2024","eTNI","CISP",8,0.00,0.00,0.00,0.0003,86157.7796,0   
"11/01/2024","eTNI","CISR",8,0.00,0.00,0.00,0.0000,86157.7796,0   
"11/01/2024","eTNI","CJSR",0,0.00,0.00,0.00,0.0001,0.0015,0       
"11/01/2024","eTNI","CJTR",0,0.00,0.00,0.00,0.0002,0.0039,0       
"11/01/2024","eTNI","CKAM",8,0.00,0.00,0.00,0.0000,86157.7482,0   
"11/01/2024","eTNI","CLQ2",3,0.00,0.00,0.00,0.0002,0.9559,0       
"11/01/2024","eTNI","CLR2",2,0.00,0.00,0.00,0.0001,0.0010,0       
"11/01/2024","eTNI","COIE",9,0.02,0.02,0.04,0.0812,72754.3563,0   
"11/01/2024","eTNI","COI0",8,0.00,0.00,0.00,0.0069,81992.9753,0   
"11/01/2024","eTNI","CONA",8,0.00,0.01,0.01,0.0222,81993.0409,0   
"11/01/2024","eTNI","CONL",8,0.00,0.00,0.00,0.0005,86157.8263,0   
"11/01/2024","eTNI","CPIR",1,0.00,0.00,0.00,0.0006,0.0189,0       
"11/01/2024","eTNI","CPIS",0,0.00,0.00,0.00,0.0002,0.0049,0       
"11/01/2024","eTNI","CPLT",0,0.00,0.00,0.01,0.0271,4.0345,0       
"11/01/2024","eTNI","CRLR",0,0.00,0.00,0.00,0.0007,0.0058,0       
"11/01/2024","eTNI","CRMF",16,0.00,0.00,0.00,0.0001,0.0003,0      
"11/01/2024","eTNI","CRSQ",24,0.00,0.00,0.00,0.0001,0.0006,0      
"11/01/2024","eTNI","CSAC",0,0.00,0.00,0.00,0.0005,0.0007,0       
"11/01/2024","eTNI","CSFU",0,0.00,0.00,0.00,0.0024,0.0234,0       
"11/01/2024","eTNI","CSHQ",8,0.00,0.00,0.00,0.0001,86157.7797,0   
"11/01/2024","eTNI","CSKP",5761,0.03,0.04,0.00,0.0002,0.0018,0    
"11/01/2024","eTNI","CSMI",1729448,3.88,5.43,0.00,0.0001,0.0002,0 
"10/01/2024","eTNI","?",0,0.00,0.00,0.00,0.0016,0.0106,0          
"10/01/2024","eTNI","AAON",9,0.00,0.00,0.00,0.0000,77492.4895,0   
"10/01/2024","eTNI","CATA",11,0.00,0.00,0.00,0.0004,0.0022,0    
"10/01/2024","eTNI","CATD",10,0.00,0.00,0.00,0.0003,0.0040,0    
"10/01/2024","eTNI","CATR",1,0.00,0.00,0.00,0.0009,0.0014,0     
"10/01/2024","eTNI","CEDA",0,0.00,0.00,0.00,0.0069,0.0131,0     
"10/01/2024","eTNI","CEMT",5,0.00,0.00,0.00,0.0029,0.0324,0     
"10/01/2024","eTNI","CEPF",1,0.00,0.00,0.00,0.0002,516766.772,0 
"10/01/2024","eTNI","CEPM",1,0.00,0.00,0.00,0.0000,516768.016,0 
"10/01/2024","eTNI","CESC",10,0.00,0.00,0.00,0.0004,0.0008,0    
"10/01/2024","eTNI","CESN",0,0.00,0.00,0.00,0.0015,0.4154,0     
"10/01/2024","eTNI","CEX2",9,0.05,0.06,0.13,0.2584,77492.8273,0 
"10/01/2024","eTNI","CFCT",1,0.00,0.00,0.00,0.0003,0.0026,0     
"10/01/2024","eTNI","CGRP",1,0.00,0.00,0.00,0.0002,0.0406,0     
"10/01/2024","eTNI","CHCK",1,0.00,0.00,0.06,0.1215,516756.890,0 
"10/01/2024","eTNI","CISC",1,0.00,0.00,0.00,0.0002,0.0081,0     
"10/01/2024","eTNI","CISD",1,0.00,0.00,0.00,0.0000,0.0119,0     
"10/01/2024","eTNI","CISE",9,0.00,0.00,0.00,0.0000,77492.5754,0 
"10/01/2024","eTNI","CISM",9,0.00,0.00,0.00,0.0000,77492.5754,0 
"10/01/2024","eTNI","CISP",9,0.00,0.00,0.00,0.0005,77492.5754,0 
"10/01/2024","eTNI","CISR",9,0.00,0.00,0.00,0.0000,77492.5753,0 
"10/01/2024","eTNI","CJSR",1,0.00,0.00,0.00,0.0001,0.0032,0     
"10/01/2024","eTNI","CJTR",1,0.00,0.00,0.00,0.0002,0.0046,0     
"10/01/2024","eTNI","CKAM",9,0.00,0.00,0.00,0.0001,77492.4956,0 
"10/01/2024","eTNI","CLQ2",10,0.00,0.00,0.00,0.0002,1.6231,0    
"10/01/2024","eTNI","CLR2",7,0.00,0.00,0.00,0.0001,0.0073,0     
"10/01/2024","eTNI","CMPE",1,0.00,0.00,0.00,0.0000,516767.965,0 
"10/01/2024","eTNI","COIE",11,0.02,0.02,0.03,0.0693,64421.6257,0
"10/01/2024","eTNI","COI0",9,0.00,0.00,0.00,0.0065,77492.5980,0 
"10/01/2024","eTNI","CONA",9,0.00,0.01,0.01,0.0207,77492.5961,0 
"10/01/2024","eTNI","CONL",9,0.00,0.00,0.00,0.0015,77492.8608,0 
"10/01/2024","eTNI","CPIR",3,0.00,0.00,0.00,0.0006,0.0330,0     
"10/01/2024","eTNI","CPIS",1,0.00,0.00,0.00,0.0001,0.0069,0     
"10/01/2024","eTNI","CPLT",1,0.00,0.00,0.01,0.0274,7.0350,0     
"10/01/2024","eTNI","CQRC",1,0.00,0.00,0.03,0.0517,516756.890,0 
"10/01/2024","eTNI","CRLR",1,0.00,0.00,0.00,0.0005,0.0076,0     
"10/01/2024","eTNI","CRMF",16,0.00,0.00,0.00,0.0002,0.0003,0    
"10/01/2024","eTNI","CRSQ",25,0.00,0.00,0.00,0.0001,0.0007,0    
"09/01/2024","eTNI","AAON",8,0.00,0.00,0.00,0.0000,83521.9557,0 
"09/01/2024","eTNI","CATA",11,0.00,0.00,0.00,0.0004,0.0013,0    
"09/01/2024","eTNI","CATD",11,0.00,0.00,0.00,0.0003,0.0071,0     
"09/01/2024","eTNI","CATR",0,0.00,0.00,0.00,0.0007,0.0010,0      
"09/01/2024","eTNI","CEMT",1,0.00,0.00,0.00,0.0030,0.0417,0      
"09/01/2024","eTNI","CEPF",0,0.00,0.00,0.00,0.0002,2671382.91,0  
"09/01/2024","eTNI","CEPM",0,0.00,0.00,0.00,0.0000,2671382.13,0  
"09/01/2024","eTNI","CESC",11,0.00,0.00,0.00,0.0004,0.0005,0     
"09/01/2024","eTNI","CEX2",8,0.04,0.06,0.13,0.2610,83522.0313,0  
"09/01/2024","eTNI","CFCT",0,0.00,0.00,0.00,0.0002,0.0010,0      
"09/01/2024","eTNI","CGRP",0,0.00,0.00,0.00,0.0002,0.0223,0      
"09/01/2024","eTNI","CHCK",0,0.00,0.00,0.07,0.1562,2671355.14,0  
"09/01/2024","eTNI","CISC",0,0.00,0.00,0.00,0.0002,0.0059,0      
"09/01/2024","eTNI","CISD",0,0.00,0.00,0.00,0.0000,0.0108,0      
"09/01/2024","eTNI","CISE",8,0.00,0.00,0.00,0.0000,83521.9927,0  
"09/01/2024","eTNI","CISM",8,0.00,0.00,0.00,0.0000,83521.9927,0  
"09/01/2024","eTNI","CISP",8,0.00,0.00,0.00,0.0003,83521.9927,0  
"09/01/2024","eTNI","CISR",8,0.00,0.00,0.00,0.0000,83521.9927,0  
"09/01/2024","eTNI","CJSR",0,0.00,0.00,0.00,0.0001,0.0014,0      
"09/01/2024","eTNI","CJTR",0,0.00,0.00,0.00,0.0002,0.0036,0      
"09/01/2024","eTNI","CKAM",8,0.00,0.00,0.00,0.0000,83521.9610,0  
"09/01/2024","eTNI","CLQ2",3,0.00,0.00,0.00,0.0002,0.6996,0      
"09/01/2024","eTNI","CLR2",2,0.00,0.00,0.00,0.0001,0.0091,0      
"09/01/2024","eTNI","CMPE",0,0.00,0.00,0.00,0.0000,2671382.60,0  
"09/01/2024","eTNI","COIE",9,0.01,0.01,0.02,0.0425,78460.0227,0  
"09/01/2024","eTNI","COI0",8,0.00,0.00,0.00,0.0037,83521.9608,0  
"09/01/2024","eTNI","CONA",8,0.00,0.00,0.01,0.0116,83521.9606,0  
"09/01/2024","eTNI","CONL",8,0.00,0.00,0.00,0.0004,83522.0365,0  
"09/01/2024","eTNI","CPIR",1,0.00,0.00,0.00,0.0006,0.0198,0      
"09/01/2024","eTNI","CPIS",0,0.00,0.00,0.00,0.0001,0.0047,0      
"09/01/2024","eTNI","CPLT",0,0.00,0.00,0.01,0.0243,4.0111,0      
"09/01/2024","eTNI","CQRC",0,0.00,0.00,0.04,0.0714,2671355.12,0  
"09/01/2024","eTNI","CRLR",0,0.00,0.00,0.00,0.0001,0.0048,0      
"09/01/2024","eTNI","CRMF",16,0.00,0.00,0.00,0.0001,0.0003,0     
"09/01/2024","eTNI","CRSQ",24,0.00,0.00,0.00,0.0001,0.0008,0     
"09/01/2024","eTNI","CSFU",0,0.00,0.00,0.00,0.0023,0.0288,0      
"09/01/2024","eTNI","CSHQ",8,0.00,0.00,0.00,0.0001,83521.9869,0  
"09/01/2024","eTNI","CSKP",5287,0.02,0.03,0.00,0.0002,0.0020,0
1 Like
7

now please explain from the data you provided which columns will be used for the time series aggregation

8

I am grouping by Date and TRAN and I am calculating the total for Billed MIPS. For each date, I should get a of TRAN and for each TRAN in each Date I should get a sum of Billed MIPS.

1 Like
9

image
image999×581 38.5 KB

0 Transformation

https://grafana.github.io/grafana-infinity-datasource/docs/uql

another way but I see some anomalies, vet them both out

image
image979×679 45 KB

10

It will be working fine if it is comming from datasource. But you have transformations which are doing manipulations before, so it can be more complicated.
Each transformation has option for debugging, so use that to find what is input/output.

11

I must be the stupidest person on this forum but I don’t see where I can put that UGL statement. I can’t find anything on the panel nor in the data source that would allow me to structure a query.

12

No one is stupid, just need to sprinkle some knowledge into the cranium

image
image750×495 23.8 KB