Automating download of UHC files from website

[Tkurzendoerfer]

Apparently UHC is using a javascript on their webpage that makes automating the download of files out of the ordinary.
Has anyone found a way to automate the downloading of files. Here is an example from one of their index files: https://transparency-in-coverage.uhc.com/?file=2022-07-03_UnitedHealthcare-Insurance-Company_Navigate_allowed-amounts.json.gz&origin=uhc

But, that only works in a browser, if we try that to download with WebClient in c# it only downloads the web page HTML.

Surely, someone has run into this problem already and has found a way to accomplish the intended purpose of the index file which is to help automate the downloading of the files in the index.

[nmayle]

I would think that this and similar techniques that make the files hard to download are not in keeping with the language of the rule (bold added by me) "The Departments also proposed to allow plans and issuers flexibility to publish the files in the locations of their choosing based upon their superior knowledge of their website traffic and the places on their website where the machine-readable files would be readily accessible by the intended users."

The intended users are defined in other language in the rule: "The intended audience also includes health care stakeholders such as researchers, legislators, and regulators, as well as application developers who could make the information usable and easily understood by laypersons. Accordingly, application developers will be able to access the data in a format that is easily used and understood using skills common to application developers."

Assuming you are an "application developer", it seems that your statement of the problem you are facing downloading the file makes it clear that what is required is beyond the "skills common to application developers".

This may have been unintentional and simply web developers not thinking about the intended users. In that case, it should be easy for uhc to fix this by improving their presentation of the list of files. The language of the guidance on GitHub can also be improved to make it clear that the files should be easy to download. "These machine-readable files must be made available to the public without restrictions that would impede the re-use of that information." was a good start, but it didn't anticipate all of the possible ways one could use to make it hard to download the MRFs.

[Tkurzendoerfer]

Thanks for your reply. I am hoping other developers are reading this and can tell me if there is a way around this but since the Index file clearly includes the 'location' of the file. The download should be as simple as referencing the clear text in the 'location' value and plugging that into the Webclient to download the file. But, I suspect the way UHC wrote the web page the javascript is interpreting the user click on the web page and translating that into the actual location of the file. That is not something we should have to do. The 'location' should be the actual location of the file.

[jasheldo]

I'm experiencing this same problem. The URLs in the index files don't actually point to the file but to a JS object. While I can't prove intent, there's no way this was unintentional. Especially given how other Payers managed to list their files. It feels this site was constructed to be a user/developer hostile as possible while giving the appearance of compliance.

[Tkurzendoerfer]

Thank you for your reply. I don't want to imply intent but this at the very least was a very clear oversight. The clear intention of the 'location' property is to give the actual location of the file but instead they are giving a URL with a command line property that only a browser can interpret through javascript to find the actual location of the file. Interesting approach but I think CMS will see this as a violation of the intent of the location property. I am contacting CMS now to start the process for review.

[Tkurzendoerfer]

Thank you for your response.

I tried that URL and it didn't work. Everything after the question mark (?) is a command line argument that is usually information that the server side program interprets or the javascript interprets, so I wasn't optimistic this would work.

And when you cut and paste everything before the ? https://mrfstorageprod.blob.core.windows.net/mrf-even/2022-07-03_UnitedHealthcare-Insurance-Company_Navigate_allowed-amounts.json.gz

Then you get a message from the server saying that we are not authorized. So, this clearly wasn't intented for the publice to access directly, it is being handled in a script to retrieve the file from its actual location and return it to a browser.

[RonPeters]

As an Azure developer, I understand that URL format. It's a lease to access the actual file, and that lease expires after a certain time at which point you must generate a new lease URL. Azure blob storage handles the internals of validating the query string. I use this pattern extensively in scenarios that require high security. What makes their implementation almost pointless is their API to generate the leases is public, so it's not actually secure. I guess they could implement some kind of DDoS protection via the API, but really they could let the Azure handle that. In short, I support flagging this as a violation of the spirit of the spec. They should just post a link to publicly accessible Azure blob storage (or a CDN). Get Outlook for Android<https://aka.ms/AAb9ysg>

…

