This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Struggling!! I need a basic report showing the output of a SQL query.

LatteLarry over 2 years ago

I'm struggling here...

All I need is a report showing the output of a SQL query. The output will have 3 columns and can have anywhere from 0 rows to 100+ rows. Report will run once a day. I cannot figure out for the life of me how to create a report with this. Please someone help!!

Top Replies

Parents

0 jm_sysadmin over 2 years ago

Why don't you give us some more details. What do you want to see in the report? The numbers of columns and rows isn't enough to let me help.
Cancel
Vote Up +1 Vote Down

Cancel

Reply

0 jm_sysadmin over 2 years ago

Why don't you give us some more details. What do you want to see in the report? The numbers of columns and rows isn't enough to let me help.
Cancel
Vote Up +1 Vote Down

Cancel

Children

0 LatteLarry over 2 years ago in reply to jm_sysadmin

jm_sysadmin Sure, and thanks! First off, the query IS NOT being run against the Orion DB. It would be run against a different DB on a SQL server not associated to Orion other than it being added as a node in Orion.

Below is an example of the output the query may return. It could have more rows or it could have no rows at all. I have a team that wants this query executed daily. The ideal situation is no count of events (no rows), but they want to know if there are events because that means problems. The query I have can also take a bit of time to execute; sometimes it can take an hour. They would like to see the output, if there is any. I considered using SAM SQL User Experience Monitor for this but I don't think it would do exactly what I need, and I'm not sure how to rework the query to make it friendly for SAM SQL UEM component.

Max Eventtime Interactionid CountofEvents
----------------------- ---------------- -------
2022-05-06 19:37:19.000 0H10H731VHHNQR3A 2,196,288
2022-05-06 19:37:16.000 0H10H731VHHNQVTT 4,760,762
2022-05-04 14:03:58.000 0H10H431A7J01VRP 12,994,475
Cancel
Vote Up 0 Vote Down

Cancel
0 jm_sysadmin over 2 years ago in reply to LatteLarry

So @shuth reviewed the SQL UEM pretty well, but I guess I am not sure why this data needs to be in Orion. I do have data that might be useful to Orion users in other systems, and at that point I make a link out to it. (Things like the CMDB and its reports, our packet capture and analysis tool, things in vCenter, etc.) If you need to alert on it, you like can make a SQL query that fits the UEM format.

If the data you have is just a SQL db, and you need a place to create a report from a query, I am not sure Orion is right. I would be looking at SRSS or PowerBI or even a PowerShell script to run nightly and build an export.

That's not to say that you can't do it, Orion is pretty flexible and AlexSoul did some great things in this series: (+) Prerequisites - Forum - Server & Application Monitor (SAM) - THWACK (solarwinds.com)

There he sets up a secondary database and uses it to achieve things that were challenges at the time. Personally, I would find a tool meant to meet the need I have, and not push Orion to far outside its intended use.
Cancel
Vote Up +1 Vote Down

Cancel
0 bobmarley over 2 years ago in reply to jm_sysadmin

I was thinking the same, it probably would be served better using a different tool. Perhaps run a powershell script via Windows task scheduler, have the script massage the data in any manner you wish and save it as a .csv or even as a .html into your Orion Web directory and display it as a Custom HTML resource. That I have done before.
Cancel
Vote Up 0 Vote Down

Cancel
0 bobmarley over 2 years ago in reply to bobmarley

I looked at how we set it up and here you go

A powershell script runs daily as a scheduled task. The script uses the Invoke-Sqlcmd -Query to get the data. Once retrieved it is manipulated in powershell then written out like this:

Out-File -FilePath 'C:\inetpub\SolarWinds\Orion\MyOrionData.txt'

Then we set up a Custom HTML resource with the code below to read the above file. Not pretty but it works fine.

<html>
<body>

<embed src="MyOrionData.txt" width="575" height="100">

</body>
</html>

In our case we were just looking for nodes that did not get synced between system A and system B so a text output was all we needed, and it didn't need to look pretty. You could add whatever formatting you need to the HTML to make it look better.
Cancel
Vote Up +1 Vote Down

Cancel