How to increase the amount of results

[CHRISTIAN_UMREIN]

Hi all,

how can I increase to amount of result in the value-field when I run a query?

My results are limited to 9999 values.

Thanks,

Chris

[goodrum]

This might be a hard coded limit but my question is why you need that many results?  A filter will pass back the top value based on the query(or queries).  Further to that, large queries can have a performance impact while doing the planning phase, especially if the query must be done multiple times in a workflow.  Would you mind sharing the query?

[lornedornak]

I don't mean to hijack, but in our environment, the hard coded limit also impacts us.  If I want to run a query to show all files in a volume that haven't been accessed in x amount of time, this result is limited by the hard coded limit.  This basically makes the query useless if ther are more than 9999 files that should be returned.

[hill]

It would help to understand a few of things:

• I'm assuming the query you're looking for is for a Finder to use in a repeat row, to loop through all potential results.  If for user inputs... i would have to ask "why" would you want more than 10k results for user input when you can only select one
• What is the use case?  Meaning for the recent example, finding all files that haven't been accessed in x amount of time.  What would you then do?  Also, assuming the limit is hard-coded at '9999'... is there any issue with running the workflow multiple times?  I do realize that it is preferable to do "everything" in one shot... but I'd like to understand if there would be a problem with running the workflow 3 times if you found 25K files that haven't been accessed in x amount of time.

Thanks,

Kevin

[lornedornak]

Use case:

A group share has hit it's quota or volume limit and a representative of the data requests a increase in capacity.  Before increasing the capacity, I'd like to submit a report to the user showing them the amount of data and files that has not been accessed in 5 years.  This will allow opportunities for clean up.

In this case, I'd run a SRM report based on least recently access files.  I'd then strip out all the data that's newer than 5 years old and hand the report to the user.  With the 9999 hard limit, if there are more than 9999 files that show up on the list (one file per row), I'd have to give the report to the user, have them clean up, run the report again, wash, rinse, repeat.  This is not a sustainable solution.

[goodrum]

Interesting use case to say the least.  What are you automating with the report?  Honestly, this would be a great use case for OnCommand Report or DFM SRM.  I assume that you have created your own WFA cache query because a list of files is not gathered by default.  Since you are providing a report, I would think that OCR or SRM reports would help you provide that information faster to the end customer.  WFA would be a better fit for handling the quota update and simplifying the actions.  From the use case, it doesn't seem that you are using the filters/finders to determine if there is a 'less than X number of old files' value to automate the action.  If you are, then a possible (albeit performance impact on the database) would be to do a SELECT COUNT(id) FROM table WHERE atime>5yrs type query might work but.... this would be a pretty painful query not to mention database requirement.  After getting the COUNT, you could do a conditional statement to enable the action if the value is less than a predetermined count of old files.  Theoretically... of course

Where is this information coming from?  Is this a separate datasource than DFM or did you create your own DFM cache query?

Again, it is an interesting use case.  I would like to here from the OP on his use case.

[hill]

Hi, to go along with what Jeremy indicated... WFA is not a REPORTING engine.  Jeremy correctly identified both OnCommand Unified Manager and OnCommand Report as better tools to facilitate your reporting needs.

Best,

Kevin.

[CHRISTIAN_UMREIN]

Hi all,

we are are large enterprise with more than 20.000 servers.

In my case I have a sql query against our cmdb that returns me all productiv servers in our environment.

This is neccessary because I'm implementing our storage- and backup requests. A finder wouldn't help, I need the possibility to see all our servers.

Cheers,

Chris

[goodrum]

Hey Chris, thanks for giving us more detail around your goals.  The SQL statements in the Filters will only display up to 9,999 records (again, assuming this is hard coded), however, this does not preclude the fact that there might be 999,999,999 entries in the database (over exaggeration to explain point).  The real issue is that only one result can be sent to a variable and Workflow Automation has no way to dynamically create a report.  Now, if this is being used in drop downs... I will cover that in a minute.  The point is that regardless of the number of results that can be displayed, for all intents and purposes, the limit is really 1 (yes, I said one).

I said I would cover the drop downs section which is one of the ways that we display multiple entries.  Say for example, you have a cache query that displays the >20,000 server names of the servers in your environment.  This query is used to provide some type of action to be taken.  The workflow doesn't really matter.  In this case, it would be a very difficult journey to 'find the right server' because I would be scrolling through one server name after another.  Since I have worked with major telecoms with hundreds of thousands of servers/virtual machines, I know how challenging the naming of server can be and after a while they all start to look the same.  So what could be done...

A great option would be a way to start filtering out the results before I actually run the query.  I could have a drop down that does a SELECT DISTINCT on the Operating System type (if my datasource table contains this information) and then have another SELECT DISTINCT for the Datacenter location (this doesn't have to be a QUERY and could just be a ENUM... if the datacenter name is embedded into the server name).  Now I have two very helpful filter points.  Maybe I can further filter, but this should give me a more manageable list.  As an aside, if the host names are not structured this way then other steps could be taken.  This is an example.

Key Points:

• Workflow Automation cannot generate any reports.
• Filters really only return one result in the end
• Dropdowns can be manipulated via multiple filters

If you want to shoot a more detailed use case to me, then I might be able to help figure out how to 'get around' the limitations.  Otherwise, if you need something that can report, then I would definitely recommend looking at OnCommand UM or Report

-Jeremy

[yaronh]

A small contribution from me:

As far as executing workflows and working with drop-downs to handle the user inputs for them - We do see HUGE lists as quite unmanageable.

One of the features we have for user inputs is cascading queries: Selecting a value and feeding it to the next query.

That may as well go with text fields, so you can implement a "wildcard" field for easy searching and thus never

need to see 20K long lists, and also have easier time to search for what you need.

Here's an example:

I Created a user input called "name_pattern" which is a string.

Then i can embed this in the list query as "Select server_name from server_table where name like '%${name_pattern}%'

That would give you a shorter list based on the pattern. Quite useful....

Attached a short preso illustrating all kinds of advanced way to handle user inputs (Its on 1.1.1 but can be easily adapted).

Hope that helps.

Yaron Haimsohn

WFA team

[CHRISTIAN_UMREIN]

Hi Jeremy,

thanks for your reply.

I already have limited the results of my servers by a select distinct.

In the database the value of all entries is 18.549.

Yaron's idea to use a autocomplete in the user input field is great but I didn't understand how to implement this.

The table where our servers are listed is called elch.server (like storage.array) and has only two colums: id (PK), hostname (varchar)

I will not give the customer the possibility two have a free text field - there can be too much mistakes.

Is there a way to define the user input as a autocomplete, for example the customer starts entering the letters "sv15" and I will get all servers starting with sv15?

Thanks,

Chris

[goodrum · Accepted solution]

Well, there is a way to have a free text field which feeds the drop down.  This will allow you to get the best of both worlds.  For example:

I have an userInput named $serverFilter which is find in a FindChart as a return variable and is not used anywhere else.  I can create a second userInput named $serverName which will be used to actual process.  I set the second to a query type like so:

SELECT

   hostname

FROM

   elch.server

WHERE

   hostname LIKE '${serverFilter}%'

This will return the list of servers based on your original input.  Now, I know that you could create one userInput and start typing in the dropdown to which  the selection will jump to the closest match.  This would work if the number of records was under 9,999 records. 

Another option would be to add other fields into the table.  Things like datacenter location, application type, OS type, etc will help to do the same thing.