________________________________ From: Tkurzendoerfer ***@***.***> Sent: Thursday, July 7, 2022 11:34:29 AM To: CMSgov/price-transparency-guide ***@***.***> Cc: Subscribed ***@***.***> Subject: Re: [CMSgov/price-transparency-guide] Automating download of UHC files from website (Discussion #534) Thank you for your response. I tried that URL and it didn't work. Everything after the question mark (?) is a command line argument that is usually information that the server side program interprets or the javascript interprets, so I wasn't optimistic this would work. And when you cut and paste everything before the ? https://mrfstorageprod.blob.core.windows.net/mrf-even/2022-07-03_UnitedHealthcare-Insurance-Company_Navigate_allowed-amounts.json.gz Then you get a message from the server saying that we are not authorized. So, this clearly wasn't intented for the publice to access directly, it is being handled in a script to retrieve the file from its actual location and return it to a browser. — Reply to this email directly, view it on GitHub<#534 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ASF44GJD5A2OHVVUFMV2QYDVS4PLLANCNFSM523PSTYA>. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[Tkurzendoerfer]

Thank you for your reply. I am not sure what an Azure developer is but I know what Microsoft Azure services are. You may be correct, these types of APIs are meant for security reasons but as you mentioned the intent is to make these files public. They are supposed to be available to everyone. But, more importantly, if this isn't a violation then companies like ours will have to hire a full time person to click on 77,000 files in order to download them. My guess is that the web developer was not informed how these files were to be accessible programatically and not require human activity to download them. And the person who created the data never realized that companies like ours were going to download everything programatically. I have already submitted an inquiry on the CMS website about this issue.

[DrZeshanKhan]

I have spent a full week trying to figure out how to open these stupid files, this is NOT TRANSPARENT. They have made this as challenging as possible!

[Tkurzendoerfer]

Write a program to break up the file into smaller files. Read 1000 bytes for instance and write that to a file. Do that through the whole file and sequentially number the output file names. Then you can open and look at any part of the file. I did that and it helped me. I also am reading the file sequetially using the json object in .NET. This allows you to write code for each object in the file struction just by looking at the object property name and value. With that and knowing the structure of the file you can traverse through the file and load a database which mimics the structrure of the flat files. Once in a relational database you can transform the data into any other structure that suits your user's 'search criteria' needs.

[tjmc]

Not the best but you can grab the file names from

https://uhc-sas-function-apim-prod.azure-api.net/api/blobs-function

-----
{
  "blobs": [
    {
      "name": "2022-07-01_02-GLOBAL-CHAUFFEURED-SERVICE_index.json",
      "origin": "uhc"
    },
    {
      "name": "2022-07-01_1-BETTER-LLC_index.json",
      "origin": "uhc"
    },
...

Then use the filename to request the sasUrl from

https://uhc-sas-function-apim-prod.azure-api.net/api/blob-sas?blobName=2022-07-01_02-GLOBAL-CHAUFFEURED-SERVICE_index.json
-----
{
  "sasUrl": "https://mrfstorageprod.blob.core.windows.net/mrf-even/2022-07-01_02-GLOBAL-CHAUFFEURED-SERVICE_index.json?blahblahblah"
}

Then use the sasUrl to download the file.

[lkoll]

Can I ask how you came across this alternative path to the files? I'm just trying to understand if this is something that will be persistently available.

Also, I seem to be getting AuthenticationFailure error when I put the sasUrl into my browser to check it out.

This XML file does not appear to have any style information associated with it. The document tree is shown below.
<Error>
<script id="CitrixBcrApi"/>
<Code>AuthenticationFailed</Code>
<Message>Server failed to authenticate the request. Make sure the value of Authorization header is formed correctly including the signature. RequestId:e4ff1564-c01e-0074-31d8-a1dc1f000000 Time:2022-07-27T16:48:31.1313860Z</Message>
<AuthenticationErrorDetail>Signature did not match. String to sign used was r 2022-07-25T14:56:08Z 2022-07-29T14:56:08Z /blob/mrfstorageprod/mrf-even/2022-07-01_02-GLOBAL-CHAUFFEURED-SERVICE_index.json 89efab61-5daa-4cf2-aa04-ce3ba9d1d1e8 db05faca-c82a-4b9d-b9c5-0f64b6755421 2022-07-25T14:56:08Z 2022-07-29T14:56:08Z b 2021-04-10 2021-04-10 b attachment" </AuthenticationErrorDetail>
</Error>

Edit: to be clear I seem to be able to get the list of files from the first url you list, and then retrieve the sasURL from the second URL, but then when I try to use the sasURL it returns the above error.

[tjmc]

I found it watching the network traffic when requesting https://transparency-in-coverage.uhc.com/

I'm not sure why you're seeing that error. Maybe try it with curl and see if there is anything useful in the output.
curl https://uhc-sas-function-apim-prod.azure-api.net/api/blob-sas\?blobName\=2022-07-01_02-GLOBAL-CHAUFFEURED-SERVICE_index.json | sed -n "s/.*\(https.\+\)\"/\1/p" | tr -d '\r\n' | xargs curl

[jasheldo]

Awesome! I actually found a simple answer is to use a headless browser with Playwright. It’s not fast but it gets the job done.

…

Sent from my iPhone

On Jul 17, 2022, at 7:43 PM, TJ ***@***.***> wrote:  Not the best but you can grab the file names from https://uhc-sas-function-apim-prod.azure-api.net/api/blobs-function ----- { "blobs": [ { "name": "2022-07-01_02-GLOBAL-CHAUFFEURED-SERVICE_index.json", "origin": "uhc" }, { "name": "2022-07-01_1-BETTER-LLC_index.json", "origin": "uhc" }, ... Then use the filename to request the sasUrl from https://uhc-sas-function-apim-prod.azure-api.net/api/blob-sas?blobName=2022-07-01_02-GLOBAL-CHAUFFEURED-SERVICE_index.json ----- { "sasUrl": "https://mrfstorageprod.blob.core.windows.net/mrf-even/2022-07-01_02-GLOBAL-CHAUFFEURED-SERVICE_index.json?blahblahblah" } Then use the sasUrl to download the file. — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.

[lkoll]

@Tkurzendoerfer did you ever find a good workaround? I'm currently dealing with this issue and am curious if there has been any further developments.

[sam-pullman]

I believe a report was made, I've been on UHC's site and it does not appear that they've fixed the issue yet, still lots of JS.

[Tkurzendoerfer]

There was a suggestion I haven’t tried yet but sounds like it would work. The suggestion was to use an object in .NET which simulates a browser. From: lkoll ***@***.***> Sent: Wednesday, July 27, 2022 1:02 PM To: CMSgov/price-transparency-guide ***@***.***> Cc: Tkurzendoerfer ***@***.***>; Mention ***@***.***> Subject: Re: [CMSgov/price-transparency-guide] Automating download of UHC files from website (Discussion #534) @Tkurzendoerfer <https://github.com/Tkurzendoerfer> did you ever find a good workaround? I'm currently dealing with this issue and am curious if there has been any further developments. — Reply to this email directly, <#534 (comment)> view it on GitHub, or <https://github.com/notifications/unsubscribe-auth/AX2PY5VOD7EVJHI54XCIQWTVWFTP5ANCNFSM523PSTYA> unsubscribe. You are receiving this because you were mentioned. <https://github.com/notifications/beacon/AX2PY5W3IOW5PRXIGMC6VALVWFTP5A5CNFSM523PSTYKYY3PNVWWK3TUL52HS4DFWFCGS43DOVZXG2LPNZBW63LNMVXHJKTDN5WW2ZLOORPWSZGOAAY4T2Q.gif> Message ID: < ***@***.***> ***@***.***>

[Tkurzendoerfer]

Yes, I reported it. But I have not heard back from CMS. We have reported HIPAA EDI issues in the past with CMS and we usually at least receive an acknowledgement about the fact that they received the notice and can get some status information. But, this process is new and it may take some time to get procedures in place to inform us on status and any decisions that have been made. From: Sam Pullman ***@***.***> Sent: Thursday, July 28, 2022 8:23 AM To: CMSgov/price-transparency-guide ***@***.***> Cc: Tkurzendoerfer ***@***.***>; Mention ***@***.***> Subject: Re: [CMSgov/price-transparency-guide] Automating download of UHC files from website (Discussion #534) I believe a report was made, I've been on UHC's site and it does not appear that they've fixed the issue yet, still lots of JS. — Reply to this email directly, view it on GitHub <#534 (reply in thread)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/AX2PY5QSLAM4CHAAVFDDETDVWJ3T7ANCNFSM523PSTYA> . You are receiving this because you were mentioned. <https://github.com/notifications/beacon/AX2PY5XDHE3SQRMMQVMBDC3VWJ3T7A5CNFSM523PSTYKYY3PNVWWK3TUL52HS4DFWFCGS43DOVZXG2LPNZBW63LNMVXHJKTDN5WW2ZLOORPWSZGOAAY6FCA.gif> Message ID: ***@***.*** ***@***.***> >

[gardnmi]

This is a python snippet that will provide you with all the download urls.

import requests

resp = requests.get('https://transparency-in-coverage.uhc.com/api/v1/uhc/blobs/')

resp.json()['blobs']

[miiiiink]

Absolute champion king of kings god among men THANK YOU

[hw-monkey]

Thank you so much!!

Aetna seems to be doing something similar. Any chance you have discovered a similar method for their MRFs?

[gardnmi]

When you inspect the network requests this json file shows up. It looks like it might contain all the mrfs.

https://mrf.healthsparq.com/aetnacvs-egress.nophi.kyruushsq.com/prd/mrf/AETNACVS_I/ALICSI/latest_metadata.json

[jasheldo]

Just use a headless browser. It’ll render the JavaScript. Then, find the elements you want once the HTML is fully loaded and one at a time, send a click over. It’s best to use ‘async’ here. As long as your code is told to expect a download you can then save the download with the proper name and move on to the next file. You don’t need all files which is what makes this approach tenable.

…

Sent from my iPhone

On Jul 27, 2022, at 12:01 PM, lkoll ***@***.***> wrote:  @Tkurzendoerfer did you ever find a good workaround? I'm currently dealing with this issue and am curious if there has been any further developments. — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you commented.

[aaronmefford]

This is not a great solution for files that are many gigabytes in size.

[jasheldo]

It’s been working out perfectly well for our organization. We manage to get files and have them analysis ready for all major payers each month in about 2 days. Easy peasey.

[gardnmi]

I'm not familiar with other cloud storage providers but I personally just stream the data from the source to my s3 storage location using a combination of python's urllib and boto3 upload_fileobj. I never need to actually download anything to my local machine.

[gl2007]

@gardnmi would you please share the code you mentioned about streaming?

[gardnmi]

@ghanashyaml I can't share the code as I don't own it. I can get you in touch with the company I was involved with if you are interested.

[andromedav1]

@gardnmi Is there a way we can connect on zoom or google meet? I would like to ask some questions and exchange some ideas with you. Thank you.

[gl2007]

Thank you @gardnmi. I understand, but the company may not also allow to share the code. Do you have any samples or ideas of how you got to implement the solution?