-
Notifications
You must be signed in to change notification settings - Fork 5
/
mapping.xml.sample
1520 lines (1436 loc) · 125 KB
/
mapping.xml.sample
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<meta>
<settings>
<freshservice
url="{url}.freshservice.com"
api_key="{api_key}"
default_approver_email=""/>
<device42
url="https://{url}"
user="{user}"
pass="{pass}"/>
</settings>
<tasks>
<task enable="true" name="Products" type="product" description="Copy Product info from Device42 to FreshService" d42_min_version="16.19.00">
<api>
<target model="products" target="freshservice" method="POST" update_method="PUT" path="api/v2/products" asset-type="Hardware"/>
<resource target="device42" method="POST"
path="services/data/v1.0/query/"
doql="
select distinct
view_vendor_v1.name as manufacturer,
case WHEN view_device_v2.type = 'Virtual' THEN view_device_v2.type WHEN view_device_v2.type = 'Cluster' THEN view_device_v2.type ELSE coalesce(view_hardware_v2.name, view_device_v2.type) END as name
from view_device_v2 left join view_hardware_v2 on view_device_v2.hardware_fk = view_hardware_v2.hardware_pk
left join view_vendor_v1 on vendor_pk=vendor_fk
"
/>
</api>
<mapping callback="from_d42" key="name">
<field resource="name" source-type="string" target="name" target-type="string" max-length="255" escape="true"/>
<field resource="manufacturer" source-type="string" target="manufacturer" target-type="string"
not-null="true" set-space="true" min-length="1" max-length="255"/>
</mapping>
</task>
<task enable="false" name="Products" type="product" description="Copy Product info from Device42 to FreshService" d42_max_version="16.18.02">
<api>
<target model="products" target="freshservice" method="POST" update_method="PUT" path="api/v2/products" asset-type="Hardware"/>
<resource target="device42" method="POST"
path="services/data/v1.0/query/"
doql="
select distinct
view_vendor_v1.name as manufacturer,
case WHEN view_device_v1.type = 'Virtual' THEN view_device_v1.type WHEN view_device_v1.type = 'Cluster' THEN view_device_v1.type ELSE coalesce(view_hardware_v1.name, view_device_v1.type) END as name
from view_device_v1 left join view_hardware_v1 on view_device_v1.hardware_fk = view_hardware_v1.hardware_pk
left join view_vendor_v1 on vendor_pk=vendor_fk
"
/>
</api>
<mapping callback="from_d42" key="name">
<field resource="name" source-type="string" target="name" target-type="string" max-length="255" escape="true"/>
<field resource="manufacturer" source-type="string" target="manufacturer" target-type="string"
not-null="true" set-space="true" min-length="1" max-length="255"/>
</mapping>
</task>
<task enable="true" name="Devices" description="Copy Servers from Device42 to FreshService using DOQL v2" d42_min_version="16.19.00">
<api>
<target model="assets" target="freshservice" method="POST" update_method="PUT" path="api/v2/assets" />
<resource model="Devices" target="device42" method="POST"
doql="
WITH
devicelastlogin AS (
with sub as ( SELECT dll.device_fk, dll.username, ROW_NUMBER() OVER(PARTITION BY dll.device_fk ORDER BY dll.last_login DESC) AS rank FROM view_devicelastlogin_v1 dll )
SELECT * FROM sub WHERE rank = 1 )
,alias as ( SELECT device_fk, string_agg(alias_name, ', ' order by alias_name) as hostname FROM view_devicealias_v1 GROUP BY device_fk)
,assetlifecycle AS (
with sub as (
SELECT alc.device_fk, alc.date,
ROW_NUMBER() OVER(PARTITION BY alc.device_fk ORDER BY alc.date DESC) AS rank
FROM view_assetlifecycle_v1 alc
INNER JOIN view_assetaction_v1 aaction ON alc.assetaction_fk = aaction.assetaction_pk WHERE aaction.name = 'Purchased')
SELECT * FROM sub WHERE rank = 1 )
,asset_endlifecycle AS (
with sub as (
SELECT alc.device_fk, to_char(alc.date, 'YYYY-MM-DD') as date,
ROW_NUMBER() OVER(PARTITION BY alc.device_fk ORDER BY alc.date) AS rank
FROM view_assetlifecycle_v1 alc
INNER JOIN view_assetaction_v1 aaction ON alc.assetaction_fk = aaction.assetaction_pk WHERE aaction.name = 'Retired')
SELECT * FROM sub WHERE rank = 1 )
,warranty AS (
with sub as (
SELECT plid.device_fk, pli.start_date, pli.end_date,
extract(year from age(pli.end_date, CASE WHEN lower(ven.name)='meraki' and pli.start_date='1970-01-01' THEN null else pli.start_date end)) warranty_years,
extract(month from age(pli.end_date, CASE WHEN lower(ven.name)='meraki' and pli.start_date='1970-01-01' THEN null else pli.start_date end)) + case when extract(day from age(pli.end_date, CASE WHEN lower(ven.name)='meraki' and pli.start_date='1970-01-01' THEN null else pli.start_date end)) > 0 then 1 else 0 end warranty_months,
ven.name as purchase_order_vendor,
ROW_NUMBER() OVER(PARTITION BY plid.device_fk ORDER BY pli.end_date DESC) AS rank
FROM view_purchaselineitems_to_devices_v1 plid
INNER JOIN view_purchaselineitem_v1 pli ON plid.purchaselineitem_fk = pli.purchaselineitem_pk
LEFT JOIN view_purchase_v1 pur ON pli.purchase_fk = pur.purchase_pk
LEFT JOIN view_vendor_v1 ven ON pur.vendor_fk=ven.vendor_pk
WHERE lower(pli.line_type) = 'contract'
and ( lower(pli.contract_type_name) = 'warranty'
or ( lower(pli.contract_type_name) in ('base', 'upgrade') and lower(ven.name) in ('ibm', 'lenovo'))
)
and pli.end_date is not null
)
SELECT * FROM sub WHERE rank = 1 )
,part_count as (
SELECT p.device_fk,
SUM(p.pcount *
CASE
WHEN pm.hdsize_unit = 'GB' THEN pm.hdsize
WHEN pm.hdsize_unit = 'TB' THEN pm.hdsize * 1024
WHEN pm.hdsize_unit = 'PB' THEN pm.hdsize * 1024 * 1024
WHEN pm.hdsize_unit = 'EB' THEN pm.hdsize * 1024 * 1024 * 1024
WHEN pm.hdsize_unit = 'ZB' THEN pm.hdsize * 1024 * 1024 * 1024 * 1024
WHEN pm.hdsize_unit = 'YB' THEN pm.hdsize * 1024 * 1024 * 1024 * 1024 * 1024
ELSE NULL
END) AS total_part_disk_size
FROM view_part_v1 p
INNER JOIN view_partmodel_v1 pm ON p.partmodel_fk = pm.partmodel_pk
WHERE pm.type_id = 3 AND pm.hdsize IS NOT NULL AND p.pcount > 0 AND p.device_fk IS NOT NULL
GROUP BY p.device_fk )
,network_port_count as (
SELECT p.device_fk,
count(*) AS total_port_count
from (
select device_fk, hwaddress from view_netport_v1 where device_fk is not null
union
select second_device_fk as device_fk, hwaddress from view_netport_v1 where second_device_fk is not null
) p
WHERE p.device_fk is not null
GROUP BY p.device_fk )
,total_costs as (
select device_fk, sum(cost) as cost from view_purchaselineitem_v1 left join view_purchaselineitems_to_devices_v1 on purchaselineitem_fk = purchaselineitem_pk group by device_fk)
,network_hw as (
select device_fk,
string_agg(
CASE
WHEN LENGTH(TRIM(netport.hwaddress)) = 12
THEN CONCAT_WS(':',left(TRIM(netport.hwaddress),2), substring(TRIM(netport.hwaddress),3,2), substring(TRIM(netport.hwaddress),5,2), substring(TRIM(netport.hwaddress),7,2), substring(TRIM(netport.hwaddress),9,2), right(TRIM(netport.hwaddress),2))
ELSE
netport.hwaddress
END, ', '
order by netport.hwaddress) as hwaddress
from (
select device_fk, hwaddress from view_netport_v1 where device_fk is not null
union
select second_device_fk as device_fk, hwaddress from view_netport_v1 where second_device_fk is not null
) netport
where (netport.hwaddress is not null AND LENGTH(TRIM(netport.hwaddress)) > 0)
group by netport.device_fk )
,network_ip as (
select
vip.device_fk,
string_agg(host(vip.ip_address)::character varying, ', ' order by vip.ip_address) as ip_address,
string_agg(case when vip.ip_address << inet '10/8' or vip.ip_address << inet '192/8' or vip.ip_address << inet '172.16/12' then
host(vip.ip_address)::character varying
else null end, ', ' order by vip.ip_address) as private_ip_address,
string_agg(case when vip.ip_address << inet '10/8' or vip.ip_address << inet '192/8' or vip.ip_address << inet '172.16/12' then null
else host(vip.ip_address)::character varying end, ', ' order by vip.ip_address) as public_ip_address,
string_agg(host(vip.subnet_mask)::character varying, ', ' order by vip.ip_address) as subnet_mask
from (select distinct device_fk, ip_address, netmask(set_masklen(view_subnet_v1.network, view_subnet_v1.mask_bits)) as subnet_mask
from view_ipaddress_v1 left join view_subnet_v1 on view_ipaddress_v1.subnet_fk = view_subnet_v1.subnet_pk) vip group by vip.device_fk)
,subnet as (
select
vn.device_fk,
string_agg(host(vn.network)::character varying, ', ' order by vn.network) as network
from (select distinct device_fk, network from view_ipaddress_v1 left join view_subnet_v1 on subnet_pk=subnet_fk) vn group by vn.device_fk)
,vrfgroup as (
select
vn.device_fk,
string_agg(vn.vrfgroup, ', ' order by vn.vrfgroup) as vrfgroup
from (select distinct device_fk, view_vrfgroup_v1.name as vrfgroup from view_ipaddress_v1
left join view_subnet_v1 on subnet_pk=subnet_fk
left join view_vrfgroup_v1 on vrfgroup_pk=vrfgroup_fk) vn group by vn.device_fk)
,device_cluster AS (
SELECT device_cluster.child_device_fk device_fk,
string_agg(device_cluster.parent_device_name, ', ' order by device_cluster.parent_device_name) as device_cluster_name
FROM view_devices_in_cluster_v1 device_cluster group by device_cluster.child_device_fk)
,mount_point as (
select dr.device_fk, string_agg(resource.resource_name, ', ' order by resource.resource_name) as mountpoint
from view_deviceresource_v1 dr left join view_resource_v2 resource on resource.resource_pk=dr.resource_fk where dr.relation = 'Mountpoint' group by dr.device_fk)
select view_device_v2.*,
case
WHEN view_device_v2.virtual_host AND lower(view_device_v2.os_name) like '%esxi%' THEN 'VMware VCenter Host'
WHEN view_device_v2.virtual_host THEN 'Host'
WHEN lower(view_device_v2.type) = 'physical' AND lower(view_device_v2.physicalsubtype) = 'laptop' THEN 'Laptop'
WHEN lower(view_device_v2.type) = 'physical' AND lower(view_device_v2.physicalsubtype) = 'workstation' THEN 'Desktop'
WHEN lower(view_device_v2.type) = 'physical' AND lower(view_device_v2.physicalsubtype) = 'network printer' THEN 'Printer'
WHEN lower(view_device_v2.type) = 'physical' AND lower(view_device_v2.physicalsubtype) = 'router' THEN 'Router'
WHEN lower(view_device_v2.type) = 'physical' AND lower(view_device_v2.physicalsubtype) = 'firewall' THEN 'Firewall'
WHEN lower(view_device_v2.os_name) similar to '%(f5|netscaler)%' THEN 'Load Balancer'
WHEN view_device_v2.network_device THEN 'Switch'
WHEN lower(view_device_v2.type) = 'physical' AND lower(view_device_v2.os_name) similar to '%(windows|microsoft)%' AND lower(view_device_v2.os_name) like '%server%' THEN 'Windows Server'
WHEN lower(view_device_v2.type) = 'physical' AND lower(view_device_v2.os_name) similar to '%(unix|z/os|z os|zos|hp-ux|os400|os/400|os 400|linux|amazon|ubuntu|centos|redhat|debian|sles|suse|gentoo|oracle|freebsd|rhel|red hat|fedora|alma|rocky|arch)%' THEN 'Unix Server'
WHEN lower(view_device_v2.type) = 'physical' AND lower(view_device_v2.os_name) similar to '%(aix)%' THEN 'AIX Server'
WHEN lower(view_device_v2.type) = 'physical' AND lower(view_device_v2.os_name) similar to '%(solaris|sunos|sun os)%' THEN 'Solaris Server'
WHEN lower(view_device_v2.type) = 'virtual' AND lower(view_device_v2.virtualsubtype) = 'vmware' THEN 'VMware VCenter VM'
WHEN lower(view_device_v2.type) = 'virtual' AND lower(view_device_v2.virtualsubtype) = 'amazon ec2 instance' THEN 'AWS VM'
WHEN lower(view_device_v2.type) = 'virtual' AND lower(view_device_v2.virtualsubtype) = 'azure virtual machine' THEN 'Azure VM'
WHEN lower(view_device_v2.type) = 'physical' THEN 'Server'
WHEN lower(view_device_v2.type) = 'virtual' THEN 'Virtual Machine'
ELSE 'Computer'
END as asset_type,
coalesce(view_device_v2.physicalsubtype, view_device_v2.virtualsubtype) as device_subtype,
view_device_v2.core_per_cpu * view_device_v2.total_cpus as total_cpucore,
t_cost.cost,
case WHEN view_device_v2.type = 'Virtual' THEN view_device_v2.type WHEN view_device_v2.type = 'Cluster' THEN view_device_v2.type ELSE coalesce(view_hardware_v2.name, view_device_v2.type) END as hw_model,
case WHEN view_device_v2.hz = 'MHz' and view_device_v2.cpu_speed is not null THEN view_device_v2.cpu_speed / 1000 ELSE view_device_v2.cpu_speed END as cpuspeed,
case
WHEN view_device_v2.ram_size_type = 'MB' and view_device_v2.ram is not null THEN view_device_v2.ram / 1024
WHEN view_device_v2.ram_size_type = 'TB' and view_device_v2.ram is not null THEN view_device_v2.ram * 1024
ELSE view_device_v2.ram END as ramsize,
network_ip.ip_address,
network_ip.public_ip_address,
network_ip.private_ip_address,
network_ip.subnet_mask,
network_hw.hwaddress,
CEIL(COALESCE(p.total_part_disk_size,
CASE
WHEN view_device_v2.hard_disk_count IS NOT NULL AND
view_device_v2.hard_disk_size IS NOT NULL AND
view_device_v2.hard_disk_size_type IS NOT NULL THEN
view_device_v2.hard_disk_count *
CASE
WHEN view_device_v2.hard_disk_size_type = 'GB' THEN view_device_v2.hard_disk_size
WHEN view_device_v2.hard_disk_size_type = 'TB' THEN view_device_v2.hard_disk_size * 1024
WHEN view_device_v2.hard_disk_size_type = 'PB' THEN view_device_v2.hard_disk_size * 1024 * 1024
ELSE NULL
END
ELSE NULL
END)) AS disk_size,
warranty.warranty_months + (warranty.warranty_years * 12) as warranty_months,
warranty.end_date as warranty_end_date,
warranty.purchase_order_vendor as purchase_order_vendor,
assetlifecycle.date as acquisition_date,
asset_endlifecycle.date as end_of_life,
case when lower(view_device_v2.os_name) like '%windows%' or lower(view_device_v2.os_name) like '%microsoft%' then view_device_v2.os_version_no else view_device_v2.os_version end as device_os_version,
case when lower(view_device_v2.os_name) like '%windows%' or lower(view_device_v2.os_name) like '%microsoft%' then view_device_v2.os_version else view_device_v2.os_version_no end as device_os_version_no,
alias.hostname as hostname,
devicelastlogin.username as last_login_by,
ci.location as cloud_instance_region,
ci.location as cloud_instance_location,
ci.instance_type as cloud_instance_type,
ci.account as cloud_account,
ci.image_id as cloud_image_id,
ci.os_platform as cloud_os_platform,
subnet.network as subnet,
vrfgroup.vrfgroup as vrfgroup,
case
WHEN (lower(view_device_v2.type) = 'virtual' AND lower(view_device_v2.virtualsubtype) = 'amazon ec2 instance')
or lower(civ.name) similar to '%(aws|amazon)%' THEN 'AWS'
WHEN (lower(view_device_v2.type) = 'virtual' AND lower(view_device_v2.virtualsubtype) = 'azure virtual machine')
or lower(civ.name) similar to '%(azure|microsoft)%' THEN 'AZURE'
WHEN (lower(view_device_v2.type) = 'virtual' AND lower(view_device_v2.virtualsubtype) = 'vmware')
or lower(civ.name) like '%vmware%'
or (view_device_v2.virtual_host and lower(view_device_v2.os_name) like '%esxi%') then 'VMWARE VCENTER'
WHEN (lower(view_device_v2.type) = 'virtual' AND lower(view_device_v2.virtualsubtype) = 'gce')
or lower(civ.name) similar to '%(google|gce|gcp)%' then 'GCP'
WHEN (lower(view_device_v2.type) = 'virtual' AND lower(view_device_v2.virtualsubtype) = 'oracle cloud instance')
or lower(civ.name) like '%oracle%' then 'Oracle Cloud'
WHEN (lower(view_device_v2.type) = 'virtual' AND (lower(view_device_v2.virtualsubtype) = 'citrix/xen'
or lower(view_device_v2.virtualsubtype) = 'xen'))
or lower(civ.name) similar to '%(citrix|xen)%'
or (view_device_v2.virtual_host and lower(view_device_v2.os_name) like '%xenserver%') then 'Citrix'
WHEN (lower(view_device_v2.type) = 'virtual' AND lower(view_device_v2.virtualsubtype) = 'hyper-v')
or (view_device_v2.virtual_host and lower(view_device_v2.os_name) similar to '%(windows|microsoft)%' AND lower(view_device_v2.os_name) like '%server%') then 'Hyper-V'
WHEN (lower(view_device_v2.type) = 'virtual' AND lower(view_device_v2.virtualsubtype) = 'digitalocean instance')
or lower(civ.name) similar to '%(digitalocean|digital ocean)%' then 'Digital Ocean'
WHEN (lower(view_device_v2.type) = 'virtual' AND lower(view_device_v2.virtualsubtype) = 'alibaba cloud instance')
or lower(civ.name) like '%alibaba%' then 'Alibaba Cloud'
else null
end as cloud_vendor,
ci.instance_id as cloud_instance_id,
ci.instance_name as cloud_instance_name,
ci.status as cloud_instance_status,
device_cluster.device_cluster_name as device_cluster_name,
mount_point.mountpoint as mountpoint,
CONCAT_WS(' ', view_device_v2.os_name, view_device_v2.os_version, view_device_v2.os_version_no) as hypervisor,
hv.name as hardware_vendor,
virtual_host_device.datacenter as host_datacenter,
host_device_cluster.device_cluster_name as host_cluster_name,
case when view_device_v2.name ~* '^((?!-)[A-Za-z0-9-]{{1,63}}(?<!-)\.)+[A-Za-z]{{2,6}}$' then substring(view_device_v2.name, position('.' in view_device_v2.name)+1)
else null end domain_name,
network_port_count.total_port_count as total_port_count
FROM view_device_v2
LEFT JOIN view_hardware_v2 on view_device_v2.hardware_fk = view_hardware_v2.hardware_pk
LEFT JOIN network_ip on view_device_v2.device_pk = network_ip.device_fk
LEFT JOIN network_hw on view_device_v2.device_pk = network_hw.device_fk
LEFT JOIN total_costs t_cost on t_cost.device_fk = device_pk
LEFT OUTER JOIN part_count AS p ON view_device_v2.device_pk = p.device_fk
LEFT OUTER JOIN warranty ON view_device_v2.device_pk = warranty.device_fk
LEFT OUTER JOIN assetlifecycle ON view_device_v2.device_pk = assetlifecycle.device_fk
LEFT OUTER JOIN asset_endlifecycle ON view_device_v2.device_pk = asset_endlifecycle.device_fk
LEFT OUTER JOIN alias ON view_device_v2.device_pk = alias.device_fk
LEFT OUTER JOIN devicelastlogin ON view_device_v2.device_pk = devicelastlogin.device_fk
LEFT JOIN view_cloudinstance_v1 as ci ON view_device_v2.device_pk = ci.device_fk
LEFT JOIN view_vendor_v1 as civ ON ci.vendor_fk = civ.vendor_pk
LEFT JOIN device_cluster ON view_device_v2.device_pk = device_cluster.device_fk
LEFT JOIN mount_point ON view_device_v2.device_pk = mount_point.device_fk
LEFT JOIN view_vendor_v1 as hv ON view_hardware_v2.vendor_fk = hv.vendor_pk
LEFT JOIN view_device_v2 as virtual_host_device ON view_device_v2.virtual_host_device_fk = virtual_host_device.device_pk
LEFT JOIN device_cluster as host_device_cluster ON virtual_host_device.device_pk = host_device_cluster.device_fk
LEFT JOIN subnet ON view_device_v2.device_pk = subnet.device_fk
LEFT JOIN vrfgroup ON view_device_v2.device_pk = vrfgroup.device_fk
LEFT JOIN network_port_count ON view_device_v2.device_pk = network_port_count.device_fk
"
/>
</api>
<mapping callback="from_d42" key="name" source="Devices">
<!-- General -->
<field resource="asset_no" source-type="string" target="asset_tag" target-type="string"
target-header="General" not-null="true" min-length="1" max-length="255"/>
<field resource="name" source-type="string" target="name" target-type="string" target-header="General"
set-space="true" min-length="1" max-length="248" escape="true"/>
<field resource="notes" source-type="string" target="description" target-header="General"
not-null="true" set-space="true" min-length="1" max-length="60000"/>
<field resource="end_of_life" source-type="string" target="end_of_life" target-type="date"
target-header="General"/>
<!-- ~General -->
<!-- Hardware -->
<field resource="in_service" source-type="boolean" target="asset_state" target-type="string"
target-header="Hardware">
<value-mapping default="Retired">
<item key="true" value="In Use"/>
</value-mapping>
</field>
<field resource="hw_model" source-type="string" target="product" target-type="string"
target-header="Hardware"
target-foregin="products" target-foregin-key="name" not-null="true" required="True" max-length="255"/>
<field resource="serial_no" source-type="string" target="serial_number" target-type="string"
target-header="Hardware" not-null="true" set-space="true"
min-length="1" max-length="255" error-skip="true"/>
<field resource="warranty_months" source-type="integer" target="warranty" target-type="integer"
target-header="Hardware" not-null="true"/>
<field resource="warranty_end_date" source-type="string" target="warranty_expiry_date" target-type="date"
target-header="Hardware" not-null="true"/>
<field resource="acquisition_date" source-type="string" target="acquisition_date" target-type="date"
target-header="Hardware" not-null="true"/>
<field resource="domain_name" source-type="string" target="domain" target-type="string"
target-header="Hardware" not-null="true" required="True" max-length="255"
/>
<field resource="last_discovered" source-type="string" target="last_audit_date" target-type="date"
target-header="Hardware" not-null="true"/>
<!-- Temporally don't map the vendor, because there is no way to sync the products and vendors using api right now.
<field resource="purchase_order_vendor" source-type="string" target="vendor" target-type="string"
target-header="Hardware"
target-foregin="vendors" target-foregin-key="name" not-null="true" required="True" max-length="255"/>
-->
<!-- ~Hardware -->
<!-- Cost -->
<field resource="cost" type="float" target="cost"
target-header="Cost" target-type="float" not-null="true" set-zero="true"/>
<!-- ~Cost -->
<!-- Computer -->
<field resource="uuid" type="string" target="uuid"
target-header="Computer" not-null="true" set-space="true"
min-length="1" max-length="255"/>
<field resource="ip_address" type="string" target="computer_ip_address"
target-header="Computer" not-null="true" max-length="255"/>
<field resource="hwaddress" type="string" target="mac_address"
target-header="Computer" not-null="true" max-length="255"/>
<field resource="os_name" source-type="string" target="os" target-type="string"
target-header="Computer" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="device_os_version" source-type="string" target="os_version" target-type="string"
target-header="Computer" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="device_os_version_no" source-type="string" target="os_service_pack" target-type="string"
target-header="Computer" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="hostname" source-type="string" target="hostname" target-type="string"
target-header="Computer" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="last_login_by" source-type="string" target="last_login_by" target-type="string"
target-header="Computer" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="cpuspeed" source-type="float" target="cpu_speed" target-type="float"
target-header="Computer" not-null="true"/>
<field resource="total_cpucore" source-type="integer" target="cpu_core_count" target-type="integer"
not-null="true"
target-header="Computer"/>
<field resource="ramsize" source-type="float" target="memory" target-type="float"
target-header="Computer" not-null="true" />
<field resource="disk_size" source-type="integer" target="disk_space" target-type="integer"
target-header="Computer" not-null="true" />
<!-- ~Computer -->
<!-- Cloud -->
<field resource="cost" type="float" target="cloud_cost"
target-header="Cloud" target-type="float" not-null="true" set-zero="true"/>
<field resource="cloud_instance_region" source-type="string" target="region" target-type="dropdown"
target-header="Cloud" not-null="true" min-length="1" max-length="255"/>
<field resource="cloud_instance_location" source-type="string" target="cd_availability_zone" target-type="dropdown"
target-header="Cloud" not-null="true" min-length="1" max-length="255"/>
<field resource="cloud_vendor" source-type="string" target="provider_type" target-type="string"
target-header="Cloud" not-null="true"/>
<field resource="last_discovered" source-type="string" target="cloud_last_audit_date" target-type="date"
target-header="Cloud" not-null="true"/>
<!-- ~Cloud -->
<!-- Host -->
<field resource="cloud_instance_id" source-type="string" target="item_id" target-type="string"
target-header="Host" not-null="true" error-skip="true"/>
<field resource="cloud_instance_name" source-type="string" target="item_name" target-type="string"
target-header="Host" not-null="true"/>
<field resource="ramsize" source-type="float" target="memory_size" target-type="float"
target-header="Host" not-null="true" />
<field resource="total_cpucore" source-type="integer" target="cpu_core_count" target-type="integer"
not-null="true"
target-header="Host"/>
<field resource="disk_size" source-type="integer" target="total_disk_size" target-type="integer"
target-header="Host" not-null="true" />
<field resource="cloud_instance_status" source-type="string" target="state" target-type="string"
target-header="Host">
<value-mapping default="Unknown">
<item key="Running" value="Powered On"/>
<item key="Stopped" value="Powered Off"/>
</value-mapping>
</field>
<field resource="ip_address" type="string" target="ip_address"
target-header="Host" not-null="true" max-length="255"/>
<!-- ~Host -->
<!-- VMware VCenter Host -->
<field resource="datacenter" type="string" target="datacenter"
target-header="VMware VCenter Host" not-null="true" max-length="255"/>
<field resource="device_cluster_name" type="string" target="cluster"
target-header="VMware VCenter Host" not-null="true" max-length="255"/>
<field resource="mountpoint" type="string" target="datastore"
target-header="VMware VCenter Host" not-null="true" max-length="255"/>
<field resource="hypervisor" type="string" target="hypervisor"
target-header="VMware VCenter Host" not-null="true" max-length="255"/>
<field resource="cpuspeed" source-type="float" target="cpu_speed" target-type="float"
target-header="VMware VCenter Host" not-null="true"/>
<field resource="os_name" source-type="string" target="os_type" target-type="string"
target-header="VMware VCenter Host" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="uuid" type="string" target="instance_uuid"
target-header="VMware VCenter Host" not-null="true" set-space="true"
min-length="1" max-length="255"/>
<field resource="serial_no" source-type="string" target="cd_serial_number" target-type="string"
target-header="VMware VCenter Host" not-null="true" set-space="true"
min-length="1" max-length="255" error-skip="true"/>
<field resource="hardware_vendor" source-type="string" target="hardware_vendor" target-type="string"
target-header="VMware VCenter Host" not-null="true" set-space="true"
min-length="1" max-length="255" error-skip="true"/>
<!-- ~VMware VCenter Host -->
<!-- Virtual Machine -->
<field resource="cloud_instance_id" source-type="string" target="item_id" target-type="string"
target-header="Virtual Machine" not-null="true" error-skip="true"/>
<field resource="cloud_instance_name" source-type="string" target="item_name" target-type="string"
target-header="Virtual Machine" not-null="true"/>
<field resource="cloud_instance_type" source-type="string" target="cd_instance_type" target-type="dropdown"
target-header="Virtual Machine" not-null="true" min-length="1" max-length="255"/>
<field resource="public_ip_address" type="string" target="public_address"
target-header="Virtual Machine" not-null="true" max-length="255"/>
<field resource="private_ip_address" type="string" target="private_address"
target-header="Virtual Machine" not-null="true" max-length="255"/>
<field resource="cloud_instance_status" source-type="string" target="vm_state" target-type="string"
target-header="Virtual Machine">
<value-mapping default="Unknown">
<item key="Running" value="Running"/>
<item key="Stopped" value="Stopped"/>
</value-mapping>
</field>
<field resource="os_name" source-type="string" target="os_name" target-type="string"
target-header="Virtual Machine" not-null="true" set-space="true" min-length="1" max-length="255"/>
<!-- ~Virtual Machine -->
<!-- VMWare VCenter VM -->
<field resource="hostname" source-type="string" target="hostname" target-type="string"
target-header="VMware VCenter VM" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="mountpoint" type="string" target="datastore"
target-header="VMware VCenter VM" not-null="true" max-length="255"/>
<field resource="ramsize" source-type="float" target="memory_size" target-type="float"
target-header="VMware VCenter VM" not-null="true" />
<field resource="total_cpucore" source-type="integer" target="num_of_cpu" target-type="integer"
not-null="true"
target-header="VMware VCenter VM"/>
<field resource="uuid" type="string" target="instance_uuid"
target-header="VMware VCenter VM" not-null="true" set-space="true"
min-length="1" max-length="255"/>
<field resource="host_datacenter" source-type="string" target="datacenter" target-type="string"
target-header="VMware VCenter VM" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="host_cluster_name" source-type="string" target="cluster" target-type="string"
target-header="VMware VCenter VM" not-null="true" set-space="true" min-length="1" max-length="255"/>
<!-- ~VMWare VCenter VM -->
<!-- AWS VM -->
<field resource="cloud_account" source-type="string" target="account_id" target-type="integer"
target-header="AWS VM" not-null="true"/>
<field resource="cloud_instance_id" source-type="string" target="cd_instance_id" target-type="string"
target-header="AWS VM" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="cloud_image_id" source-type="string" target="image_id" target-type="string"
target-header="AWS VM" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="cloud_os_platform" source-type="string" target="platform" target-type="dropdown"
target-header="AWS VM"/>
<field resource="subnet" source-type="string" target="subnet_id" target-type="string"
target-header="AWS VM" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="vrfgroup" source-type="string" target="vpc_id" target-type="string"
target-header="AWS VM" not-null="true" set-space="true" min-length="1" max-length="255"/>
<!-- ~AWS VM -->
<!-- Azure VM -->
<field resource="cloud_account" source-type="string" target="subscription_id" target-type="string"
target-header="Azure VM" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="hostname" source-type="string" target="computer_name" target-type="string"
target-header="Azure VM" not-null="true" set-space="true" min-length="1" max-length="255"/>
<!-- ~Azure VM -->
<!-- Printer -->
<field resource="ip_address" type="string" target="ip_address"
target-header="Printer" not-null="true" max-length="255"/>
<field resource="hwaddress" type="string" target="mac_address"
target-header="Printer" not-null="true" max-length="255"/>
<field resource="subnet_mask" type="string" target="subnet_mask"
target-header="Printer" not-null="true" max-length="255"/>
<!-- ~Printer -->
<!-- Router -->
<field resource="os_name" source-type="string" target="firmware" target-type="string"
target-header="Router" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="os_version" source-type="string" target="firmware_version" target-type="string"
target-header="Router" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="ip_address" type="string" target="ip_address"
target-header="Router" not-null="true" max-length="255"/>
<field resource="total_port_count" source-type="integer" target="ports" target-type="integer"
target-header="Router" not-null="true"/>
<field resource="hwaddress" type="string" target="mac_address"
target-header="Router" not-null="true" max-length="255"/>
<field resource="subnet_mask" type="string" target="subnet_mask"
target-header="Router" not-null="true" max-length="255"/>
<!-- ~Router -->
<!-- Firewall -->
<field resource="os_name" source-type="string" target="firmware" target-type="string"
target-header="Firewall" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="os_version" source-type="string" target="firmware_version" target-type="string"
target-header="Firewall" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="ip_address" type="string" target="ip_address"
target-header="Firewall" not-null="true" max-length="255"/>
<field resource="total_port_count" source-type="integer" target="ports" target-type="integer"
target-header="Firewall" not-null="true"/>
<field resource="hwaddress" type="string" target="mac_address"
target-header="Firewall" not-null="true" max-length="255"/>
<field resource="subnet_mask" type="string" target="subnet_mask"
target-header="Firewall" not-null="true" max-length="255"/>
<!-- ~Firewall -->
<!-- Load Balancer -->
<field resource="cloud_instance_id" source-type="string" target="item_id" target-type="string"
target-header="Load Balancer" not-null="true" error-skip="true"/>
<field resource="cloud_instance_name" source-type="string" target="item_name" target-type="string"
target-header="Load Balancer" not-null="true"/>
<!-- ~Load Balancer -->
<!-- Switch -->
<field resource="os_name" source-type="string" target="firmware" target-type="string"
target-header="Switch" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="os_version" source-type="string" target="firmware_version" target-type="string"
target-header="Switch" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="ip_address" type="string" target="ip_address"
target-header="Switch" not-null="true" max-length="255"/>
<field resource="total_port_count" source-type="integer" target="ports" target-type="integer"
target-header="Switch" not-null="true"/>
<field resource="hwaddress" type="string" target="mac_address"
target-header="Switch" not-null="true" max-length="255"/>
<field resource="subnet_mask" type="string" target="subnet_mask"
target-header="Switch" not-null="true" max-length="255"/>
<!-- ~Switch -->
</mapping>
<unmapping callback="from_d42" key="name" source="Devices">
<field resource="network_device" source-type="boolean" display-name="Network Device"/>
<field resource="blade_chassis" source-type="boolean" display-name="Blade Chassis"/>
<field resource="device_subtype" source-type="string" display-name="Device Subtype"/>
</unmapping>
<d42url resource="device_pk" source-type="integer" url="{0}/admin/rackraj/device/{1}/" />
</task>
<task enable="false" name="Devices" description="Copy Servers from Device42 to FreshService using DOQL v1" d42_max_version="16.18.02">
<api>
<target model="assets" target="freshservice" method="POST" update_method="PUT" path="api/v2/assets"/>
<resource model="Devices" target="device42" method="POST"
doql="
WITH
devicelastlogin AS (
with sub as ( SELECT dll.device_fk, dll.username, ROW_NUMBER() OVER(PARTITION BY dll.device_fk ORDER BY dll.last_login DESC) AS rank FROM view_devicelastlogin_v1 dll )
SELECT * FROM sub WHERE rank = 1 )
,alias as ( SELECT device_fk, string_agg(alias_name, ', ' order by alias_name) as hostname FROM view_devicealias_v1 GROUP BY device_fk)
,assetlifecycle AS (
with sub as (
SELECT alc.device_fk, alc.date,
ROW_NUMBER() OVER(PARTITION BY alc.device_fk ORDER BY alc.date DESC) AS rank
FROM view_assetlifecycle_v1 alc
INNER JOIN view_assetaction_v1 aaction ON alc.assetaction_fk = aaction.assetaction_pk WHERE aaction.name = 'Purchased')
SELECT * FROM sub WHERE rank = 1 )
,asset_endlifecycle AS (
with sub as (
SELECT alc.device_fk, to_char(alc.date, 'YYYY-MM-DD') as date,
ROW_NUMBER() OVER(PARTITION BY alc.device_fk ORDER BY alc.date) AS rank
FROM view_assetlifecycle_v1 alc
INNER JOIN view_assetaction_v1 aaction ON alc.assetaction_fk = aaction.assetaction_pk WHERE aaction.name = 'Retired')
SELECT * FROM sub WHERE rank = 1 )
,warranty AS (
with sub as (
SELECT plid.device_fk, pli.start_date, pli.end_date,
extract(year from age(pli.end_date, CASE WHEN lower(ven.name)='meraki' and pli.start_date='1970-01-01' THEN null else pli.start_date end)) warranty_years,
extract(month from age(pli.end_date, CASE WHEN lower(ven.name)='meraki' and pli.start_date='1970-01-01' THEN null else pli.start_date end)) + case when extract(day from age(pli.end_date, CASE WHEN lower(ven.name)='meraki' and pli.start_date='1970-01-01' THEN null else pli.start_date end)) > 0 then 1 else 0 end warranty_months,
ven.name as purchase_order_vendor,
ROW_NUMBER() OVER(PARTITION BY plid.device_fk ORDER BY pli.end_date DESC) AS rank
FROM view_purchaselineitems_to_devices_v1 plid
INNER JOIN view_purchaselineitem_v1 pli ON plid.purchaselineitem_fk = pli.purchaselineitem_pk
LEFT JOIN view_purchase_v1 pur ON pli.purchase_fk = pur.purchase_pk
LEFT JOIN view_vendor_v1 ven ON pur.vendor_fk=ven.vendor_pk
WHERE lower(pli.line_type) = 'contract'
and ( lower(pli.contract_type_name) = 'warranty'
or ( lower(pli.contract_type_name) in ('base', 'upgrade') and lower(ven.name) in ('ibm', 'lenovo'))
)
and pli.end_date is not null
)
SELECT * FROM sub WHERE rank = 1 )
,part_count as (
SELECT p.device_fk,
SUM(p.pcount *
CASE
WHEN pm.hdsize_unit = 'GB' THEN pm.hdsize
WHEN pm.hdsize_unit = 'TB' THEN pm.hdsize * 1024
WHEN pm.hdsize_unit = 'PB' THEN pm.hdsize * 1024 * 1024
WHEN pm.hdsize_unit = 'EB' THEN pm.hdsize * 1024 * 1024 * 1024
WHEN pm.hdsize_unit = 'ZB' THEN pm.hdsize * 1024 * 1024 * 1024 * 1024
WHEN pm.hdsize_unit = 'YB' THEN pm.hdsize * 1024 * 1024 * 1024 * 1024 * 1024
ELSE NULL
END) AS total_part_disk_size
FROM view_part_v1 p
INNER JOIN view_partmodel_v1 pm ON p.partmodel_fk = pm.partmodel_pk
WHERE pm.type_id = 3 AND pm.hdsize IS NOT NULL AND p.pcount > 0 AND p.device_fk IS NOT NULL
GROUP BY p.device_fk )
,network_port_count as (
SELECT p.device_fk,
count(*) AS total_port_count
from (
select device_fk, hwaddress from view_netport_v1 where device_fk is not null
union
select second_device_fk as device_fk, hwaddress from view_netport_v1 where second_device_fk is not null
) p
WHERE p.device_fk is not null
GROUP BY p.device_fk )
,total_costs as (
select device_fk, sum(cost) as cost from view_purchaselineitem_v1 left join view_purchaselineitems_to_devices_v1 on purchaselineitem_fk = purchaselineitem_pk group by device_fk)
,network_hw as (
select device_fk,
string_agg(
CASE
WHEN LENGTH(TRIM(netport.hwaddress)) = 12
THEN CONCAT_WS(':',left(TRIM(netport.hwaddress),2), substring(TRIM(netport.hwaddress),3,2), substring(TRIM(netport.hwaddress),5,2), substring(TRIM(netport.hwaddress),7,2), substring(TRIM(netport.hwaddress),9,2), right(TRIM(netport.hwaddress),2))
ELSE
netport.hwaddress
END, ', '
order by netport.hwaddress) as hwaddress
from (
select device_fk, hwaddress from view_netport_v1 where device_fk is not null
union
select second_device_fk as device_fk, hwaddress from view_netport_v1 where second_device_fk is not null
) netport
where (netport.hwaddress is not null AND LENGTH(TRIM(netport.hwaddress)) > 0)
group by netport.device_fk )
,network_ip as (
select
vip.device_fk,
string_agg(host(vip.ip_address)::character varying, ', ' order by vip.ip_address) as ip_address,
string_agg(case when vip.ip_address << inet '10/8' or vip.ip_address << inet '192/8' or vip.ip_address << inet '172.16/12' then
host(vip.ip_address)::character varying
else null end, ', ' order by vip.ip_address) as private_ip_address,
string_agg(case when vip.ip_address << inet '10/8' or vip.ip_address << inet '192/8' or vip.ip_address << inet '172.16/12' then null
else host(vip.ip_address)::character varying end, ', ' order by vip.ip_address) as public_ip_address,
string_agg(host(vip.subnet_mask)::character varying, ', ' order by vip.ip_address) as subnet_mask
from (select distinct device_fk, ip_address, netmask(set_masklen(view_subnet_v1.network, view_subnet_v1.mask_bits)) as subnet_mask
from view_ipaddress_v1 left join view_subnet_v1 on view_ipaddress_v1.subnet_fk = view_subnet_v1.subnet_pk) vip group by vip.device_fk)
,subnet as (
select
vn.device_fk,
string_agg(host(vn.network)::character varying, ', ' order by vn.network) as network
from (select distinct device_fk, network from view_ipaddress_v1 left join view_subnet_v1 on subnet_pk=subnet_fk) vn group by vn.device_fk)
,vrfgroup as (
select
vn.device_fk,
string_agg(vn.vrfgroup, ', ' order by vn.vrfgroup) as vrfgroup
from (select distinct device_fk, view_vrfgroup_v1.name as vrfgroup from view_ipaddress_v1
left join view_subnet_v1 on subnet_pk=subnet_fk
left join view_vrfgroup_v1 on vrfgroup_pk=vrfgroup_fk) vn group by vn.device_fk)
,device_cluster AS (
SELECT device_cluster.child_device_fk device_fk,
string_agg(device_cluster.parent_device_name, ', ' order by device_cluster.parent_device_name) as device_cluster_name
FROM view_devices_in_cluster_v1 device_cluster group by device_cluster.child_device_fk)
,mount_point as (
select mp.device_fk, string_agg(mp.mountpoint, ', ' order by mp.mountpoint) as mountpoint from view_mountpoint_v1 mp group by mp.device_fk)
select view_device_v1.*,
case
WHEN view_device_v1.virtual_host AND lower(view_device_v1.os_name) like '%esxi%' THEN 'VMware VCenter Host'
WHEN view_device_v1.virtual_host THEN 'Host'
WHEN lower(view_device_v1.os_name) similar to '%(f5|netscaler)%' THEN 'Load Balancer'
WHEN view_device_v1.network_device THEN 'Switch'
WHEN lower(view_device_v1.type) = 'physical' AND lower(view_device_v1.os_name) similar to '%(windows|microsoft)%' AND lower(view_device_v1.os_name) like '%server%' THEN 'Windows Server'
WHEN lower(view_device_v1.type) = 'physical' AND lower(view_device_v1.os_name) similar to '%(unix|z/os|z os|zos|hp-ux|os400|os/400|os 400|linux|amazon|ubuntu|centos|redhat|debian|sles|suse|gentoo|oracle|freebsd|rhel|red hat|fedora|alma|rocky|arch)%' THEN 'Unix Server'
WHEN lower(view_device_v1.type) = 'physical' AND lower(view_device_v1.os_name) similar to '%(aix)%' THEN 'AIX Server'
WHEN lower(view_device_v1.type) = 'physical' AND lower(view_device_v1.os_name) similar to '%(solaris|sunos|sun os)%' THEN 'Solaris Server'
WHEN lower(view_device_v1.type) = 'virtual' AND lower(view_device_v1.virtual_subtype) = 'vmware' THEN 'VMware VCenter VM'
WHEN lower(view_device_v1.type) = 'virtual' AND lower(view_device_v1.virtual_subtype) = 'amazon ec2 instance' THEN 'AWS VM'
WHEN lower(view_device_v1.type) = 'virtual' AND lower(view_device_v1.virtual_subtype) = 'azure virtual machine' THEN 'Azure VM'
WHEN lower(view_device_v1.type) = 'physical' THEN 'Server'
WHEN lower(view_device_v1.type) = 'virtual' THEN 'Virtual Machine'
ELSE 'Computer'
END as asset_type,
view_device_v1.cpucount * view_device_v1.cpucore as total_cpucore,
t_cost.cost,
case WHEN view_device_v1.type = 'Virtual' THEN view_device_v1.type WHEN view_device_v1.type = 'Cluster' THEN view_device_v1.type ELSE coalesce(view_hardware_v1.name, view_device_v1.type) END as hw_model,
case WHEN view_device_v1.hz = 'MHz' and view_device_v1.cpupower is not null THEN view_device_v1.cpupower / 1000 ELSE view_device_v1.cpupower END as cpuspeed,
case WHEN view_device_v1.ram_size_type = 'MB' and view_device_v1.ram is not null THEN view_device_v1.ram / 1024 WHEN view_device_v1.ram_size_type = 'TB' and view_device_v1.ram is not null THEN view_device_v1.ram * 1024 ELSE view_device_v1.ram END as ramsize,
network_ip.ip_address, network_ip.public_ip_address, network_ip.private_ip_address, network_ip.subnet_mask, network_hw.hwaddress,
CEIL(COALESCE(p.total_part_disk_size,
CASE
WHEN view_device_v1.hard_disk_count IS NOT NULL AND
view_device_v1.hard_disk_size IS NOT NULL AND
view_device_v1.hard_disk_size_type IS NOT NULL THEN
view_device_v1.hard_disk_count *
CASE
WHEN view_device_v1.hard_disk_size_type = 'GB' THEN view_device_v1.hard_disk_size
WHEN view_device_v1.hard_disk_size_type = 'TB' THEN view_device_v1.hard_disk_size * 1024
WHEN view_device_v1.hard_disk_size_type = 'PB' THEN view_device_v1.hard_disk_size * 1024 * 1024
ELSE NULL
END
ELSE NULL
END)) AS disk_size,
warranty.warranty_months + (warranty.warranty_years * 12) as warranty_months,
warranty.end_date as warranty_end_date,
warranty.purchase_order_vendor as purchase_order_vendor,
assetlifecycle.date as acquisition_date,
asset_endlifecycle.date as end_of_life,
case when lower(view_device_v1.os_name) like '%windows%' or lower(view_device_v1.os_name) like '%microsoft%' then view_device_v1.os_version_no else view_device_v1.os_version end as device_os_version,
case when lower(view_device_v1.os_name) like '%windows%' or lower(view_device_v1.os_name) like '%microsoft%' then view_device_v1.os_version else view_device_v1.os_version_no end as device_os_version_no,
alias.hostname as hostname,
devicelastlogin.username as last_login_by,
case
WHEN (lower(view_device_v1.type) = 'virtual' AND lower(view_device_v1.virtual_subtype) = 'amazon ec2 instance')
or lower(civ.name) similar to '%(aws|amazon)%' THEN 'AWS'
WHEN (lower(view_device_v1.type) = 'virtual' AND lower(view_device_v1.virtual_subtype) = 'azure virtual machine')
or lower(civ.name) similar to '%(azure|microsoft)%' THEN 'AZURE'
WHEN (lower(view_device_v1.type) = 'virtual' AND lower(view_device_v1.virtual_subtype) = 'vmware')
or lower(civ.name) like '%vmware%'
or (view_device_v1.virtual_host and lower(view_device_v1.os_name) like '%esxi%') then 'VMWARE VCENTER'
WHEN (lower(view_device_v1.type) = 'virtual' AND lower(view_device_v1.virtual_subtype) = 'gce')
or lower(civ.name) similar to '%(google|gce|gcp)%' then 'GCP'
WHEN (lower(view_device_v1.type) = 'virtual' AND lower(view_device_v1.virtual_subtype) = 'oracle cloud instance')
or lower(civ.name) like '%oracle%' then 'Oracle Cloud'
WHEN (lower(view_device_v1.type) = 'virtual' AND (lower(view_device_v1.virtual_subtype) = 'citrix/xen'
or lower(view_device_v1.virtual_subtype) = 'xen'))
or lower(civ.name) similar to '%(citrix|xen)%'
or (view_device_v1.virtual_host and lower(view_device_v1.os_name) like '%xenserver%') then 'Citrix'
WHEN (lower(view_device_v1.type) = 'virtual' AND lower(view_device_v1.virtual_subtype) = 'hyper-v')
or (view_device_v1.virtual_host and lower(view_device_v1.os_name) similar to '%(windows|microsoft)%' AND lower(view_device_v1.os_name) like '%server%') then 'Hyper-V'
WHEN (lower(view_device_v1.type) = 'virtual' AND lower(view_device_v1.virtual_subtype) = 'digitalocean instance')
or lower(civ.name) similar to '%(digitalocean|digital ocean)%' then 'Digital Ocean'
WHEN (lower(view_device_v1.type) = 'virtual' AND lower(view_device_v1.virtual_subtype) = 'alibaba cloud instance')
or lower(civ.name) like '%alibaba%' then 'Alibaba Cloud'
else null
end as cloud_vendor,
ci.instance_name as cloud_instance_name,
ci.instance_type as cloud_instance_type,
ci.account as cloud_account,
ci.image_id as cloud_image_id,
ci.os_platform as cloud_os_platform,
subnet.network as subnet,
vrfgroup.vrfgroup as vrfgroup,
view_device_v1.cloud_location as cloud_region,
device_cluster.device_cluster_name as device_cluster_name,
mount_point.mountpoint as mountpoint,
CONCAT_WS(' ', view_device_v1.os_name, view_device_v1.os_version, view_device_v1.os_version_no) as hypervisor,
hv.name as hardware_vendor,
virtual_host_device.datacenter as host_datacenter,
host_device_cluster.device_cluster_name as host_cluster_name,
case when view_device_v1.name ~* '^((?!-)[A-Za-z0-9-]{{1,63}}(?<!-)\.)+[A-Za-z]{{2,6}}$' then substring(view_device_v1.name, position('.' in view_device_v1.name)+1)
else null end domain_name,
network_port_count.total_port_count as total_port_count
FROM view_device_v1
LEFT JOIN view_hardware_v1 on view_device_v1.hardware_fk = view_hardware_v1.hardware_pk
LEFT JOIN network_ip on view_device_v1.device_pk = network_ip.device_fk
LEFT JOIN network_hw on view_device_v1.device_pk = network_hw.device_fk
LEFT JOIN total_costs t_cost on t_cost.device_fk = device_pk
LEFT OUTER JOIN part_count AS p ON view_device_v1.device_pk = p.device_fk
LEFT OUTER JOIN warranty ON view_device_v1.device_pk = warranty.device_fk
LEFT OUTER JOIN assetlifecycle ON view_device_v1.device_pk = assetlifecycle.device_fk
LEFT OUTER JOIN asset_endlifecycle ON view_device_v1.device_pk = asset_endlifecycle.device_fk
LEFT OUTER JOIN alias ON view_device_v1.device_pk = alias.device_fk
LEFT OUTER JOIN devicelastlogin ON view_device_v1.device_pk = devicelastlogin.device_fk
LEFT JOIN view_cloudinstance_v1 as ci ON view_device_v1.device_pk = ci.device_fk
LEFT JOIN view_vendor_v1 as civ ON view_device_v1.cloud_vendor_fk = civ.vendor_pk
LEFT JOIN device_cluster ON view_device_v1.device_pk = device_cluster.device_fk
LEFT JOIN mount_point ON view_device_v1.device_pk = mount_point.device_fk
LEFT JOIN view_vendor_v1 as hv ON view_hardware_v1.vendor_fk = hv.vendor_pk
LEFT JOIN view_device_v1 as virtual_host_device ON view_device_v1.virtual_host_device_fk = virtual_host_device.device_pk
LEFT JOIN device_cluster as host_device_cluster ON virtual_host_device.device_pk = host_device_cluster.device_fk
LEFT JOIN subnet ON view_device_v1.device_pk = subnet.device_fk
LEFT JOIN vrfgroup ON view_device_v1.device_pk = vrfgroup.device_fk
LEFT JOIN network_port_count ON view_device_v1.device_pk = network_port_count.device_fk
"
/>
</api>
<mapping callback="from_d42" key="name" source="Devices">
<!-- General -->
<field resource="asset_no" source-type="string" target="asset_tag" target-type="string"
target-header="General" not-null="true" min-length="1" max-length="255"/>
<field resource="name" source-type="string" target="name" target-type="string" target-header="General"
set-space="true" min-length="1" max-length="248" escape="true"/>
<field resource="notes" source-type="string" target="description" target-header="General"
not-null="true" set-space="true" min-length="1" max-length="60000"/>
<field resource="end_of_life" source-type="string" target="end_of_life" target-type="date"
target-header="General"/>
<!-- ~General -->
<!-- Hardware -->
<field resource="in_service" source-type="boolean" target="asset_state" target-type="string"
target-header="Hardware">
<value-mapping default="Retired">
<item key="true" value="In Use"/>
</value-mapping>
</field>
<field resource="hw_model" source-type="string" target="product" target-type="string"
target-header="Hardware"
target-foregin="products" target-foregin-key="name" not-null="true" required="True" max-length="255"/>
<!-- Temporally don't map the vendor, because there is no way to sync the products and vendors using api right now.
<field resource="purchase_order_vendor" source-type="string" target="vendor" target-type="string"
target-header="Hardware"
target-foregin="vendors" target-foregin-key="name" not-null="true" required="True" max-length="255"/>
-->
<field resource="serial_no" source-type="string" target="serial_number" target-type="string"
target-header="Hardware" not-null="true" set-space="true"
min-length="1" max-length="255" error-skip="true"/>
<field resource="warranty_months" source-type="integer" target="warranty" target-type="integer"
target-header="Hardware" not-null="true"/>
<field resource="warranty_end_date" source-type="string" target="warranty_expiry_date" target-type="date"
target-header="Hardware" not-null="true"/>
<field resource="acquisition_date" source-type="string" target="acquisition_date" target-type="date"
target-header="Hardware" not-null="true"/>
<field resource="domain_name" source-type="string" target="domain" target-type="string"
target-header="Hardware" not-null="true" required="True" max-length="255"
/>
<!-- ~Hardware -->
<!-- Cost -->
<field resource="cost" type="float" target="cost"
target-header="Cost" target-type="float" not-null="true" set-zero="true"/>
<!-- ~Cost -->
<!-- Computer -->
<field resource="uuid" type="string" target="uuid"
target-header="Computer" not-null="true" set-space="true"
min-length="1" max-length="255"/>
<field resource="ip_address" type="string" target="computer_ip_address"
target-header="Computer" not-null="true" max-length="255"/>
<field resource="hwaddress" type="string" target="mac_address"
target-header="Computer" not-null="true" max-length="255"/>
<field resource="os_name" source-type="string" target="os" target-type="string"
target-header="Computer" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="device_os_version" source-type="string" target="os_version" target-type="string"
target-header="Computer" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="device_os_version_no" source-type="string" target="os_service_pack" target-type="string"
target-header="Computer" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="hostname" source-type="string" target="hostname" target-type="string"
target-header="Computer" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="last_login_by" source-type="string" target="last_login_by" target-type="string"
target-header="Computer" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="cpuspeed" source-type="float" target="cpu_speed" target-type="float"
target-header="Computer" not-null="true"/>
<field resource="total_cpucore" source-type="integer" target="cpu_core_count" target-type="integer"
not-null="true"
target-header="Computer"/>
<field resource="ramsize" source-type="float" target="memory" target-type="float"
target-header="Computer" not-null="true" />
<field resource="disk_size" source-type="integer" target="disk_space" target-type="integer"
target-header="Computer" not-null="true" />
<!-- ~Computer -->
<!-- Cloud -->
<field resource="cost" type="float" target="cloud_cost"
target-header="Cloud" target-type="float" not-null="true" set-zero="true"/>
<field resource="cloud_region" source-type="string" target="region" target-type="dropdown"
target-header="Cloud" not-null="true" min-length="1" max-length="255"/>
<field resource="cloud_location" source-type="string" target="cd_availability_zone" target-type="dropdown"
target-header="Cloud" not-null="true" min-length="1" max-length="255"/>
<field resource="cloud_vendor" source-type="string" target="provider_type" target-type="dropdown"
target-header="Cloud" not-null="true"/>
<!-- ~Cloud -->
<!-- Host -->
<field resource="cloud_instance_id" source-type="string" target="item_id" target-type="string"
target-header="Host" not-null="true" error-skip="true"/>
<field resource="cloud_instance_name" source-type="string" target="item_name" target-type="string"
target-header="Host" not-null="true"/>
<field resource="ramsize" source-type="float" target="memory_size" target-type="float"
target-header="Host" not-null="true" />
<field resource="total_cpucore" source-type="integer" target="cpu_core_count" target-type="integer"
not-null="true"
target-header="Host"/>
<field resource="disk_size" source-type="integer" target="total_disk_size" target-type="integer"
target-header="Host" not-null="true" />
<field resource="cloud_status" source-type="string" target="state" target-type="string"
target-header="Host">
<value-mapping default="Unknown">
<item key="Running" value="Powered On"/>
<item key="Stopped" value="Powered Off"/>
</value-mapping>
</field>
<field resource="ip_address" type="string" target="ip_address"
target-header="Host" not-null="true" max-length="255"/>
<!-- ~Host -->
<!-- VMware VCenter Host -->
<field resource="datacenter" type="string" target="datacenter"
target-header="VMware VCenter Host" not-null="true" max-length="255"/>
<field resource="device_cluster_name" type="string" target="cluster"
target-header="VMware VCenter Host" not-null="true" max-length="255"/>
<field resource="mountpoint" type="string" target="datastore"
target-header="VMware VCenter Host" not-null="true" max-length="255"/>
<field resource="hypervisor" type="string" target="hypervisor"
target-header="VMware VCenter Host" not-null="true" max-length="255"/>
<field resource="cpuspeed" source-type="float" target="cpu_speed" target-type="float"
target-header="VMware VCenter Host" not-null="true"/>
<field resource="os_name" source-type="string" target="os_type" target-type="string"
target-header="VMware VCenter Host" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="uuid" type="string" target="instance_uuid"
target-header="VMware VCenter Host" not-null="true" set-space="true"
min-length="1" max-length="255"/>
<field resource="serial_no" source-type="string" target="cd_serial_number" target-type="string"
target-header="VMware VCenter Host" not-null="true" set-space="true"
min-length="1" max-length="255" error-skip="true"/>
<field resource="hardware_vendor" source-type="string" target="hardware_vendor" target-type="string"
target-header="VMware VCenter Host" not-null="true" set-space="true"
min-length="1" max-length="255" error-skip="true"/>
<!-- ~VMware VCenter Host -->
<!-- Virtual Machine -->
<field resource="cloud_instance_id" source-type="string" target="item_id" target-type="string"
target-header="Virtual Machine" not-null="true" error-skip="true"/>
<field resource="cloud_instance_name" source-type="string" target="item_name" target-type="string"
target-header="Virtual Machine" not-null="true"/>
<field resource="cloud_instance_type" source-type="string" target="cd_instance_type" target-type="dropdown"
target-header="Virtual Machine" not-null="true" min-length="1" max-length="255"/>
<field resource="public_ip_address" type="string" target="public_address"
target-header="Virtual Machine" not-null="true" max-length="255"/>
<field resource="private_ip_address" type="string" target="private_address"
target-header="Virtual Machine" not-null="true" max-length="255"/>
<field resource="cloud_status" source-type="string" target="vm_state" target-type="string"
target-header="Virtual Machine">
<value-mapping default="Unknown">
<item key="Running" value="Running"/>
<item key="Stopped" value="Stopped"/>
</value-mapping>
</field>
<field resource="os_name" source-type="string" target="os_name" target-type="string"
target-header="Virtual Machine" not-null="true" set-space="true" min-length="1" max-length="255"/>
<!-- ~Virtual Machine -->
<!-- VMWare VCenter VM -->
<field resource="hostname" source-type="string" target="hostname" target-type="string"
target-header="VMware VCenter VM" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="mountpoint" type="string" target="datastore"
target-header="VMware VCenter VM" not-null="true" max-length="255"/>
<field resource="ramsize" source-type="float" target="memory_size" target-type="float"
target-header="VMware VCenter VM" not-null="true" />
<field resource="total_cpucore" source-type="integer" target="num_of_cpu" target-type="integer"
not-null="true"
target-header="VMware VCenter VM"/>
<field resource="uuid" type="string" target="instance_uuid"
target-header="VMware VCenter VM" not-null="true" set-space="true"
min-length="1" max-length="255"/>
<field resource="host_datacenter" source-type="string" target="datacenter" target-type="string"
target-header="VMware VCenter VM" not-null="true" set-space="true" min-length="1" max-length="255"/>
<field resource="host_cluster_name" source-type="string" target="cluster" target-type="string"
target-header="VMware VCenter VM" not-null="true" set-space="true" min-length="1" max-length="255"/>