Create a view to show distinct/unique Model from amSoftInstall

Hi,

I'm trying to create a view where it shows distinct/unique model name from amSoftInstall.

I would like to create a view from AM Client - I can filter out but not make it 'distinct'. Any idea how I can make it show only unique model names?

I've tried creating from AM Web Widgets, but cannot export more than 100 records. I have 6K - 18K of records.

I've tried from AM Export Tool - possible but not a view and not accessible via AM Web.

TIA

Parents
  • Not sure I understand completely since counts of each cannot be obtained, however, grouping can at least give you the distinct models used in amSoftInstall (when group branch(es) is(are) minimized)...

       Grouping can be found under triangle (to left of counter) and set to Model.Name.

    If possible from AM Export Tool, then I assume content from where clause can be copied/pasted into related AM Client screen using query filter or prism (prism also left of counter).

    All distinct/unique related models have a Nature where 'Also create' equals "Software installations or utilizations (amSoftInstall)".

    To see which distinct/unique models are used in amSoftInstall, open the Models screen in AM Client, open query filter (or click on prism), and add the following...

    lModelId IN (SELECT DISTINCT a.lModelId FROM amSoftInstall a WHERE a.lModelId <> 0)

  • Hi Mark,

    Thanks for your answers

    On amSoftInstall, when grouped by Model.Name, doesnt remove duplicates. So it won't work in what I need.

    AM Export Tool - i can create the query distinct from amSoftInstall - but user only have access to AM Web. Thus this view must be visible on Web.

    Using this query below, I can get the distinct models from amModels. 

    lModelId IN (SELECT DISTINCT a.lModelId FROM amSoftInstall a WHERE a.lModelId <> 0)

    Is it possible to further expand this query to include where ComputerType (fr amComputer) = "windows server computer"? I need to further segregate its classfication.

    TIA!

Reply
  • Hi Mark,

    Thanks for your answers

    On amSoftInstall, when grouped by Model.Name, doesnt remove duplicates. So it won't work in what I need.

    AM Export Tool - i can create the query distinct from amSoftInstall - but user only have access to AM Web. Thus this view must be visible on Web.

    Using this query below, I can get the distinct models from amModels. 

    lModelId IN (SELECT DISTINCT a.lModelId FROM amSoftInstall a WHERE a.lModelId <> 0)

    Is it possible to further expand this query to include where ComputerType (fr amComputer) = "windows server computer"? I need to further segregate its classfication.

    TIA!

Children
  • Group by Model.Name, on amSoftInstall, does show distinct (when group branches are collapsed). When group branches are expanded, then you are correct as this is due to link type with amModel since one or more amSoftInstall records can have same model association.

    Anything is possible, to further expand, as long as you know the table relationships...

    lModelId IN (SELECT DISTINCT a.lModelId FROM amSoftInstall a, amPortfolio b, amComputer c WHERE a.lParentPortfolioId = b.lPortfolioItemId AND b.lPortfolioItemId = c.lItemId AND c.ComputerType = 'windows server computer')