3 Replies Latest reply on Aug 3, 2010 10:37 AM by sasuke

    reporting and sql

    sasuke
    sasuke Jul 16, 2010 10:00 AM

      Hey all

      I've got 2 questions for u.

      let me explain the situation.

      We have got several of our customers that can login to the solarwinds website and see a map of their network with their own nodes on it. Each customer has it's own login to the website. We also use this login name in a custom field on the node that we name CustomerName.

      We now want to create a report so that the customer can log in to the website and see the events of the last x-days. for this I created an sql query:

      use solarwinds

      DECLARE @Customerid varchar(30);
      SET @Customerid = 'customername';


      SELECT
      Nodes.NodeID AS NodeID,
      Events.EventTime AS Event_Time,
      Nodes.VendorIcon AS Vendor_Icon,
      Nodes.Caption AS NodeName,
      Events.Message AS Message

      FROM
      Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetObjectID)
      where CustomerName = @Customerid
      ORDER BY 2 DESC

      1) is it possible to use the login name from the website and put it in the @customerid value? This way I would need only 1 report and don't need to create a report for all the existing and new customers.

      2) when i run this query in sql sever management studio, it's like speedy gonzales, if I run it as an sql report in the solarwinds reporting tool, it doens't give me any results, it hangs on Querying database.

       

      Any idea's

      I'm on holiday for 2 weeks so I'm sorry if I don't reply quickly, but all answers are welcome.

      with kind regards

      sasuke

      • 415 Views
      • Tags:
          • Re: reporting and sql
            njoylif
            njoylif Expert Aug 2, 2010 11:51 AM (in response to sasuke)

            as far as point 2, ensure the user that the Orion report is running has rights to run the SQL command (whatever - check permissions).
            if you run it within report writer, I assume it hangs there too?  any error messages?

            I've never tried to create variables on the report side, maybe try making this a stored procedure and calling it from the report writer (should be able to send variable from report writer statement for customername, just have to have a report per customer still).

            anyways, some thoughts/things to try.

            for point 1, I would think there is a way, just how to access it...this would have to be SW or someone that plays with .net a lot.

            I could also use this type of thing as I have a few customers that see their info....

            1 of 1 people found this helpful
              • Re: reporting and sql
                sasuke
                sasuke Aug 3, 2010 10:37 AM (in response to njoylif)

                Hey njoylif

                 

                thanks for the reply.
                I guess you mean I have to check if the sql user I created for solarwinds has acces to build the report I created?
                I've just made this user an admin on the sql server. when I then connect as this user to the sql server and I run the query it works like a charm. If I try it again in the report writer and it is the same problem. Is there perhaps a solarwinds service that I should check and run as a user?

                As I was typing this message I just read about the stored procedure again. If I do it this way it seems to work fine so problem solved :)

                now for the first part.. any .net players arround?

                greetz

                sasuke