Insight: How can I get an inbound attribute query result to show only one object?

[Trina Washington]

Hello, 

     We're trying to create a list of Users that have out of warranty laptops so we can create a Jira ticket for the user to order a new machine.

We have assets assigned to Users and not Users assigned to assets. Some Users have multiple laptops therefore we need an accurate count of how many users have laptops assigned to them. We can go into the Users object group and do an inbound reference query

objectType = "Users" and object HAVING inR(Equipment="laptop") 

This works perfectly.

Now we need to drlll down to laptops that are not under warranty. We want machines that the warranty has expired over a year ago.

objectType = "Users" and object HAVING inR(Equipment = "laptop" and "Warranty Expires"  < now(-365d)

This query does work but still shows Users that have another laptop that is under warranty.  

How can I get the query to only show me names of Users with out of warranty equipment? If they have another laptop under warranty, we do not want them on the list.

[Jeroen Poismans · Accepted solution]

Hi there!

If I get your Insight model correctly I think following should do the trick. Let's say we identify equipment still under warranty as 

"Warranty Expires" > now()

Then we coudl extend your IQL as follows:

objectType = "Users" and object HAVING inR(Equipment = "laptop" and "Warranty Expires"  < now(-365d)
and object NOT HAVING inR(Equipment = "laptop" and "Warranty Expires" > now())

This way we ask for the users that have a link with a warranty expired laptop and NOT having a link with a laptop still under waranty

Hope this helps!

Jeroen

[Trina Washington]

Thanks Jeroen, I played around with it and found that exact same query!

[Randy Michel]

I have a similar ask but have one question. The Equipment is that the name of the schema or the Reference name? 

[Jeroen Poismans]

Hi!

In this example "Equipment" would be the attribute on the inbound referenced object.

You are literally looking for "User that have an asset linked of tEquipment laptop and where the warranty has expired over a year ago"

Jeroen