How to get CI's WarrantyExpirationDate

English! place to talk about development, programming and coding
Post Reply
lglg
Znuny newbie
Posts: 3
Joined: 17 Jul 2017, 04:58
Znuny Version: 5.0.20

How to get CI's WarrantyExpirationDate

Post by lglg »

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
Znuny newbie
Posts: 3
Joined: 17 Jul 2017, 04:58
Znuny Version: 5.0.20

Re: How to get CI's WarrantyExpirationDate

Post by lglg »

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
Znuny guru
Posts: 2210
Joined: 13 Mar 2014, 09:16
Znuny Version: 6.0.14
Real Name: Rolf Straub

Re: How to get CI's WarrantyExpirationDate

Post by RStraub »

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 6.0.14 -- MariaDB -- Ubuntu 16 LTS
lglg
Znuny newbie
Posts: 3
Joined: 17 Jul 2017, 04:58
Znuny Version: 5.0.20

Re: How to get CI's WarrantyExpirationDate

Post by lglg »

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
Post Reply