See the computers that were most associated?

[amdkryn]

How can I do to see the computers / Ci that were most associated with the tickets? The goal is to know which computers had more problems. It can be a SQL query. I can not create a report with this information.

I do not know how to search the database with SQL commands.

My System:
OTRS 3.1.11 / KIX4OTRS / Opensuse 12

Sorry for my english horrible

[amdkryn]

Anybody?

[tto]

How can I do to see the computers / Ci that were most associated with the tickets? The goal is to know which computers had more problems. It can be a SQL query. I can not create a report with this information.

I do not know how to search the database with SQL commands.

The following SQL is VERY BASIC. It just counts the number of links between CI with ID 9999 and tickets. It does not differ between link types and counts any tickets independent from ticket type or state. You may use this as a starter but it's not your final statement. Try to find someone in your team who's speaking SQL:

Code: Select all

SELECT count(*)
FROM link_relation lr, link_object lo1, link_object lo2
WHERE lr.source_object_id = lo1.id
AND lo1.name = 'ITSMConfigItem'
AND lr.target_object_id = lo2.id
AND lo2.name = 'Ticket'
AND lr.source_key = '9999'

regards, T.

[amdkryn]

Thanks for the help.

The query is generating an error:
Unknown column 'lo1.source_key' in 'where clause', SQL: "SELECT count (*) FROM link_relation lr, link_object LO1, link_object lo2 = lo1.id AND WHERE lr.source_object_id lo1.name = 'ITSMConfigItem'AND lr.target_object_id lo2.id lo2.name = AND = 'Ticket'AND lo1.source_key = '1032001563' LIMIT 40 '

In place of 999 put the computer ID 1032001563. Where did I go wrong?

[tto]

The query is generating an error:
Unknown column 'lo1.source_key' in 'where clause', SQL: "SELECT count (*) FROM link_relation lr, link_object LO1, link_object lo2 = lo1.id AND WHERE lr.source_object_id lo1.name = 'ITSMConfigItem'AND lr.target_object_id lo2.id lo2.name = AND = 'Ticket'AND lo1.source_key = '1032001563' LIMIT 40 '

my mistake - should work by now. I have no chance to check it. One more thing: the statement counts only the links where the CI is the source of the link. Directional links TO this CI are not counted.

regards, T.

[amdkryn]

No longer are occurring error but does not generate any results.
I just changed the value of lr.source_key = '10156000254 '
This IC has a few tickets assigned. Am I doing something wrong?

[tto]

No longer are occurring error but does not generate any results.
I just changed the value of lr.source_key = '10156000254 '
This IC has a few tickets assigned. Am I doing something wrong?

...you're not using the ID, but the number. You find the ID in the URL when editing a CI (for instance).

regards, T.

[amdkryn]

Now I understand, thanks