How to get CI's WarrantyExpirationDate

English! place to talk about OTRS development, programming and coding

Moderator: tto

lglg
OTRS newbie
Posts: 3
Joined: 17 Jul 2017, 04:58
OTRS Version?: 5.0.20

How to get CI's WarrantyExpirationDate

Postby lglg » 23 Jul 2017, 15:38

Hi folks

I'm new in OTRS developing.
I need to find the WarrantyExpirationDate of CIs, and check if it's nearly approaching, to decide if it's need to alert the user.
But I stocked in getting WarrantyExpirationDate of CIs.
I think there are 2 ways to get it, one is by web service, Kernel::GenericInterface::Operation::ConfigItem::ConfigItemGet.

Code: Select all

my ( $Self, %Param ) = @_;
    my $User='soap_user';
    my $Pw='soap_pass';

    my $dbh = DBI->connect("DBI:mysql:otrs:16.187.42.143", 'root', 'ddc@dxc', { RaiseError => 1 });
    my $sth = $dbh->prepare("SELECT * FROM configitem");
    $sth->execute();

    while ( my @row = $sth->fetchrow_array() )
    {
   $Kernel::OM->Get('Kernel::System::Log')->Log(
         Priority => 'error',
       Message => join('\t', $row[0])."\n",
   );
    my $RPC = new SOAP::Lite(proxy => 'http://16.187.42.143/otrs/itsmrpc.pl',uri => 'http://16.187.42.143/Core');

    my $res = $RPC->Dispatch($User,$Pw,'ITSMConfigItem','ConfigItemGet',ConfigItemID => $row[0]);
    die $res->fault->{faultstring} if $res->fault;
   $Kernel::OM->Get('Kernel::System::Log')->Log(
            Priority => 'error',
          Message => $res."\n",
      );
      
   }

   $sth->finish();
   $dbh->disconnect();
   
    # Get the DBObject from the central object manager
    # my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
    $Kernel::OM->Get('Kernel::System::Log')->Log(
         Priority => 'error',
       Message => 'retrieve completed',
   );


but the result I got is like a soap response, can't find any warranty values.

I also searched it from DB by DBI, but only find it in configitem_history.
If it's not changed, it will not be stored in this table.
So, can anyone give me some advice on how to get it?
Any suggestions are highly appreciated.

Thanks,
lglg

lglg
OTRS newbie
Posts: 3
Joined: 17 Jul 2017, 04:58
OTRS Version?: 5.0.20

Re: How to get CI's WarrantyExpirationDate

Postby lglg » 24 Jul 2017, 06:22

I found the WarrantyExpirationDate is stored in table xml_storage, but how to connect to this table?
The data in this table is like the following:
mysql> select * from xml_storage where xml_content_key like '%WarrantyExpirationDate%';
+------------------------------+---------+-----------------------------------------------------------+------------------------------------------------+
| xml_type | xml_key | xml_content_key | xml_content_value |
+------------------------------+---------+-----------------------------------------------------------+------------------------------------------------+
| ITSM::ConfigItem::Archiv::22 | 1 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-07-04 |
| ITSM::ConfigItem::Archiv::22 | 1 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::Archiv::22 | 2 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-07-04 |
| ITSM::ConfigItem::Archiv::22 | 2 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::Archiv::22 | 3 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-07-04 |
| ITSM::ConfigItem::Archiv::22 | 3 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::Archiv::22 | 6 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-07-05 |
| ITSM::ConfigItem::Archiv::22 | 6 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::Archiv::22 | 7 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-07-04 |
| ITSM::ConfigItem::Archiv::22 | 7 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::Archiv::22 | 9 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-07-05 |
| ITSM::ConfigItem::Archiv::22 | 9 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::22 | 10 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-07-04 |
| ITSM::ConfigItem::22 | 10 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::22 | 11 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-07-04 |
| ITSM::ConfigItem::22 | 11 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::22 | 13 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-07-05 |
| ITSM::ConfigItem::22 | 13 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::22 | 14 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-07-11 |
| ITSM::ConfigItem::22 | 14 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::22 | 15 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-07-12 |
| ITSM::ConfigItem::22 | 15 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::Archiv::22 | 16 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-09-23 |
| ITSM::ConfigItem::Archiv::22 | 16 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
| ITSM::ConfigItem::22 | 17 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'Content'} | 2017-09-20 |
| ITSM::ConfigItem::22 | 17 | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1]{'TagKey'} | [1]{'Version'}[1]{'WarrantyExpirationDate'}[1] |
+------------------------------+---------+-----------------------------------------------------------+------------------------------------------------+
26 rows in set (0.01 sec)


In information_schema, I found the field xml_type and etc are only in table xml_storage.
how does it connect to the CIs?
Can somebody give me some advice?

Thanks,
lglg

RStraub
OTRS guru
Posts: 2049
Joined: 13 Mar 2014, 09:16
OTRS Version?: 5.0.14
Real Name: Rolf Straub

Re: How to get CI's WarrantyExpirationDate

Postby RStraub » 24 Jul 2017, 10:20

Hey there,

did you try these API calls?

Code: Select all

   my $VersionRef = $ConfigItemObject->VersionGet(
       VersionID  => 123,
       XMLDataGet => 1,    # (optional) default 1 (0|1)
   );


Taken from here:
https://otrs.perl-services.de/docs/ITSM ... rsion.html

Good luck navigating in the XML storage. I hated that with a vengance. Can't wrap my head around those arrays where [0] is undef and the actual data is in [1].
Currently using: OTRS 5.0.14 -- MariaDB -- Ubuntu 16 LTS

lglg
OTRS newbie
Posts: 3
Joined: 17 Jul 2017, 04:58
OTRS Version?: 5.0.20

Re: How to get CI's WarrantyExpirationDate

Postby lglg » 25 Jul 2017, 05:54

Hi RStraub

Thanks for the suggestion.
I found the warranty expiration date is stored in xml_storage table.
So I used below sql to get the result.

Code: Select all

    my $sql = "select ci.configitem_number, xs.xml_content_value from xml_storage xs, configitem ci ";
    $sql = $sql."where xs.xml_content_key like '%WarrantyExpirationDate%' and xs.xml_content_key like '%Content%' ";
    $sql = $sql."and xs.xml_key = ci.last_version_id and substring_index(xs.xml_type, '::', -1) = ci.class_id ";
    $sql = $sql."and to_days(xs.xml_content_value) - to_days(now()) <90";
    my $sth = $dbh->prepare($sql);
    $sth->execute();


I will try you suggestion.
Thanks,
lglg


Return to “OTRS Developers”

Who is online

Users browsing this forum: No registered users and 2 guests