chris-bowman/Azure-Cost-Reporting

Issue found on the tag filtering compute column in resource table

RamBNarayanan opened this issue · 7 comments

RamBNarayanan commented

I had some issue with the compute column of the tags (Tags.Application and Tags.CostCentre) where it wasn't populating the data.

I have updated the compute field as per below to fix

Tags.Application = 
VAR LookupKey = "Application"
VAR SearchField = Resources[Tags]
VAR FindLookupKey = SEARCH(LookupKey,SearchField,1,1)
VAR FindValueStartColon = SEARCH(":",SearchField,FindLookupKey,1)+2 
VAR FindValueStart = IF(MID(SearchField,FindValueStartColon,1)="""",FindValueStartColon+1,FindValueStartColon)
VAR FindValueEnd0 = SEARCH(",",SearchField,FindLookupKey,LEN(SearchField))-FindValueStartColon-2
VAR FindValueEnd = if(FindLookupKey=2,FindValueEnd0,FindValueEnd0+1)

RETURN TRIM(iferror(if(FindLookupKey>1, MID(SearchField,FindValueStart,FindValueEnd),""),""))

VAR FindValueEnd = if(FindLookupKey=2,FindValueEnd0,FindValueEnd0+1) is added if the tag is the first one in the list
FindValueStart updated to get the values populated.

chris-bowman commented

Hi @RamBNarayanan. In my testing, this actually ends up getting the extra " at the end. Perhaps it's a difference in the data being returned? The Tags column (at least in the EA I have access to) shows as:
"Application": "nameofapp", "CostCentre": "1234" etc, so adding the +1 just gets the result as nameofapp"

Does yours include extra characters?

RamBNarayanan commented

My tags were like the below when i pulled the data and i was trying to filter out owner and environment. i didn't get an extra "

{""owner"":""DevOps"",""purpose"":""container for inlifeandrenewals prod uk secrets"",""createdBy"":""DevOps"",""expireson"":""never"",""DateCreated"":""27-Apr-2022"",""IsBicep"":""false""}"
"{""owner"":""DevOps"",""purpose"":""container for inlifeandrenewals prod euw secrets"",""createdBy"":""DevOps"",""expireson"":""never"",""DateCreated"":""27-Apr-2022"",""IsBicep"":""false""}"
"{""owner"":""DevOps"",""purpose"":""container for inlifeandrenewals prod usw secrets"",""createdBy"":""DevOps"",""expireson"":""never"",""DateCreated"":""27-Apr-2022"",""IsBicep"":""false""}"
"{""createdBy"":""Enterprise Systems"",""expireson"":""never"",""owner"":""Enterprise Systems"",""purpose"":""mailchimp sending domain (monthly news letter)"",""isBicep"":""false""}"
"{""created-by"":""terraform""}"
"{""expiresOn"":""never"",""createdBy"":""DevOps Team"",""owner"":""DevOps"",""location"":""eastus"",""environment"":""prd"",""purpose"":""Storage for Documents API""}"
"{""Status"":""BusinessFunction"",""Owner"":""DevOps"",""Application"":""Shared""}"
"{""purpose"":""Decision and Pricing"",""isBicep"":""true"",""Owner"":""DevOps"",""environment"":""prd"",""location"":""eastus""}"
"{""purpose"":""Decision and Pricing"",""isBicep"":""true"",""Owner"":""DevOps"",""environment"":""prd"",""location"":""eastus""}"
"{""owner"":""DevOps"",""environment"":""prd"",""purpose"":""Storage for SQL Bulk import"",""expiresOn"":""never"",""createdBy"":""DevOps Team"",""location"":""eastus""}"

I had to use the below to create a new column in resources table like you did for Application

Tags.Owner = 
VAR LookupKey = "owner"
VAR SearchField = Resources[Tags]
VAR FindLookupKey = SEARCH(LookupKey,SearchField,1,1)
VAR FindValueStartColon = SEARCH(":",SearchField,FindLookupKey,1)+2 
VAR FindValueStart = IF(MID(SearchField,FindValueStartColon,1)="""",FindValueStartColon+1,FindValueStartColon)
VAR FindValueEnd0 = SEARCH(",",SearchField,FindLookupKey,LEN(SearchField))-FindValueStartColon-2
VAR FindValueEnd = if(FindLookupKey=2,FindValueEnd0,FindValueEnd0+1)

RETURN TRIM(iferror(if(FindLookupKey>1, MID(SearchField,FindValueStart,FindValueEnd),""),""))
chris-bowman commented

Been too long before coming back to this, apologies. I think I found the issue, and am looking at the following change to properly handle the double quote / extra whitespace, which I'll include in a 3.2 update

VAR FindLookupKey = SEARCH(LookupKey,SearchField,1,1)
VAR FindValueStartColon = SEARCH(":",SearchField,FindLookupKey,1)+2
VAR FindValueStart = FindValueStartColon+IF(MID(SearchField,FindValueStartColon,1)="""",1,0)
VAR FindValueEnd = SEARCH(",",SearchField,FindLookupKey,LEN(SearchField)+1)-FindValueStart-1

RETURN TRIM(iferror(if(FindLookupKey>1, MID(SearchField,FindValueStart,FindValueEnd),""),""))

dswann1011 commented

Chris,
Thanks for the AWESOME template! I am in the same issue with tags as well. When will you have 3.2 ready?

dswann1011 commented

CHris--- Thanks!!! i will try it out!

chris-bowman commented

Let me know if it fixes the issue - the tag formula was doing my head in, but I think it works 😁

dswann1011 commented