Your company's ad could live here and reach over 50,000 people a month!

Share This Post

Wild Card Prompts

By Garth Jones

Q: Can the input variable that is query based be searchable similar to the native SCCM Web Reporting interface? i.e. Can the Collection ID be searched using a wild card rather than having to select one from a drop down only?

Background: Let us assume that we want to mimic the old ASP report prompts as best as we can. Let‘s also assume that we want a list of PCs and User Name OS for the PCs.

There are a few ways to do this depending on exactly what you are trying to do.

A basic SQL query looks like this:

Select 
      R.Name0,
      R.User_Name0,
      ROU.System_OU_Name0
from 
      dbo.v_R_System R
      join dbo.v_RA_System_SystemOUName ROU on R.ResourceID = ROU.ResourceID
Where
      R.Name0 = @PC 
Order by 
      R.Name0

A basic prompt looks like this:

Select Distinct 
      CS.Name0 as 'PC Name'
From 
      dbo.v_GS_COMPUTER_SYSTEM CS
Order by 
      CS.Name0

Option 1: Free From Text Box

  • Go to the prompt and change the available values to Non-queried. This makes the prompt a free form text box.

clip_image002

Results look like this

clip_image004

Option 2: – Use a Prompt to Filter Results of Another Prompt

· Keep the existing queried prompt

· Create a new prompt called Filter

· Set the default values of the new prompt to %%

clip_image006

· Change the Prompt Query to add the following line:

Where

cs.name0 like @filter

When you run the report you will get something like this.

clip_image008

Notice that when I enter the data in the Filter section of the Prompts, it filters out the results in the drop down.

clip_image010

Option 3: Multi-Value

  • Change the main query’s where clause to:

Where

R.Name0 in (@PC)

  • On the prompt for PC select the Multi-value option.

clip_image012

Here are the results. Notice that I combined both Options 2 and 3!

clip_image014

Share This Post

Enhansoft specializes in providing software solutions, custom reports, and consulting services for retrieving your IT asset inventory. This is done within the system management area focusing on Microsoft System Center Configuration Manager (SCCM) by extending its asset information and reporting capabilities. Enhansoft is a Bronze sponsor of myITforum.com.