(names of parent key of it, names of files whose(name of it as lowercase ends with ".mdf") of folders "DATA" of folders ((it as string) of values "SQLDataRoot" of it)) of keys "Setup" of keys ((it as string) of values of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL" of native registry) of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server" of native registry
I have a server with 3 db instances with different versions. This works for me and each line refers to one instance. But I cannot use the same way to get all the databases whose files end with .mdf. So I need to get it in an array. Other solution is welcome.
(names of parent key of it, names of files whose(name of it as lowercase ends with “.mdf”) of folders “DATA” of folders ((it as string) of values “SQLDataRoot” of it)) of keys “Setup” of keys ((it as string) of values of key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL” of native registry) of key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server” of native registry
Here is the output, but without the name of the instances that defined at “names of parent key of it”
“computer_property_209”: [
“master.mdf”,
“model.mdf”,
“MSDBData.mdf”,
“master.mdf”,
“model.mdf”,
“MSDBData.mdf”,
“master.mdf”,
“model.mdf”,
“MSDBData.mdf”,
“SSISDB.mdf”
]
So, the relevance specified by itself will not output the data in the format you reference above. Even aside from the fact that the name of the instances has been removed, there is reference to “computer_property_209” and some brackets, neither of which would be included in the output of the Client Relevance. As jgstew is asking, we need a bit more information as to how you are getting the specific output referenced above in order to be able to perhaps provide some guidance here.
For reference, the client relevance you’ve referenced returns a ‘tuple’, which has similarities to an array. Here’s an example output from one of my lab environments:
It might help to better understand better overall what it is you are trying to achieve with an array. I’m guessing this data is being used in an extraction of some kind, but more context is needed.
We need something like this. As you can see each line refer to one instance. It works dynamic. If it has more instances, it works the same. I am also satisfied if we get the .mdf databases like this.
You’ve still not posted your query or how you are obtaining the data in JSON format.
I do not think you will be able to represent this relationship in pure relevance in a single query. I think your easiest path would be to post-process the query in whatever script you have handling the REST call.
For a common case, see the “Installed Applications - Windows” Analysis, where we retrieve values like DisplayName and DisplayVersion, combine them into a single result with “|” symbol, and store that as a single result. The script retrieving the value would handle splitting on “|” to separate the values, if necessary.
(names of parent key of it, names of files whose(name of it as lowercase ends with “.mdf”) of folders “DATA” of folders ((it as string) of values “SQLDataRoot” of it)) of keys “Setup” of keys ((it as string) of values of key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL” of native registry) of key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server” of native registry
After having created a new computer property with this relevance in the Console and BFI I will also get a new property from the API. The output is in one bracket.
I hope there are more examples of an array. The Relevance site has only one simple example.
Do you have an example with the pipe? Is the output in one line for each instance and separate by the pipe?
Sure, the idea is just to put all of the “related” things in a single result and then pull them apart in the API query later.
q: (item 0 of it & "|" & item 1 of it) of (names of parent key of it, names of files whose(name of it as lowercase ends with ".mdf") of folders "DATA" of folders ((it as string) of values "SQLDataRoot" of it)) of keys "Setup" of keys ((it as string) of values of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL" of native registry) of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server" of native registry
A: MSSQL13.MSSQLSERVER|BESReporting.mdf
A: MSSQL13.MSSQLSERVER|BFEnterprise.mdf
A: MSSQL13.MSSQLSERVER|master.mdf
A: MSSQL13.MSSQLSERVER|model.mdf
A: MSSQL13.MSSQLSERVER|MSDBData.mdf
A: MSSQL13.MSSQLSERVER|temadb.mdf
A: MSSQL13.MSSQLSERVER|tempdb.mdf
A: MSSQL13.MSSQLSERVER|tem_analytics.mdf
A: MSSQL13.MSSQLSERVER|Test.mdf
A: MSSQL13.MSSQLSERVER|TRCDB.mdf
Otherwise, JSON does not guarantee that the order of array elements from one key match the same order as the array elements in another key; there wouldn’t be a way to verify which database names go with which database instance name.
There’s nothing special about the “pipe” operator here, except that we know it won’t be part of an instance name or file name. You could use any delimiter you like, or a combination of delimiters like “:::”
