We’re trying to ingest data generated by SQL queries on a regular basis that can be exported into a local directory in CSV, JSON, XML, etc. Is Watch Directory the best way to do this? Does anyone know what might be the best format to accomplish this?
This is actually a good question that I don’t think I’ve attempted before. Traditionally it pulls .txt and .log files just fine.
JSON should work just fine and would probably your best bet as long as in the JSON file each new entry is it’s own JSON object. Having it in JSON ensures that it will be parsed automatically.
{"foo":"bar1","herp":"derp1"}
{"foo":"bar2","herp":"derp2"}
Were you ever able to get this figured out? We are actually doing the same exact thing, but IDR refuses to parse the JSON format we are using, even though we are compliant with the RFC version they mention in their documents. We are using the format as below.
[
{
“value”: “/Date(1678813970320)/”,
“Username”: “USERNAME”,
“Program”: “SOME PROGRAM”,
“SQL Text”: “SELECT * FROM SOMETHING”,
“Database”: “MY DATABASE”,
“Timestamp”: “/Date(1678813970320)/”,
“DateTime”: “Tuesday, March 14, 2023 1:12:50 PM”
},
{
“value”: “/Date(1678814286320)/”,
“Username”: “USERNAME”,
“Program”: “SSMS OR WHATEVER”,
“SQL Text”: “SELECT * FROM SOMETHING”,
“Database”: “MY DATABASE”,
“Timestamp”: “/Date(1678814286320)/”,
“DateTime”: “Tuesday, March 14, 2023 1:18:06 PM”
}
]
@dwilliams what you have provided is not valid JSON, its an array of two JSON objects.
In order for us to parse native JSON it should pass a JSON linter, see this example
valid JSON would be two separate objects, newline separated. The first would look like this
David
Also I’m not sure if it was curly double quotes in your source of if something happened during copy and pasting, but those stylized quotes also seem to cause issues with parsers i.e. “” as opposed to “”
David