Forum Discussion
MicrosoftExternal file support in KQL - Azure Sentinel
Sanket26 Yes. See the following for an example:
MicrosoftThe issue which I am facing right now is : The html page where the csv is hosted isn't in desired format (There are multiple lines of header before the actual data). Also downloading the file, modifying the format and then uploading to a blob isn't the best option for me.
I am getting this error :
Partial query failure: Wrong number of fields (E_WRONG_NUMBER_OF_FIELDS). (message: 'Kusto::Csv::Parser<>.PrepareFields: CSV has an inconsistent number of fields per line: ', details: 'Offending record: 10 (start position in stream: 531), fieldsCount: 4, currentRecordFieldCount: 4, record: # ja3_md5,Firstseen,Lastseen,Listingreason
[end record]')
Does it need to be in Log Analytics/Azure Sentinel using KQL?
- Sanket26
Please find the link details : https://sslbl.abuse.ch/blacklist/ja3_fingerprints.csv
Also yes I was running this data in Azure sentinel.
There you go Sanket26
externaldata (Everything:string) [@"https://sslbl.abuse.ch/blacklist/ja3_fingerprints.csv"] with (format="txt",ignoreFirstRecord=true) // reading each line as a string | where Everything !startswith "#" //removing the lines that started with '#' | project Everything=parse_csv(Everything) // parsing the string as csv | project ja3_md5=Everything[0],Firstseen=Everything[1],Lastseen=Everything[2], Listingreason=Everything[3] //splitting the csv into columnsI've added some comments for you to know what I was doing.
Let me know if this was helpful!
- Sanket26
Thank a lot. It really helped. The issue is resolved. I am now able to fetch data directly from the http page. The part I was missing was I didn't perform the parsing on the csv as a result I wasn't getting the schema as expected.