SWQL display modification

I am not very expereinced with SWQL or SQL, I am just wondering if there is a way for me to edit the following code to display based on our requriedments and information that is already stored in the database?

This code is for us to display the Perfstack Projects created by our users as there is no easy way for us to share these projects without keep sending links around.

SELECT

DisplayName AS [Project]

,'/ui/perfstack/' + ProjectID AS [_LinkFor_Project]

,AccountID AS [Created By]

,UpdateDateTime AS [Last Updated On]

,CreateDateTime AS [Created On]

FROM Orion.PerfStack.Projects

ORDER BY DisplayName ASC

This is currently fine as it is, however I would like if I could possibly remove duplicate named projects and only display the one that has the most recent "UpdateDateTime", this would mean that everyone would always see the one report and it looks as if when someone saves it on their sides and updates it that its all the one report. We might change this in future to become embedded if there is more than one with the same name and people can see all the modifications by different users but not for now.

Find more posts tagged with

swql perfstack

custom_swql_query

widget

Accepted answers

All comments

wluther

@jack galloway Here is a link to a document I put out a while back, specifically related to viewing PerfStack projects.

Building Simple PerfStack Project Lists With SWQL

Would this be helpful to you at all?

Thank you,

-Will

jack_galloway

Hi Wluther,

Yes I am sure your post is where I got this orginal code from, I looking to have the code hide projects with duplicate project names and only show the one that has been updated most recently. Is this possible, so that when someone saves the same project on their profile and make changes then it looks like they just updated the same project without it displaying the same project saved by each user.

regards

Jack

wluther

jack galloway

I don't think you can have multiple projects with the same name.
If you create project "ABC", and send it to someone else (or if they choose it from the list of saved projects), and they edit it and save it, it will still be project "ABC".

You do have the option of "Save As", so you could make projects "ABC1", "ABC2", "ABC3". Then you can have the list only show the most recent project which starts with "ABC".

Additionally, I know the projects will show who created them, but I'm not sure if they show the last person to edit/save them.

I have not messed around with PerfStacks for a while, so let me dig in and see what I can find.

Thank you,

-Will

wluther

jack galloway

Okay, I created a test account, and with that account, created a new PerfStack project. ("New Analysis Project")

I then opened that project (via the SWQL links we built) as my account, but did NOT have the option to save the project.

I could, however, save as, and use the same name. ("New Analysis Project")

Once the project name existed for each user, I could not longer save/save as that same project name.

When that happened, I could certainly save as a different project name ("New Analysis Project2").

With the new project name, each user would be able to save as that new/same name.("New Analysis Project2")

But once each account had saved that version, they would run into the same issues, not being able to save it as the same name.

We could probably setup the query to display the most recent (updated) project with a project name LIKE 'New Analysis Project%'.

You would need to make sure everyone saved the project with the same name/prefix + new number/suffix, but it should only show the most recent of those projects.

Let us know what you think, and also if you have any further info/requirements for this to work.

Thank you,

-Will

jack_galloway

Yeah I know that users can't edit the project that was orginally created by another user and they save the project as the same name under their own account. I think its not a great method of working and being able to see others perfstack projects then being able to make modifications without this hassle is what needs to be setup, so I want to make this widget look like all the projects with the same name are the one thing and users just need to open, save as, edit and then save the project on their side for the widget to automatically update and replace the orginal one and keeps it all tidy by not having so many copies of the same project listed but under different users.

I just need a way to compare the "DisplayName" part of the database for duplicates, then pick the one that has the most recent "UpdateDateTime" and display that. Not sure if SQL/SWQL is able to do that.

thanks