As we move to more modern systems like Kibana Elasticsearch from older systems we need simple ways to move our data. Here is a way to move a small amount of data (less than 1k records). This method will using MS SQL JSON features to create data Kibana can read. That is the tough part.. You just have to be creative in getting the data in a format you need. And that is what you can learn below.
If you use the FOR JSON SQL formatter, half your work is done. But the query needs to be created in such a way that the entire record set is not formatted as a JSON file. What Kibana needs is a JSON string representing the record, not the set of records.
This is the record layout needed:
{"_id":"c4645805-7aef-e911-aa1e-b0e52ccc70f6","showAll":1}
It would be nice to include the console command to import the record too. That looks like this:
PUT myIndex/_doc/c4645805-7aef-e911-aa1e-b0e52ccc70f6
We can combine those two lines into a single record output so each record looks like this:
PUT myIndex/_doc/c4645805-7aef-e911-aa1e-b0e52ccc70f6
{"_id":"c4645805-7aef-e911-aa1e-b0e52ccc70f6","showAll":1}
Don't worry about the space at the beginning of the second line. It will be ignored by the Kibana Elasticsearch DevTools Console.
This is the SQL query. Change it to fit your table layout and column names:
SELECT (CONCAT('PUT myIndexName/_doc/', LOWER(accountId), CHAR(13)+CHAR(10))),
(SELECT LOWER(accountId) AS '_id', 0 AS inactive, settingValue AS 'showAll'
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
FROM dbo.UserSettings
WHERE dbo.UserSettings.SettingName = 'showAll'
Once you have your query set up, select the query output to be text. There is a button in SMSS to do that. The query will then spit out two lines per record just like above. One line will be the console command, and the other will be the record data. Simply copy the data to the Kibana Elasticsearch DevTools Console, and run it. Your data should be imported quickly.
Warnings
Be careful, the data names and values are case sensitive, and if you have the wrong case or spelling, Kibana will create a new property name for that record. And your data will not be stored where you think it should be. For example, these are not the same value:
c4645805-7aef-e911-aa1e-b0e52ccc70f6 C4645805-7AEF-E911-AA1E-B0E52CCC70F6
And Kibana does not convert boolean values automatically. So 1 and true are not equal either.
For larger amounts of data you can use the Kibana bulk import endpoint. And you can use the command line bcp to bulk export your data. So the whole process could be automated.