Can we import text and excel file in Dynatrace query

[kumarv14]

Hello all/ @krzysztof_hoja 

Could you please advise on how to import a TXT or XLS file into Dynatrace so that it can be used within a query? Specifically, I would like to read IMSI values from the file and use them dynamically within the query, such that each IMSI is processed one by one, and the corresponding results are generated and exported into an output file.

fetch logs, scanLimitGBytes:-1
| filter dt.system.bucket=="custom_digital_" and index=="_db"
| filter Market=="IN" and IMSI=="20xxx"

|fields timestamp, AuditMessage,TransactionType

IMSI details 

3xx4490
7xx6595
2416xx
36334
454xx

Thanks in advance !!!

[Tommaso_Fin]

Hi, did you checked lookup tables?

Lookup data in Grail — Dynatrace Docs

[MaximilianoML]

Vote up @Tommaso_Fin 😁

[MaximilianoML]

Hello @kumarv14 ,

Yes, this can be handled by storing the IMSI list as lookup data in Grail, and then using that lookup table inside your DQL query.

In this case, I would not try to loop through the IMSIs one by one manually. A cleaner approach is to upload the values as a lookup table and then correlate them with your logs in a single query.

Example IMSI file:

IMSI
3xx4490
7xx6595
2416xx
36334
454xx

The file should preferably be prepared as CSV. If the source is an Excel file, convert it to CSV first and then upload it as lookup data.

Example lookup path:

/lookups/imsi_list

Example DQL:

load "/lookups/imsi_list"
| dedup IMSI
| join [
    fetch logs, scanLimitGBytes:-1
    | filter dt.system.bucket == "custom_digital_"
    | filter index == "_db"
    | filter Market == "IN"
    | fields timestamp, IMSI, AuditMessage, TransactionType
], on:{IMSI}
| fields
    timestamp = right.timestamp,
    IMSI,
    AuditMessage = right.AuditMessage,
    TransactionType = right.TransactionType

This will compare the IMSI values from the uploaded lookup table with the IMSI field in the logs, and return the corresponding matching records.

Once the query returns the expected results, the output can be exported from the table view as a CSV file.

One important detail: if values such as 3xx4490 or 7xx6595 are only anonymized examples, then the query above is appropriate. However, if the x characters are meant to behave as wildcards, then an exact join will not match them, and a pattern-matching approach would be needed instead.

I hope it helps you 😃