8 Replies Latest reply on Jul 14, 2017 4:56 AM by phoffmann

    Can I create a query or report that lists the unique hardware devices on each system?

    bdwest Rookie

      We're new to LanDesk/Ivanti products.  We have 2016.3 installed.  I would like to create a query or report that shows me all the unique devices on each system.  So no processors, hard drives, keyboards, etc.  I'd like to customize the list of devices or device class GUIDs to filter out.  Is this possible?

       

      Message was edited by: Brian West

        • 1. Re: Can I create a query or report that lists the unique OS-devices on each system?
          carlos Expert

          You can do something  like this:

          There is also a report for it under reports monitoring --> Inventory --> Operating System

          (This is in 9.6, I wouldn't think is too different from 2016)

          • 2. Re: Can I create a query or report that lists the unique OS-devices on each system?
            bdwest Rookie

            Part of the difficulty in answering this question is the multiple meanings of "device".  What I'd like is a report on each managed device (computer, all Windows systems in our situation) showing all of the unique or unusual hardware devices attached to that managed device/computer/system.  So if I have a Windows workstation with a keyboard, monitor, hard drive, and camera attached to it, I'd like to have a query or report that shows me the hardware devices that I care about.  For the purposes of this report I don't need to know about its keyboard, monitor, and hard drive, but I do want to know if there's a camera attached.

             

            Does that clarify things?

            • 3. Re: Can I create a query or report that lists the unique OS-devices on each system?
              carlos Expert

              Inventory can report back almost everything, the problem is that it needs "something" to find, as you know manufactures do not necessarily identify all the components the same, as you can see the inventory reports back a main component, I do not have any with a camera on it so i can say how it looks like, look on the inventory and see if you can find it, then add that device to the query on the right. If you can not find it, you can also do a registry search. I hope this helps.

               

              1 of 1 people found this helpful
              • 4. Re: Can I create a query or report that lists the unique hardware devices on each system?
                phoffmann SupportEmployee

                So the way to do that is to edit the data columns in the bottom half of the query.

                 

                The stuff that @carlos picture showed as :

                • Device Name
                • Type
                • OS Name

                 

                THAT's the section where you add "what data columns you want added / displayed".

                 

                If there's a value in there - we'll show it (for arguments sake - keyboard == "MyCherryKeyboard is the best but has only 5 keys"). If there's NO value in it ... we'll just leave it empty (NULL value).

                 

                So you "just" need to add whatever components / inventory bits you want and that's that. The query itself (what devices you want to report on) can be either "Device ID EXISTS" (for "show me everything you have") or you can filter by name or whatever.

                 

                Now ... DO TAKE CARE ...

                 

                ... some attributes are *NOT* as unique as one might think. Take "HD Serial Number" for instance ... we'll give you an entry for *EVERY* HD serial number that device has. So you can get to situations like

                 

                Device_1 ==> HD_SN_1

                Device_1 ==> HD_SN_2

                Device_1 ==> HD_SN_3

                Device_2 ==> HD_SN_1

                Device_2 ==> HD_SN_2

                Device_2 ==> HD_SN_3

                 

                ... because devices 1 & 2 have 3 HDD's in them, we end up picking up / displaying 3 fields for each ... so that CAN skew your counts potentially. This "be careful about what you *THINK* is unique" is a key aspect that catches a lot of people off guard.

                 

                "Be careful" to make sure that your intended attributes *ARE* unique ...

                 

                (Not meaning to scare you - just that it's something that a LOT of people trip over).

                 

                ===========

                 

                As an "easy to edit" alternative -- start with a query of "DEVICE NAME = AAAA or DEVICE NAME == BBBB" (so 2-3 devices). Then create a new COLUMN SET (separate tool) and create a column set with your intended attributes in there.

                 

                You can DRAG the column set (as you save it) over the query & we'll - temporarily - apply that column set). It's VERY nice way to "play with data" in a controlled fashion & not have your Core server barf all over you because suddenly it's having to try & display 5 billion data lines .

                • 5. Re: Can I create a query or report that lists the unique hardware devices on each system?
                  bdwest Rookie

                  Thanks for the info.  I guess my problem is I want a list of devices that we don't already know about.  Some device classes I want to filter out completely, like keyboards and mice.  But what I'm trying to discover is something I don't know.  I created a query showing the four values under OS-Devices (Class GUID, Device Identifier, Hardware ID, and Name), so now my query results are enormous.  So if a computer has 103 devices, it has that many lines in the results (as I'm sure you already know).  I know how to add a Qualifier, but I don't see how to make the Qualifier a Not, that is, to say, display this if it's Not this or that.  Maybe that's not possible with this part of the system?

                  • 6. Re: Can I create a query or report that lists the unique hardware devices on each system?
                    phoffmann SupportEmployee

                    The qualifier is purely a "MUST BE" -- there isn't a "NOT X" type qualifier.

                     

                    Remember - the query tool is a case of "make SQL (which is complex) easy enough that someone without any understanding can use it".

                     

                    The EASIEST way to get you your data is likely going to be a SQL based report. There may be some way to help you out with "a regular query" but I'm not functioning at 100% yet and not sure if what I'm thinking about would work.

                     

                    Do you know SQL yourself / have access to a DBA? (TIP - it *REALLY* helps if you do. Pester your boss to get you some basic SQL training signed off -- I keep saying that "sooner or later - usually sooner - a real admin will need to dive into the DB for something." And that keeps being proven true )

                     

                    You don't need to be a SQL wizard here, the basic things you need to be able to do / understand:

                    • Basic SELECT
                    • Table joins (i.e. - LEFT OUTER JOIN ...)
                    • How to pay attention with your WHERE clauses and grouping thereof (i.e. - don't get your logic to trip you up).

                     

                    ... that's by and large it. You're likely to end up with a LONG SQL statement (on account of "VALUE X NOT IN (A, B, C, D .... X, Y, Z)) -- but not one that's really COMPLICATED.

                    1 of 1 people found this helpful
                    • 7. Re: Can I create a query or report that lists the unique hardware devices on each system?
                      bdwest Rookie

                      I was afraid of that. I mainly wanted to make sure there wasn't some other approach to this that existed in LDMS/IEM which I wasn't aware of.

                      • 8. Re: Can I create a query or report that lists the unique hardware devices on each system?
                        phoffmann SupportEmployee

                        There may be with some complicated query-engine shenanigans (that I don't have time atm to test to prove me wrong / right).

                         

                        The "fastest" way to get this done would be SQL to begin with (not as scary as it sounds).

                         

                        If you give me an example of what you're actually looking for, I can give you a starter for 10 (with explanations) that'll help you get going?